Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 060C39FB726
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 12 May 2007 13:51:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 39404-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 12 May 2007 13:51:22 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id E26209FB68A
	for <pgsql-performance@postgresql.org>;
	Sat, 12 May 2007 13:51:22 -0300 (ADT)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 9F052564DB; Sat, 12 May 2007 11:51:21 -0500 (CDT)
Received: (hashcash-sendmail, from uid 1001); Sat, 12 May 2007 11:51:18 -0500
Date: Sat, 12 May 2007 11:51:18 -0500
From: "Jim C. Nasby" <jim@nasby.net>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Guillaume Cottenceau <gc@mnc.ch>, pgsql-performance@postgresql.org
Subject: Re: estimating the need for VACUUM FULL and REINDEX
Message-ID: <20070512165118.GC52939@nasby.net>
References: <87odkvmsav.fsf@meuh.mnc.lan> <87lkfve5r3.fsf@meuh.mnc.lan>
	<20070511172504.GH17314@alvh.no-ip.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20070511172504.GH17314@alvh.no-ip.org>
X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.13 (2006-08-11)
X-Hashcash: 
 1:20:070512:alvherre@commandprompt.com::LGOaESoHde8LA0Zy:00000000000
	0000000000000000000000001F7Y
X-Hashcash: 1:20:070512:gc@mnc.ch::0raFyvmIBmymcCVP:00000000AAAB
X-Hashcash: 
 1:20:070512:pgsql-performance@postgresql.org::k6mEr5kXjk1TEaYP:00000
	0000000000000000000000000wV9
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/240
X-Sequence-Number: 24630

On Fri, May 11, 2007 at 01:25:04PM -0400, Alvaro Herrera wrote:
> Guillaume Cottenceau wrote:
> > Guillaume Cottenceau <gc 'at' mnc.ch> writes:
> > 
> > > With that in mind, I've tried to estimate how much benefit would
> > > be brought by running VACUUM FULL, with the output of VACUUM
> > > VERBOSE. However, it seems that for example the "removable rows"
> > > reported by each VACUUM VERBOSE run is actually reused by VACUUM,
> > > so is not what I'm looking for.
> > 
> > I've tried to better understand how autovacuum works (we use 7.4)
> > to see if a similar mechanism could be used in 7.4 (e.g. run
> > VACUUM ANALYZE often enough to not end up with a need to VACUUM
> > FULL).
> > 
> > The autovacuum daemon uses statistics collected thanks to
> > stats_row_level. However, inside pg_stat_user_tables, the values
> > n_tup_upd and n_tup_del seem to be reported from pg startup and
> > never reset, whereas the information from previous VACUUM would
> > be needed here, if I understand correctly. Is there anything that
> > can be done from that point on with existing pg information, or
> > I'd need e.g. to remember the values of my last VACUUM myself?
> 
> In 7.4 there was the pg_autovacuum daemon in contrib, wasn't there?  No
> need to write one yourself.

Correct. But one important note: the default parameters in the 7.4
contrib autovac are *horrible*. They will let your table grow to 3x
minimum size, instead of 1.4x in 8.0/8.1 and 1.2x in 8.2. You must
specify a different scale if you want anything resembling good results.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)