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?
Date: Fri, 9 Nov 2007 18:49:21 -0300
Message-ID: <20071109214921.GI7161@alvh.no-ip.org> (raw)
In-Reply-To: <41ED0E73B2268F4D9E4081FAB5ED05FD0351BB0E@midas.utopiasystems.net>
References: <41ED0E73B2268F4D9E4081FAB5ED05FD0351BB0E@midas.utopiasystems.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, davidc@donorschoose.org
Subject: Re: Can I Determine if AutoVacuum Does Anything?
In-Reply-To: <20071109214921.GI7161@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox