Re: BUG #14635: Query is executed slower on hot standby slave database then on master database

Hello,

So we have two reasons of current problem:

1. Changes to hint bits are not WAL logged => can be fixed by upgrading
PostgreSQL at least to 9.4 version and turning on parameter "wal_log_hints";

2. Slave DB does not use the index hint bits while it is in hot standby
mode => how this can be fixed?

Thank you for your answers.

On Fri, May 19, 2017 at 6:41 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Thu, May 18, 2017 at 9:26 AM, Vitaliy Gomenyuk <vgomenyuk(at)callfire(dot)com>
> wrote:
>
>>
>> 2. I'm not sure that the problem is with not WAL-logged hint bits,
>> because due to my next investigation:
>> 2.0. We have two equal servers for master and slave database (CPU,
>> RAM, HDD).
>> 2.1. On master database I'm running a query, it executes in 138.116 ms
>> 2.2. On a hot-standby slave database with working replication process
>> I'm running the same query, it executes in 1623.496 ms (much slower)
>> 2.3. Then on that hot-standby slave database I've created a trigger
>> file, which stops a replication process immediately and right after that,
>> I'm running the same query, it executes in 132.354 ms (fast like on
>> master).
>>
> 2.4. During the time when I'm creating a trigger file (less then 1
>> second), indexes in that table can't be changed. Even more, that slave
>> hot-standby database was created from the full master copy just before I
>> started test. So master and slave databases are equal. One difference is
>> turned on replication process in a hot-standby mode. So only a replication
>> process can cause this behaviour.
>>
>
> If the index hint bits were set on the master and then a base backup is
> taken, then those bits are set on the replica created from that backup.
> But it does not use the index hint bits while it is in hot standby mode.
> But when it is promoted, it will start using them.
>
>
>
>> But why and how to improve the situation?
>>
>
> Vacuum your table (on the master).
>
> Cheers,
>
> Jeff
>

--

Best regards,
*Vitaliy Gomenyuk* Senior DBA
vgomenyuk(at)callfire(dot)com | +380 67 220 5903
email <vgomenyuk(at)callfire(dot)com> | website <https://www.callfire.com/> |
Facebook <https://www.facebook.com/callfire> | Twitter
<https://www.twitter.com/callfire>