Re: Postgres Replaying WAL slowly

[Jeff Frost <jeff@xxxxxxxxxxxxx>]

On Jun 30, 2014, at 4:57 PM, Jeff Frost <jeff@xxxxxxxxxxxxx> wrote:

On Jun 30, 2014, at 4:04 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Ah ... that's more like a number I can believe something would have
trouble coping with.  Did you see a noticeable slowdown with this?
Now that we've seen that number, of course it's possible there was an
even higher peak occurring when you saw the trouble.

Perhaps there's an O(N^2) behavior in StandbyReleaseLocks, or maybe
it just takes awhile to handle that many locks.

Did you check whether the locks were all on temp tables of the
ON COMMIT DROP persuasion?

Unfortunately not, because I went for a poor man's: SELECT count(*) FROM pg_locks WHERE mode = 'AccessExclusiveLock' 

run in cron every minute.

That said, I'd bet it was mostly ON COMMIT DROP temp tables.

The unfortunate thing is I wouldn't know how to correlate that spike with the corresponding slowdown because the replica is about 5.5hrs lagged at the moment.

Hopefully it will get caught up tonight and we can see if there's a correlation tomorrow.

And indeed it did catch up overnight and the lag increased shortly after a correlating spike in AccessExclusiveLocks that were generated by temp table creation with on commit drop.