[check_postgres] Changes to last_autovacuum check
Josh Berkus
josh at agliodbs.com
Thu Feb 3 18:59:14 UTC 2011
> That is definitely a concern, and perhaps strengthens the argument for a
> separate action. Or perhaps the same action but controllable by some other
> flag as to how it does it?
Yeah, I'm thinking of it as a seperate check. And actually this check
is only really good with 8.4 and later, because in 8.3 the dead_rows
numbers are not trustworthy.
> I think the check as suggested by Josh is more of a "is autovacuum working"
> rather than a "here's some tables we should autovacuum more agggressively"
> approach.
Well, quite frankly, I don't find the old autovac check ("Which tables
have not been autovacuumed in 24 hours?") at all useful in production,
and I never enable it. It's quite rare to have a database in which the
update pattern to tables is uniform, either between tables or around the
clock. I know that you and I disgree on the degree to which false
positives are a problem, though.
What the new check would detect a lot more accurately is the only
pathological situation which is really possible in 8.4 and later; that
is, autovacuum not keeping up (whether due to blocks, settings, too many
tables or too much activity).
> Other ideas. Have an action that combines both autovac and
> regular vac for purposes of the check.
Actually, the query does that. If you noticed, it references both
last_vacuum and last_autovacuum.
> Exclude tables that are excluded
> from autovac by pg_autovacuum / reloptions.
I'd rather parse the reloptions and check if those tables are being
vacuumed on schedule as well. In some ways, those are going to be the
most critical tables to check.
> Find some other way to exclude
> non-busy tables without undermining the original intent of the check,
> which is to find non-busy tables that are not getting vac'd....
I don't understand this. Why would you want to vacuum a non-busy table?
Now, here's the hard part: how do we do the same thing for autoanalyze?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
More information about the Check_postgres
mailing list