Skip site navigation (1) Skip section navigation (2)

Re: Finetuning Autovacuum

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: Benjamin Krajmalnik <kraj(at)illumen(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Finetuning Autovacuum
Date: 2010-01-05 13:35:55
Message-ID: 1262698555.29852.9.camel@bnicholson-desktop (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
On Mon, 2010-01-04 at 22:00 -0700, Benjamin Krajmalnik wrote:
> Alvaro, Scott - thanks for your replies and the direction you pointed me
> into.
> The underlying problem was that the cost limit was too low, so the
> autovacuum process would run forever and not be able to do anything.  I
> reduced the cost delay and increased the cost limit form the default of
> 200 to 10000, which is slightly more than the longest time I saw when
> running manually.  Autovacuum now works as expected - the tables, which
> have a very high rate of updates and some inserts (very few) are not
> growing anymore.  The run time for the scheduled background processes
> (which perform some background aggregation) have gone down slightly
> (about 10%) as well.  Since autovacuum will prevent a deadlock, it is
> definitely preferable to me over manually vacuuming.
> Once again, thanks! 

As an aside - are what percentage of your updates HOT updates?  If it's
low, and you can change your indexing (not having an index on the
column(s) that is updated), or set the fill factor if needed in such a
way that you can get into doing a larger number of HOT updates - that
will be very beneficial in for your usage pattern.

Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

In response to

pgsql-admin by date

Next:From: Gurjeet SinghDate: 2010-01-07 08:12:43
Subject: Re: How can i change the Postgres Database password?
Previous:From: Benjamin KrajmalnikDate: 2010-01-05 05:00:51
Subject: Re: Finetuning Autovacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group