From: Jim C. Nasby <jim@nasby.net>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: estimating the need for VACUUM FULL and REINDEX
Date: Sat, 12 May 2007 11:51:18 -0500
Message-ID: <20070512165118.GC52939@nasby.net> (raw)
In-Reply-To: <20070511172504.GH17314@alvh.no-ip.org>
References: <87odkvmsav.fsf@meuh.mnc.lan>
<87lkfve5r3.fsf@meuh.mnc.lan>
<20070511172504.GH17314@alvh.no-ip.org>
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)
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: jim@nasby.net, alvherre@commandprompt.com, gc@mnc.ch
Subject: Re: estimating the need for VACUUM FULL and REINDEX
In-Reply-To: <20070512165118.GC52939@nasby.net>
* 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