Re: large table vacuum issues

On Jan 4, 2008 6:38 PM, Ed L. <pgsql(at)bluepolka(dot)net> wrote:
> We need some advice on how to handle some large table autovacuum
> issues. One of our 8.1.2

First of all, update your 8.1 install to 8.1.10. Failing to keep up
with bug fixes is negligent. who knows, you might be getting bitten
by a bug that was fixed between 8.1.2 and 8.1.10

> 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.

Have you tried adjusting the

#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits

settings to something so as to make vacuum less intrusive? might be
the easiest fix.

> Are there any other tricks to get it past this large table for
> the time being and still get the xid wraparound fix?

the other trick would be to do a dump / restore of your whole db,
which can often be quicker than vacuuming it if it's got a lot of dead
tuples in it.