X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 6524D9DC87B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 18:52:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 35536-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Jan 2006 18:52:59 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id 9391B9DC820
	for <pgsql-performance@postgresql.org>;
	Wed, 18 Jan 2006 18:52:54 -0400 (AST)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 4DD2739820; Wed, 18 Jan 2006 16:52:57 -0600 (CST)
Date: Wed, 18 Jan 2006 16:52:57 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Michael Crozier <crozierm@conducivetech.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Autovacuum / full vacuum (off-topic?)
Message-ID: <20060118225257.GY17896@pervasive.com>
References: <dqigaf$1b98$1@news.hub.org>
	<034701c61c46$f427df20$f20214ac@bite.lt>
	<60d5ipcw8y.fsf@dba2.int.libertyrms.com>
	<200601181115.51850.crozierm@conducivetech.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200601181115.51850.crozierm@conducivetech.com>
X-Operating-System: FreeBSD 6.0-RELEASE amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.095 required=5 tests=[AWL=0.095]
X-Spam-Score: 0.095
X-Spam-Level: 
X-Archive-Number: 200601/316
X-Sequence-Number: 16794

On Wed, Jan 18, 2006 at 11:15:51AM -0800, Michael Crozier wrote:
> I've been thinking about parsing the vacuum output and storing it in 
> Postgresql.  All the tuple, page, cpu time, etc... information would be 
> inserted into a reasonably flat set of tables.
<snip>
> Assuming this isn't a fundamentally horrible idea, it would be nice if there 
> were ways to do this without parsing the pretty-printed vacuum text (ie, 
> callbacks, triggers, guc variable).

The best way to do this would be to modify the vacuum code itself, but
the issue is that vacuum (or at least lazyvacuum) doesn't handle
transactions like the rest of the backend does, so I suspect that there
would be some issues with trying to log the information from the same
backend that was running the vacuum.

> I'd like to know if anybody does this already, thinks its a bad idea, or can 
> knock me on the noggin with the pg manual and say, "it's already there!".

I think it's a good idea, but you should take a look at the recently
added functionality that allows you to investigate the contests of the
FSM via a user function (this is either in 8.1 or in HEAD; I can't
remember which).
-- 
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