Re: High inserts, bulk deletes - autovacuum vs scheduled vacuum

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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net