[Pgpool-general] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

[Paul Robert Marino]

The autovacuum process is suppose to reduce the frequence of needing a
vacuum full or vacuum freeze not completly replace it.
Do you regularly schedule at least a vacuum freeze if not this may be your
problem.
Also I've seen this happen when a server went too long betwean running
vacuum full and there was a transaction id wrap around issue, but you would
see that in the log.
The first piece of advice is to restart the cluster and imeadiatly do a
vacuum freeze this will do every thing short of doing an exclusive table
lock. The next step is to plan a vacuum full on each the tables. The vacuum
full will reqiure an exclusive table lock so you will not be able to query
or update the tables durring this time but the earrlier vacuum freeze will
have accelerated the process. note you may do multiple vacuums concurently
on seperate tables (but not multiple on the same at once) if downtime is an
issue, you'll still run into io contention but may be better able to take
advantage of your cpu and ram if you do it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20111122/eecd4b0c/attachment-0001.html>