Re: What specific circumstances trigger Autovacuum wraparound

Hello David!

Thanks for taking the time to respond. Upgrading is not in scope right now for this server even though that is certainly something I will do down the line.

I talked to a colleague of mine and he dug up something very interesting and probably answers the behavior I'm seeing, from PG archives. So I thought I would post it here so that if this question comes up again its more easy to find it in the future for PG 9.4 specifically.

There is another setting that controls Autovacuum called autovacuum_multixact_freeze_max_age, in PG 9.5 it is easy to get the correct value for the table to see if you hit the default limit of 400 million, however in PG 9.4 and earlier the function mxid_age() does not exist, so you can't query this value directly, hence this reference from this PG Archive post is crucial:

The thread above reference a bug thread BUG #11264 https://www.postgresql.org/message-id/flat/20140827175105.GI7046%40eldon.alvh.no-ip.org#20140827175105.GI7046@eldon.alvh.no-ip.org

And in that thread we can see Alvaro Herrera write the following paragraph:

"A better way not involving mxid_age() would be to use pg_controldata to
extract the current value of the mxid counter, then subtract the current
relminmxid from that value."

Given that statement we took a look at this server and the table in question. We could see that NextMultiXactId:  2640838929 and the relminmxid for that specific table was 1791982183. Now if you take 2640838929 - 1791982183 = 848856746 you can see that this value is the double of the default value of 400 million and would explain why vacuum is running:

"As a safety device, a whole-table vacuum scan will occur for any table whose multixact-age is greater than autovacuum_multixact_freeze_max_age. Whole-table vacuum scans will also occur progressively for all tables, starting with those that have the oldest multixact-age, if the amount of used member storage space exceeds the amount 50% of the addressible storage space. Both of these kinds of whole-table scans will occur even if autovacuum is nominally disabled."

This calculation is also confirmed by looking at the git commit of mxid_age() this is exactly what this function returns and even though the 5 other tables are not over this value we suspect that due to that we are using more than 50% of the addressable storage space Autovacuum takes to "opportunity" to run for these tables also.

Now you could of course increase this value also, not sure what the max is, but I rather want to complete vacuum. So the plan for me now is that during the weekend, where there is no activity at all except for a backup, is to increase the work memory from relatively low 1024 MB to use almost all memory I have on this server, set the server to use one worker and then see if Autovacuum by itself can solve the cleanup by itself or at least close the gap so that are confident that we will catch up.

Best regards

Daniel