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

Hello Haribabu,

Thank you for your answer.

There is status of the replication:

select pg_last_xlog_receive_location() "receive_location", pg_last_xlog_replay_location() "replay_location", pg_is_in_recovery() "recovery_status", pg_is_xlog_replay_paused() "replication_paused", now() - pg_last_xact_replay_timestamp() "replication_delay";

 

receive_location | replay_location | recovery_status | replication_paused | replication_delay

------------------+-----------------+-----------------+--------------------+-------------------

 66A/8F77A330     | 66A/8F77A330    | t               | f                  | 00:00:00.015721

(1 row)

We have several slave servers and I also have setup new fresh slave databases several times - I got the same situation each time.

I still need help with it.

Waiting for your response. Thanks.

On Thu, May 4, 2017 at 3:26 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:

On Thu, May 4, 2017 at 12:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Vitaliy Gomenyuk <vgomenyuk@callfire.com> writes:
> [ same query slower on slave ]

Hmm, the discrepancy is evidently in the larger bitmap index scan:

> There is an execution plan from master:
>                      ->  Bitmap Index Scan on "OutgoingMessages_Status_StampToSend_Deleted"  (cost=0.00..3556.90 rows=80249 width=0) (actual time=139.761..139.761 rows=9158 loops=1)
>                            Index Cond: ((om."Status" = 0) AND (om."Deleted" = false))
>                            Buffers: shared hit=70252

> There is an execution plan from slave:
>                      ->  Bitmap Index Scan on "OutgoingMessages_Status_StampToSend_Deleted"  (cost=0.00..3556.90 rows=80249 width=0) (actual time=1470.853..1470.853 rows=8671249 loops=1)
>                            Index Cond: ((om."Status" = 0) AND (om."Deleted" = false))
>                            Buffers: shared hit=70252

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?

Hi Vitaliy,

Is it possible for you check the status of the replication? and also is it possible

for you to create another fresh slave and check whether the issue is happening

there also?

Regards,

Hari Babu

Fujitsu Australia

--

Best regards,

Vitaliy Gomenyuk Senior DBA vgomenyuk@callfire.com | +380 67 220 5903  email | website | Facebook | Twitter