pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (17+ messages)  latest in thread

Message-ID: <20051003213602.GK40138@pervasive.com>
Permalink:  ../20051003213602.GK40138@pervasive.com/
Also on:    postgresql.org/message-id/20051003213602.GK40138@pervasive.com

 · 

reply

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