Re: Slow Vacuum was: vacuum output question

On Tue, Dec 30, 2008 at 10:37:04AM -0600, Dan Armbrust wrote:

> The way that they reported the problem to us was that if they enable
> autovacuum, when ever it runs (about 4 times an hour) it would stop
> processing the things it needed to process, due to table lock
> contention for several minutes.

If that's true, there are a couple possibilities for why. One of them
is that autovacuum needs tuning. Another is that certain tables
aren't really good candidates for autovacuum (<8.3, this is pretty
common). Another is that they're starved for I/O; you need to check
that when processing and vacuum is happening, not the rest of the
time. Another is that they're doing something like explicit locking
or very large numbers of UPDATEs in one transaction, and vacuum is
just highlighting a problem with what they're doing.

> Their workaround had been to run a daily autovacuum at the lowest load
> time of day, to cause the least disruption.

What is a "daily autovacuum"? It sounds like some tables just need
vacuuming more often. If they find that the system is not responsive
during that, it tells us that they need more disk bandwidth or that
they need to integrate vacuuming some tables with their program.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca