From: Jim C. Nasby <jim@nasby.net>
To: Jeremy Haile <jhaile@fastmail.fm>
To: pgsql-performance@postgresql.org
Subject: Re: High inserts, bulk deletes - autovacuum vs scheduled vacuum
Date: Wed, 10 Jan 2007 15:21:26 -0600
Message-ID: <20070110212125.GZ12217@nasby.net> (raw)
In-Reply-To: <1168461780.9408.1168619515@webmail.messagingengine.com>
References: <1168363601.5177.1168380907@webmail.messagingengine.com>
<20070110203556.GX12217@nasby.net>
<1168461780.9408.1168619515@webmail.messagingengine.com>
Please cc the list so others can help and learn.
On Wed, Jan 10, 2007 at 03:43:00PM -0500, Jeremy Haile wrote:
> > I'd suggest trying autovacuum and see how it does (though you might want
> > to tune it to be more or less aggressive, and you'll probably want to
> > enable the cost delay).
>
> What are some decent default values for the cost delay vacuum settings?
> I haven't used these before.
I find that simply setting vacuum_cost_delay to 20 is generally a good
starting point. I'll usually do that and then run a vacuum while
watching disk activity; I try and tune it so that the disk is ~90%
utilized with vacuum running. That allows a safety margin without
stretching vacuums out forever.
> Also - do the default autovacuum settings make sense for tables on the
> scale of 10 million rows? For example, using the defaults it would
> require about a million rows (250 + 0.1 * 10 million) to be
> inserted/updated/deleted before analyzing - which seems high. (about 2
> million for vacuum) Or am I overestimating how often I would need to
> vacuum/analyze these tables?
Depends on your application... the way I look at it is that a setting of
0.1 means 10% dead space in the table. While 5% or 1% would be better,
you hit a point of diminishing returns since you have to read the entire
table and it's indexes to vacuum it.
BTW, that's the default values for analyze... the defaults for vacuum
are 2x that.
> Do most people use the default autovacuum settings successfully, or are
> they usually modified?
I generally use the 8.2 defaults (which are much better than the 8.1
defaults) unless I'm really trying to tune things. What's more important
is to make sure critical tables (such as queue tables) are getting
vacuumed frequently so that they stay small. (Of course you also need to
ensure there's no long running transactions).
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
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: jim@nasby.net, jhaile@fastmail.fm
Subject: Re: High inserts, bulk deletes - autovacuum vs scheduled vacuum
In-Reply-To: <20070110212125.GZ12217@nasby.net>
* 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