Postgres Pro
Downloads
Home   >   mailing lists

Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database - Mailing list pgsql-bugs

From Haribabu Kommi
Subject Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database
Date
Msg-id CAJrrPGeQ-oQdEHUHyB9xYaF0Da5YOPYbsGcH5hN2RaSkmFscfA@mail.gmail.com
Whole thread Raw
In response to Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database  (Vitaliy Gomenyuk <vgomenyuk@callfire.com>)
Responses Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database  (Vitaliy Gomenyuk <vgomenyuk@callfire.com>)
Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-bugs
Tree view


On Fri, May 26, 2017 at 9:42 PM, Vitaliy Gomenyuk <vgomenyuk@callfire.com> wrote:
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?

The main reason for slave DB not use the index hint bits because of difference
in between xmin value of both master and standby, This is required for proper
MVCC behavior for the queries that are running on the standby.

Following is the comment from the code that explains more.

/*
* During recovery we ignore killed tuples and don't bother to kill them
* either. We do this because the xmin on the primary node could easily be
* later than the xmin on the standby node, so that what the primary
* thinks is killed is supposed to be visible on standby. So for correct
* MVCC for queries during recovery we must ignore these hints and check
* all tuples. Do *not* set ignore_killed_tuples to true when running in a
* transaction that was started during recovery. xactStartedInRecovery
* should not be altered by index AMs.
*/

Even if the hint bits are WAL logged, it doesn't solve the problem of multiple
rows selection in standby. I feel the only way to reduce the number of dead tuples
visibility in standby, do a frequent VACUUM or Increase the AUTOVACUUM
interval or changing the default values of AUTOVACUUM parameters of the
tables that are frequently updated/deleted as these operations generates dead
tuples.
 
Regards,
Hari Babu
Fujitsu Australia

pgsql-bugs by date:

Previous
From: Vitaliy Gomenyuk
Date:
Subject: Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database
Next
From: Michael Paquier
Date:
Subject: Re: [BUGS] BUG #14666: Question on money type as the key ofpartitioned table