From: Jim C. Nasby <jnasby@pervasive.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: tgl@sss.pgh.pa.us, Jeff Kirby <Jeff.Kirby@wicourts.gov>
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum Full Analyze Stalled
Date: Mon, 3 Oct 2005 16:36:02 -0500
Message-ID: <20051003213602.GK40138@pervasive.com> (raw)
In-Reply-To: <20051003201225.GA11527@surnet.cl>
References: <s3413ba7.062@gwmta.wicourts.gov>
<20051003194114.GD40138@pervasive.com>
<20051003201225.GA11527@surnet.cl>
On Mon, Oct 03, 2005 at 04:12:25PM -0400, Alvaro Herrera wrote:
> On Mon, Oct 03, 2005 at 02:41:14PM -0500, Jim C. Nasby wrote:
> > I haven't looked in detail at autovacuum in 8.1 yet, but I know that in
> > previous versions it was a bad idea to depend on it to vacuum a small
> > table that has a lot of update activity frequently enough. The issue is
> > that since it's single-threaded if it starts a vacuum on a large table
> > it could be hours before it gets around to vacuuming the small table
> > again.
>
> Does it really make a lot of difference? While the big vacuum is
> running, old tuples accumulate on the small table, because it shows as a
> running transaction and the small vacuum won't delete them. The new
> autovacuum is no different than the old one in this regard. (There's a
> patch by Hannu Krossing IIRC that, if included in 8.2, will make things
> better.)
Hrm, http://archives.postgresql.org/pgsql-patches/2005-07/msg00086.php
indicates that multiple transactions are used during vacuum (see item
1). If that's the case, it is still better to run a stand-alone vacuum
if there are tables being autovacuumed that are large and have indexes.
In any case, Tom's got some serious objections to that patch, so it
might never make it in. I'd like to see an item on the TODO so this
doesn't slip through the cracks. It's also possible that a simpler,
cleaner version of this would be to have vacuum do it's work in batches
rather than messing around with visibility code (which I agree is
dangerous).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jnasby@pervasive.com, Kevin.Grittner@wicourts.gov, Jeff.Kirby@wicourts.gov
Subject: Re: Vacuum Full Analyze Stalled
In-Reply-To: <20051003213602.GK40138@pervasive.com>
References: <s3413ba7.062@gwmta.wicourts.gov> <20051003194114.GD40138@pervasive.com> <20051003201225.GA11527@surnet.cl> <20051003213602.GK40138@pervasive.com>
* 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