Обсуждение: Vacuum of large tables causing replication delays to hot standby
Hello All,
In an attempt to offload some of the pressure off our master postgres node,
We recently decided to start running reports off of our hot-standby.
There is a desire for these reports to return fairly current data, so we have been monitoring the replication delay between the master -> standby.
We currently have max_standby_archive_delay and max_streaming_archive_delay set to -1, to avoid any timeouts in the application (when pulling reports).
hot_standby_feedback is enabled on the slave node, but we are not currently setting vacuum_defer_cleanup_age.
95% of the time, the delay is only microseconds. But we have discovered that whenever the master does an auto vacuum of a large table, the transaction replay delay can climb is high as 1 hour. These delays don’t seem to correlate with any particular queries that are running against the master or the standby, and the delay only subsides when the vacuum completes.
Does anyone have any recommendations for a configuration that can minimize the replay delays that occur during the vacuums of large tables.
--
Thank you,
Jeff McDowell
Email: Jeff.mcdowell@panerabread.com
Jeff Mcdowell <Jeff.Mcdowell@panerabread.com> writes:
> 95% of the time, the delay is only microseconds. But we have discovered that whenever the master does an auto vacuum
ofa large table, the transaction replay delay can climb is high as 1 hour. These delays don�t seem to correlate with
anyparticular queries that are running against the master or the standby, and the delay only subsides when the vacuum
completes.
What PG version might this be?
regards, tom lane
Tom Lane wrote:
> Jeff Mcdowell <Jeff.Mcdowell@panerabread.com> writes:
> > 95% of the time, the delay is only microseconds. But we have discovered that whenever the master does an auto
vacuumof a large table, the transaction replay delay can climb is high as 1 hour. These delays don�t seem to correlate
withany particular queries that are running against the master or the standby, and the delay only subsides when the
vacuumcompletes.
>
> What PG version might this be?
This sounds related to
commit 3e4b7d87988f0835f137f15f5c1a40598dd21f3d
Author: Simon Riggs <simon@2ndQuadrant.com>
AuthorDate: Sun Apr 3 17:46:09 2016 +0100
CommitDate: Sun Apr 3 17:46:09 2016 +0100
Avoid pin scan for replay of XLOG_BTREE_VACUUM in all cases
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
9.2.12 Sent from my iPhone > On Apr 30, 2016, at 12:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Jeff Mcdowell <Jeff.Mcdowell@panerabread.com> writes: >> 95% of the time, the delay is only microseconds. But we have discovered that whenever the master does an auto vacuum ofa large table, the transaction replay delay can climb is high as 1 hour. These delays don’t seem to correlate with anyparticular queries that are running against the master or the standby, and the delay only subsides when the vacuum completes. > > What PG version might this be? > > regards, tom lane