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".
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