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

Re: Finetuning Autovacuum

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Benjamin Krajmalnik <kraj(at)illumen(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Finetuning Autovacuum
Date: 2010-01-05 03:01:28
Message-ID: dcc563d11001041901h73d56085h1feb562478ada83@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Mon, Jan 4, 2010 at 7:10 PM, Benjamin Krajmalnik <kraj(at)illumen(dot)com> wrote:
> Thanks, Scott.
> I would think that we would reach some sort of steady state, yet the tables appear to continue to grow.

Then it's likely you're blowing out your free space map.

> Looking at the running processes from the server status in pgAdmin, I can see that one table has been under autovacuum for 2.5 hours (vacuum/analyze).

That's autovacuum_vacuum_cost_delay biting you there.  The default
cost delay for regular vacuums is 0 (i.e. no cost delay) while the
default for autovac is something like 20ms, which is pretty high.

Do me a favor, run vacuum verbose on the db as a superuser and post
the last 20 or so lines back...  That'll tell us if you've blown out
the fsm.  Note that since you have recently fixed things up it'll
likely still look good.  Run it each night and email the last 20 lines
to yourself just to see how things are holding up.

In response to

pgsql-admin by date

Next:From: Alvaro HerreraDate: 2010-01-05 03:40:29
Subject: Re: Finetuning Autovacuum
Previous:From: Benjamin KrajmalnikDate: 2010-01-05 02:10:56
Subject: Re: Finetuning Autovacuum

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