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

Re: Finetuning Autovacuum

From: "Benjamin Krajmalnik" <kraj(at)illumen(dot)com>
To: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>,"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Finetuning Autovacuum
Date: 2010-01-05 03:45:17
Message-ID: F4E6A2751A2823418A21D4A160B68988613C65@fletch.stackdump.local (view raw or flat)
Thread:
Lists: pgsql-admin
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.

In response to

pgsql-admin by date

Next:From: Benjamin KrajmalnikDate: 2010-01-05 05:00:51
Subject: Re: Finetuning Autovacuum
Previous:From: Alvaro HerreraDate: 2010-01-05 03:40:29
Subject: Re: Finetuning Autovacuum

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