1

My streaming replication standby server is showing a delay of a few minutes when vacuuming a large table on the master.

What is the reason for the lag, and is there a way to reduce it?

Both servers have identical hardware on a 10GB network. Relevant settings are:

wal_buffers = 16MB
wal_writer_delay = 20ms
checkpoint_segments = 16
shared_buffers = 14GB
archive_timeout = 10
maintenance_work_mem = 1GB
4
  • 1) You listed wal_buffers twice. 2) What is your maintenance_work_mem?
    – Chris
    Commented Jun 23, 2015 at 0:34
  • Updated to show maintenance_work_mem
    – Nick
    Commented Jun 23, 2015 at 0:43
  • Can you tell me your log_autovacuum_min_duration?
    – Chris
    Commented Jun 23, 2015 at 0:58
  • log_autovacuum_min_duration = -1
    – Nick
    Commented Jun 23, 2015 at 1:00

1 Answer 1

1

Nick, based on the information we've gathered, I'm going to venture a guess, but if I'm wrong, come back and let us know and there may still be something we can do. I wouldn't say I'm confident about the internals of auto-vacuum processes on hot standby servers.

Streaming Replication, aka Log-Shipping

Streaming replication, hot standby, log shipping...there's a lot of different names that people use, but the general idea is that you replicate your back up server by implementing a system where the transaction logs from your primary server are sent and re-implmented on a secondary server to keep the two identical.

Now, you've stated that when you VACUUM your table on the primary, you have significant lag on your standby, and also that your configuration for log_autovacuum_min_duration is set to -1, which disables logging of autovacuum actions. As a potential remedy, you could set this value to 0, indicating you wish to log all autovacuum operations.

My guess is that, while autovacuuming is occurring periodically on your master, since it isn't being logged, it isn't replicated on the standby. When you run a manual VACUUM, only then does the logged VACUUM become replicated, which may be what's eating into your performance on the standby.

How are things if you run a VACUUM on the master, followed immediately by another VACUUM on master? This will give us some indication of if I'm right or if I'm full of hot air.

3
  • Chris, changing log_autovacuum_min_duration to a positive value unfortunately did not decrease the lag. I believe log_autovacuum_min_duration is actually only used for reporting and does not affect the internals.
    – Nick
    Commented Jun 24, 2015 at 16:48
  • Did you try running two back-to-back vacuums to see if the lag occurs on the second vacuum action?
    – Chris
    Commented Jun 24, 2015 at 20:07
  • The parameter log_autovacuum_min_duration only controls whether or not the background operation of autovacuum if it occurs makes it to the postgres logs (not to be confused with the WAL logs used for replication). As to why there is significant lag on the replica during VACUUM think of it as a huge WRITE that happened on the master and needs to be replicated to the standby. It is indicative that the writes on master are happening at a far greater rate than the standby is able to replicate Commented May 15, 2018 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.