[check_postgres] Changes to last_autovacuum check

[David Christensen]

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