Re: BUG #17198: Planning time too high when execute query on standby cluster

From: Andriy Bartash <Andriy(dot)Bartash(at)everbridge(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>, "abartash(at)xmatters(dot)com" <abartash(at)xmatters(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #17198: Planning time too high when execute query on standby cluster
Date: 2021-09-21 16:27:06
Message-ID: MWHPR04MB078537F7F7C287D4F58F5E76F6A19@MWHPR04MB0785.namprd04.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Thank you Magnus
Yes, we have checksum enabled (means that wal_log_hints is always on).
Currently, we are running reindexing or calling pg_repack instead.
I will try more aggressive vacuum setup to prevent this issue. At least now we know the root cause – “hint bits not being updated on
the standby side, and not being included in the replication”
Wondering, if there is a Postgres config parameter what might help us?
Thank you in advance.

From: Magnus Hagander <magnus(at)hagander(dot)net>
Date: Monday, September 20, 2021 at 11:55 PM
To: abartash(at)xmatters(dot)com <abartash(at)xmatters(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #17198: Planning time too high when execute query on standby cluster
[everbridge logo] CAUTION:
This email originated outside the organization. Do not click links or open attachments if you do not recognize the sender and know the content is safe.

On Tue, Sep 21, 2021 at 8:42 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 17198
> Logged by: Andriy Bartash
> Email address: abartash(at)xmatters(dot)com
> PostgreSQL version: 12.8
> Operating system: CentOS7
> Description:
>
> We have physical standby and noticed that some queries have Planning time
> very high (2-10 sec). Same queries on Primary cluster have Planning time 4-6
> msec
> Tried to run ANALYZE <table>, but it didn't help.
> Also noticed that RC (?) was bloated objects (table either index)

Do you have wal_log_hints or checksums enabled on your system? If not,
try running a VACUUM on the primary (not analyze), and see if that
helps.

Situations like this can happen due to hint bits not being updated on
the standby side, and not being included in the replication.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

The content of this email is confidential and intended for the recipient specified in message only. It is strictly forbidden to share any part of this message with any third party, without a written consent of the sender. If you received this message in error, please reply to this message and follow with its deletion, so that we can ensure such an error does not occur in the future.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Euler Taveira 2021-09-21 20:46:50 Re: BUG #17193: Issue with pg_output login
Previous Message Tom Lane 2021-09-21 15:49:56 Re: BUG #17199: Calling stored procedure with stable function as argument results in wrong result