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

Thank you for all your answers.

1. We can't reindex the table on a hot-standby database:
texting=# REINDEX TABLE "Table";
ERROR: cannot execute REINDEX during recovery

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. But why and how to improve the situation?

A question is still open.

On Tue, May 16, 2017 at 4:58 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
wrote:

> On Mon, May 15, 2017 at 10:44 PM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
> >
> >
> > On Sat, May 13, 2017 at 3:30 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >>
> >> On Thu, May 4, 2017 at 10:26:17AM +1000, Haribabu Kommi wrote:
> >> > The contents of the indexes should be the same, so why is the
> slave
> >> > returning so many more rows? It has to be because the index
> entries
> >> > are
> >> > not marked as killed (known-dead-to-everybody), or not being
> treated
> >> > as
> >> > killed, in the slave. I vaguely recall that there's a difference
> in
> >> > the
> >> > rules for index entry visibility on slaves, but it's not clear to
> me
> >> > why
> >> > that should be.
> >> >
> >> >
> >> > The index cleanup by the full vacuum and vacuum one page are WAL
> logged,
> >> > so when they gets replayed on the slave, both the indexes must be
> same.
> >> >
> >> > May be the WAL didn't replayed on the slave because of conflict
> >> > transaction?
> >> > Or Any other scenarios it may be different?
> >>
> >> We don't WAL-log hint bits. Could that affect queries running on the
> >> standbys?
> >
> >
> > Yes, this may be a reason for extra number of records that are present in
> > the
> > index.
> >
> > Hi Vitaly,
> >
> > Hint bits are used to mark tuples as created and/or deleted by
> transactions
> > that are known committed or aborted. The changes to the hint bits are not
> > WAL logged. These are WAL logged when the data checksums are enabled
> > or wal_log_hintbits GUC is set, but these two options are not available
> in
> > PostgreSQL version 9.2
> >
> > Is it possible for you test the query when you execute the reindex of the
> > index?
> >
> > or
> >
> > Try to add pgstattuple and pageinspect extensions and verify whether the
> > index
> > is same in both master and standby.
> >
> > If there exists more number of records in standby compared to master,
> this
> > may
> > be because of some cleanup that is happening in master and not done in
> the
> > standby.
> >
>
> Because hint bit affects to only heap tuple I think that it's not
> relevant. But because marking index entry as dead is not WAL-logged
> it's possible that unnecessary index entries are marked as dead on
> master server while these are not marked on standby server. This would
> make index scan on standby server return entry much more than master
> server.
>
> Regards,
>
> --
> Masahiko Sawada
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>

--

Best regards,
*Vitaliy Gomenyuk* DBA