From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Autovacuum / full vacuum (off-topic?)
Date: Wed, 18 Jan 2006 17:04:51 -0500
Organization: cbbrowne Computing Inc
Lines: 49
Message-ID: <60bqy9nqf0.fsf@dba2.int.libertyrms.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
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.18 (linux)
Cancel-Lock: sha1:BK5/FY2PRHw809ItOS3rCaLNiw4=
To: pgsql-performance@postgresql.org

crozierm@conducivetech.com (Michael Crozier) writes:

> On Wednesday 18 January 2006 08:54 am, Chris Browne wrote:
>> To the contrary, there is a whole section on what functionality to
>> *ADD* to VACUUM.
>
> Near but not quite off the topic of VACUUM and new features...
>
> 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.
>
> The benefits I would expect from this are:
>
> * monitoring ability - I could routinely monitor the values in the
> table to warn when vacuum's are failing or reclaimed space has risen
> dramatically.  I find it easier to write and maintain monitoring
> agents that perform SQL queries than ones that need to routinely
> parse log files and coordinate with cron.
>
> * historical perspective on tuple use - which a relatively small
> amount of storage, I could use the vacuum output to get an idea of
> usage levels over time, which is beneficial for planning additional
> capacity
>
> * historical information could theoretically inform the autovacuum,
> though I assume there are better alternatives planned.
>
> * it could cut down on traffic on this list if admin could see
> routine maintenance in a historical context.
>
> 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).
>
> 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!".

We had someone working on that for a while; I don't think it got to
the point of being something ready to unleash on the world.

I certainly agree that it would be plenty useful to have this sort of
information available.  Having a body of historical information could
lead to having some "more informed" suggestions for heuristics.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/unix.html
Bad command. Bad, bad command! Sit! Stay! Staaay...