On Fri, 2007-11-16 at 12:13 +0100, Tobias Brox wrote:
> [snip] should i use both auto-vacuum and
> > manual-vacuum?
I would say for 8.2 that's the best strategy (which might change with
8.3 and it's multiple vacuum workers thingy).
> That being said, we have some huge tables in our database and pretty
> much traffic, and got quite some performance problems when the
> autovacuum kicked in and started vacuuming those huge tables, so we're
> currently running without. Autovacuum can be tuned to not touch those
> tables, but we've chosen to leave it off.
We are doing that here, i.e. set up autovacuum not to touch big tables,
and cover those with nightly vacuums if there is still some activity on
them, and one weekly complete vacuum of the whole DB ("vacuum" without
other params, preferably as the postgres user to cover system tables
too).
In fact we also have a few very frequently updated small tables, those
are also covered by very frequent crontab vacuums because in 8.2
autovacuum can spend quite some time vacuuming some medium sized tables
and in that interval the small but frequently updated ones get bloated.
This should be better with 8.3 and multiple autovacuum workers.
For the "disable for autovacuum" part search for pg_autovacuum in the
docs.
I would say the autovacuum + disable autovacuum on big tables + nightly
vacuum + weekly vacuumdb + frequent crontab vacuum of very updated small
tables works well in 8.2. One thing which could be needed is to also
schedule continuous vacuum of big tables which are frequently updated,
with big delay settings to throttle the resources used by the vacuum. We
don't need that here because we don't update frequently our big
tables...
Cheers,
Csaba.
