Re: Some vacuum & tuning help - Mailing list pgsql-performance

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