Re: pg_autovacuum

Matt,

> So interesting, most uses request the per table settings, guess there
> is sufficient demand for both.

The reason for us is that multi-database installations generally have
significantly different purposes for each database; for example, with a
"reporting" database on the same server that needs no vacuuming at all.

> You might be missing the point, the advantage of using pg_autovacuum is
> that it wouldn't waste cycles doing vacuums on tables that don't need
> it. If we have persistent data (saving state information periodically)
> then this is a very easy feature to add.

OK, I can see that.

> What I'm thinking is that the VACUUM command could be modified to write
> down some data from the stats system at vacuum time. Once the VACUUM
> command writes this down for itself then pg_autovacuum just uses that
> number to make its decision. Again, we are trying to reduce as much as
> possible superfluous vacuums. If an admin vacuums his whole cluster
> every Sunday night that may prevent lots of vacuums occurring during
> business hours that effect processing.

Be nice, yes. However, my experience is that mixing manual and autovacuums
is bound to lead to endless support requests, because conflicts *will* arise.
So in some ways you'd be working to please those who can't be pleased.

> Backend integration should solve the 1st issue. Parallel vacuums is
> something that could be worked on at some point. Would it make sense
> to incorporate this with tablespaces? The vacuum daemon would only
> issue one vacuum command per tablespace, but could issue as many
> parallel vacuums as you have independent tablespaces.

Hmmm ... that's an interesting idea. I'd more been thinking about vacuums
of small tables, where a high-end server under low load could vacuum several
tables in parallel, one per CPU. However, working through tablespaces would
make a lot of sense.

> I think timeout issue would need to be a part of vacuum proper, and I'm
> not sure about the "backing up" issue.

Well, we've discussed timeout for vacuum.

Thing is, autovacuum changes the equation somewhat. Imagine that the
transaction rate of your tables accelerates so that autovacuum with a 0.3
scale setting is triggered every 23 minutes. But say that it takes 29
minutes to vacuum through all of your tables ... or even 49 minutes if you
have "slow vacuum" turned on!

You would get into a cycle where you are running vacuum continuously, all the
time. This is a very bad situation and the admin should be warned about it
via the logs.

Hmmm ... thinkiing about that, are we changing the defaults for threshold and
scale? You and I have discussed this, yes?

> The reason it's similar is that once pg_autovacuum data is persistent,
> it would be trivial to implement this feature, and the data that any
> tool would need to make these decisions is the same as what
> pg_autovacuum is already tracking.

Well, if it's easy to do, then go for it. I can see how some would find it
useful. Once it's sufficently bulletproof, it could replace the standard
VACUUM (whole db).

> I think the patch was submitted to either the hackers or patches list.
> If you can't find it, I'll look around and see if I still have a copy.
> The person who submitted said it was simple, but was working for him in
> production.

Thanks for the forward.

--
-Josh Berkus
Aglio Database Solutions
San Francisco