Re: Finetuning Autovacuum

Alvaro, since you are the autovacuum guru :)
What setting do you recommend I make to the autovacuum to see if I can get it to work better, and assuming I cannot get it to complete in a reasonable time, I will run vacuum manually on the 3 tables so I can kill it if I see a deadlock issue.

Obviously I prefer to use autovacuum, but I have to vacuum the files because when they bloat the performance gets affected significantly, resulting in a snowball effect.

> -----Original Message-----
> From: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
> Sent: Monday, January 04, 2010 8:40 PM
> To: Scott Marlowe
> Cc: Benjamin Krajmalnik; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Finetuning Autovacuum
>
> Scott Marlowe escribió:
> > On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik
> <kraj(at)illumen(dot)com> wrote:
>
> > > Initially, I had scheduled tasks through pgagent running a vacuum
> analyze
> > > every 15 minutes, but other posts I have read here have stated this
> could
> > > cause deadlocks, and mentioned running autovacuum is preferable
> >
> > Autovacuum is just a daemon that calls vacuum (regular) for you, so
> if
> > regular vacuum could cause deadlocks then so could autovacuum.
>
> FWIW there are some smarts in the deadlock detection code that prefer
> to
> kill autovacuum if it is blocking some other process (even if it
> doesn't
> cause a deadlock -- just blocking a user process is enough). This does
> not occur with user invoked vacuum, so there indeed can be a difference
> here. Note that this means that if you have an operation somewhere
> that
> wants to get a lock that conflicts with vacuum all the time, it could
> be
> causing autovacuum to get killed and thus never completing, leading to
> catastrophic bloat.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.