Re: large table vacuum issues

On Jan 4, 2008 6:38 PM, Ed L. <pgsql@bluepolka.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.