Folks,
Yeah, I can't believe I'm calling for *yet another* configuration
variable either. Suggested workaround fixes very welcome.
The basic issue is that autovacuum_max_workers is set by most users
based on autovac's fairly lightweight action most of the time: analyze,
vacuuming pages not on the visibility list, etc. However, when XID
wraparound kicks in, then autovac starts reading entire tables from disk
... and those tables may be very large.
This becomes a downtime issue if you've set autovacuum_max_workers to,
say, 5 and several large tables hit the wraparound threshold at the same
time (as they tend to do if you're using the default settings). Then
you have 5 autovacuum processes concurrently doing heavy IO and getting
in each others' way.
I've seen this at two sites now, and my conclusion is that a single
autovacuum_max_workers isn't sufficient if to cover the case of
wraparound vacuum. Nor can we just single-thread the wraparound vacuum
(i.e. just one worker) since that would hurt users who have thousands of
small tables.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com