[check_postgres] Changes to last_autovacuum check
David Christensen
david at endpoint.com
Thu Jan 27 03:55:37 UTC 2011
On Jan 22, 2011, at 5:51 PM, Josh Berkus wrote:
> All,
>
> I was looking at last_autovacuum (and last_autoanalyze) and trying to
> improve it; the current check seems to cause a lot of false positives by
> reporting tables which haven't been updated in quite some time. Here's
> my first stab at a query for "table has not been autovacuumed"; rather
> than depending on time, it checks for tables whose ratio of dead rows is
> more than 2X what autovacuum is supposed to tolerate:
>
> SELECT schemaname, relname,
> GREATEST(last_vacuum, last_autovacuum) as last_vacuumed
> FROM pg_stat_user_tables,
> ( SELECT setting::numeric as scalefactor
> FROM pg_settings
> WHERE name = 'autovacuum_vacuum_scale_factor' )
> as scalefactor,
> ( SELECT setting::numeric as threshold
> FROM pg_settings
> WHERE name = 'autovacuum_vacuum_threshold' )
> as threshold
> WHERE n_dead_tup > threshold
> and
> ( ( n_dead_tup - threshold ) /
> (CASE WHEN n_live_tup = 0
> THEN 1 ELSE n_live_tup END ))
>> ( scalefactor * 2 );
This query only works from 8.3 onward, in case that's a consideration.
So as far as the basic check is concerned, the current deficiency is that it's purely a time-based one, which particular access patterns will trigger the false positives for? I can see the utility in this particular approach, however it seems that either definition could be useful, so perhaps this would merit a separate action? (It kind of gets fuzzy when comparing autovac threshold criteria and general bloat, say.)
Regards,
David
--
David Christensen
End Point Corporation
david at endpoint.com
More information about the Check_postgres
mailing list