On 24 September 2010 19:16, Brad Nicholson <bnichols@ca.afilias.info> wrote:
[Brad Nicholson]
> Why is the vacuum dragging out over time? Is the size of your data
> increasing, are you doing more writes that leave dead tuples, or are your
> tables and/or indexes getting bloated?
Digressing a bit here ... but the biggest reason is the data size increasing.
We do have some bloat-problems as well - every now and then we decide
to shut down the operation, use pg_dump to dump the entire database to
an sql file and restore it. The benefits are dramatic, the space
requirement goes down a lot, and often some of our
performance-problems goes away after such an operation.
> Also, is there a reason why you do nightly vacuums instead of letting
> autovacuum handle the work?
If it was to me, we would have had autovacuum turned on. We've had
one bad experience when the autovacuumer decided to start vacuuming
one of the biggest table at the worst possible moment - and someone
figured autovacuum was a bad idea. I think we probably still would
need regular vacuums to avoid that happening, but with autovacuum on,
maybe we could have managed with regular vacuums only once a week or
so.
> We started doing far less vacuuming when we let
> autovacuum handle things.
What do you mean, that you could run regular vacuum less frequently,
or that the regular vacuum would go faster?