Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database - Mailing list pgsql-bugs
Thank you for all your answers.
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.mshk@gmail.com> wrote:
Because hint bit affects to only heap tuple I think that it's notOn Mon, May 15, 2017 at 10:44 PM, Haribabu Kommi
<kommi.haribabu@gmail.com> wrote:
>
>
> On Sat, May 13, 2017 at 3:30 AM, Bruce Momjian <bruce@momjian.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.
>
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 |
pgsql-bugs by date: