Date: Wed, 21 Feb 2007 17:51:30 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
Message-ID: <20070221205130.GD25424@alvh.no-ip.org>
Mail-Followup-To: Mark Stosberg <mark@summersault.com>,
	pgsql-performance@postgresql.org
References: <eri3hk$2dss$1@news.hub.org>
	<20070221185544.GX25424@alvh.no-ip.org>
	<eria2f$2ut7$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <eria2f$2ut7$1@news.hub.org>
User-Agent: Mutt/1.5.13 (2006-08-11)

Mark Stosberg wrote:
> Alvaro Herrera wrote:
> > Mark Stosberg wrote:
> >> When I upgraded a busy database system to PostgreSQL 8.1, I was excited
> >> about AutoVacuum, and promptly enabled it, and turned off the daily
> >> vacuum process.
> >>
> >> (
> >> I set the following, as well as the option to enable auto vacuuming
> >> stats_start_collector = true
> >> stats_row_level = true
> >> )
> >>
> >> I could see in the logs that related activity was happening, but within
> >> a few days, the performance became horrible, and enabling the regular
> >> vacuum fixed it.
> >>
> >> Eventually autovacuum was completely disabled.

> > This has been tracked down to a bug in 8.1's Windows port.  See
> > http://people.planetpostgresql.org/mha/index.php?/archives/134-8.1-on-win32-pgstat-and-autovacuum.html
> 
> Thanks for the response Alvaro. This would have been on FreeBSD.

Oh, maybe I misread your OP :-)  With "completely disabled" I thought
you meant it was "frozen", i.e., it ran, but did nothing.

> Let me ask the question a different way: Is simply setting the two
> values plus enabling autovacuuming generally enough, or is further
> tweaking common place?

I assume your FSM configuration is already good enough?

What you should do is find out what tables are not getting vacuumed
enough (e.g. by using contrib/pgstattuple repeteadly and seeing where is
dead space increasing) and tweak the autovacuum settings to have them
vacuumed more often.  This is done by inserting appropriate tuples in
pg_autovacuum.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.