Re: Finetuning Autovacuum

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.