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

Re: Finetuning Autovacuum

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Benjamin Krajmalnik <kraj(at)illumen(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Finetuning Autovacuum
Date: 2010-01-05 03:40:29
Message-ID: 20100105034029.GR3778@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-admin
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

Responses

pgsql-admin by date

Next:From: Benjamin KrajmalnikDate: 2010-01-05 03:45:17
Subject: Re: Finetuning Autovacuum
Previous:From: Scott MarloweDate: 2010-01-05 03:01:28
Subject: Re: Finetuning Autovacuum

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