Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database

Поиск
Список
Период
Сортировка
От Haribabu Kommi
Тема Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database
Дата
Msg-id CAJrrPGfxb77YKYpJcL7Op1NMVtqdX+zRFDyFZDmhpX1vHjx1rw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database  (Bruce Momjian <bruce@momjian.us>)
Ответы Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database  (Masahiko Sawada <sawada.mshk@gmail.com>)
Список pgsql-bugs
Дерево обсуждения


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.

Regards,
Hari Babu
Fujitsu Australia

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Vitaliy Gomenyuk
Дата:
Сообщение: Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: [BUGS] Concurrent ALTER SEQUENCE RESTART Regression