Date: Fri, 9 Nov 2007 18:49:21 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: David Crane <davidc@donorschoose.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Can I Determine if AutoVacuum Does Anything?
Message-ID: <20071109214921.GI7161@alvh.no-ip.org>
References: <41ED0E73B2268F4D9E4081FAB5ED05FD0351BB0E@midas.utopiasystems.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <41ED0E73B2268F4D9E4081FAB5ED05FD0351BB0E@midas.utopiasystems.net>
User-Agent: Mutt/1.5.16 (2007-06-11)

David Crane wrote:
> We've had our PostgreSQL 8.1.4 installation configured to autovacuum
> since January, but I suspect it might not be doing anything.  Perhaps I
> can determine what happens through the log files?  Is there a summary of
> which "when to log" settings in postgresql.conf should be set to get at
> least table-level messages about yes/no decisions?  The only message I
> see now is very terse, indicating that autovacuum does run:

Yeah, you have to set log_min_messages to debug2 to get useful output
for autovacuum.  This is fixed in 8.3, but for earlier version there is
nothing short of patching the server.

>     autovacuum = on
> 
>     autovacuum_naptime = 360

This is a bit on the high side, but it may not be very important.  Keep
in mind that in 8.2 and earlier, it means "how long between autovac
checks", so if there are many databases, it could be long before one
autovac run in a particular database and the next one.  (In 8.3 it has
been redefined to mean the interval between runs on every database).

>     autovacuum_vacuum_threshold = 1000
>     autovacuum_analyze_threshold = 500

These are the default values but for small tables they seem high as
well.  IIRC your problem is actually with big tables, for which it
doesn't make much of a difference.


-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support