[check_postgres] Changes to last_autovacuum check

[Greg Sabino Mullane]

On Wed, Jan 26, 2011 at 09:55:37PM -0600, David Christensen wrote:
> 
> 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:
...
> This query only works from 8.3 onward, in case that's a consideration.

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?

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. Other ideas. Have an action that combines both autovac and 
regular vac for purposes of the check. Exclude tables that are excluded 
from autovac by pg_autovacuum / reloptions. 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....

-- 
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/check_postgres/attachments/20110203/8332a4b3/attachment.bin