2

I'm currently working with a relatively big postgres 9.6 server with multiple databases and observing the following behavior:

The server has 5 max parallel workers configured but currently, only one vacuum process running (to prevent wraparound though). Some tables should be auto vacuumed since they reached the configured thresholds to trigger AV but AV is not starting. Instead, the Vacuum to prevent wraparound is sequentially vacuuming tables with XID older than 200M.

Is this expected that the regular auto vacuum process is put on hold while the vacuum to prevent wraparound is running? I don't think that the server is currently at risk of actually running into the wraparound since it will finish in time but I've never noticed such behavior before on other servers. Is this something special to 9.6 or is there any setting that might cause this?

1 Answer 1

2

There is a unfixed bug starting long before 9.6 (which as far as I know has not intentionally been fixed any newer versions either) where if any database is older than autovacuum_freeze_max_age, all autovacuum workers get diverted to that database, starving all other databases of any autovac activity. If there is only one table which needs vacuuming in that database, then all the other workers (autovacuum_max_workers - 1 of them) will just keep evaluating that database over and over again and finding nothing to do.

This is not special to 9.6, but you will only see it if you have multiple databases which see meaningful activity, and you hit the autovacuum_freeze_max_age threshold (many systems never do that), and you are paying careful attention.

You could increase autovacuum_freeze_max_age to restore 'regular order' to the autovac procedures, just be sure to keep an eye on things and make sure the anti-wraparound vacuum does eventually finish successfully, then lower it back down again. You should not leave it elevated, because eventually the problem will just kick in again but at that time it will be even more severe than this time was.

EDIT: But, you can't change autovacuum_freeze_max_age without doing a server restart, which of course will interrupt the autovac currently going on. So for this to work you would need to increase the value, restart the server, and then manually VACUUM FREEZE whatever table is currently being worked on

max_parallel_works has nothing to do with this.

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.