pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org, "Matthew T. O'Connor" <matthew@zeut.net>
Subject: Re: Some vacuum & tuning help
Date: Tue, 05 Aug 2003 20:13:39 +0530
Message-ID: <3F300FF3.7064.DA8047@localhost> (raw)
In-Reply-To: <60el00jhyx.fsf@dev6.int.libertyrms.info>

On 5 Aug 2003 at 10:29, Christopher Browne wrote:

> Shridhar Daithankar wrote: 
> > I agree, specifying per table thresholds would be good in autovacuum.. 
>  
> Which begs the question of what the future direction is for pg_autovacuum.
> 
> There would be some merit to having pg_autovacuum throw in some tables
> in which to store persistent information, and at that point, it would
> make sense to add some flags to support the respective notions that:

Well, the C++ version I wrote quite a while back, which resides on gborg and 
unmaintained, did that. It was considered as table pollution. However whenever 
autovacuum stuff goes in backend as such, it is going to need a catalogue.
 
>  -> Some tables should _never_ be touched;

That can be determined runtime from stats. Not required as a special feature 
IMHO..

> 
>  -> Some tables might get "reset" to indicate that they should be
>     considered as having been recently vacuumed, or perhaps that they
>     badly need vacuuming;

Well, stats collector takes care of that. Autovacuum daemon reads that 
statistics, maintain  a periodic snapshot of the same to determine whether or 
not it needs to vacuum.

Why it crawls for a dirty database is as follows. Autovauum daemon starts, read 
statistics, sets it as base level and let a cycle pass, which is typically few 
minutes. When it goes again, it finds that lots of things are modified and need 
vacuum and so it triggers vacuum.

Now vacuum goes on cleaning entire table which might be days job continously 
postponed some one reason or another. Oops.. your database is on it's knees..

 
>  -> As you suggest, per-table thresholds;

I would rather put it in terms of pages. If any table wastes 100 pages each, it 
deserves a vacuum..

 
>  -> pg_autovacuum would know when tables were last vacuumed by
>     it...

If you maintain a table in database, there are lot of things you can maintain. 
And you need to connect to database anyway to fire vacuum..

 
>  -> You could record vacuum times to tell pg_autovacuum that you
>     vacuumed something "behind its back."

It should notice..

>  -> If the system queued up proposed vacuums by having a "queue"
>     table, you could request that pg_autovacuum do a vacuum on a
>     particular table at the next opportunity.

That won't ever happen if autovacuum is constantly running..

> Unfortunately, the "integrate into the backend" thing has long seemed
> "just around the corner."  I think we should either:
>  a) Decide to enhance pg_autovacuum, or
>  b) Not.

In fact, I would say that after we have autovacuum, we should not integrate it. 
It is a very handy tool of tighting a database. Other database go other way 
round. They develop maintance functionality built in and then create tool on 
top of it. Here we have it already done.

It's just that it should be triggered by default. That would rock..

Bye
 Shridhar

--
Bubble Memory, n.:	A derogatory term, usually referring to a person's intelligence.	See also "vacuum tube".




view thread (26+ messages)  latest in thread

Message-ID: <3F300FF3.7064.DA8047@localhost>
Permalink:  ../3F300FF3.7064.DA8047@localhost/
Also on:    postgresql.org/message-id/3F300FF3.7064.DA8047@localhost

 · 

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-performance@postgresql.org
  Cc: shridhar_daithankar@persistent.co.in, matthew@zeut.net
  Subject: Re: Some vacuum & tuning help
  In-Reply-To: <3F300FF3.7064.DA8047@localhost>
  References: <60el00jhyx.fsf@dev6.int.libertyrms.info> <3F300FF3.7064.DA8047@localhost>

* 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