We need some advice on how to handle some large table autovacuum
issues. One of our 8.1.2 autovacuums is launching a DB-wide
vacuum on our 270GB database to prevent xid wrap-around, but is
getting hung-up and/or bogged down for hours on a 40gb table and
taking the server performance down with it, apparently due to an
IO bottleneck. The autovac child process becomes completely
unresponsive to SIGTERM/SIGINT; only a sigkill restart with
disabling the autovac daemon gets us back to adequate
performance for now.
We are discussing how to partition the table (difficult due to
existing foreign keys in other tables), and archiving/clearing
data.
Are there any other tricks to get it past this large table for
the time being and still get the xid wraparound fix?
TIA.
Ed