From: Csaba Nagy <nagy@ecircle-ag.com>
To: Tobias Brox <tobias@nordicbet.com>
Cc: Gábor Farkas <gabor@nekomancer.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum: recommended?
Date: Fri, 16 Nov 2007 12:56:34 +0100
Message-ID: <1195214194.5424.21.camel@PCD12478> (raw)
In-Reply-To: <20071116111355.GB1195@mail.nordicbet.com>
References: <473D659B.1070008@nekomancer.net>
<20071116111355.GB1195@mail.nordicbet.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagy@ecircle-ag.com, tobias@nordicbet.com, gabor@nekomancer.net
Subject: Re: autovacuum: recommended?
In-Reply-To: <1195214194.5424.21.camel@PCD12478>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox