[check_postgres] check_postgres_bloat and filtering with include

[Roy McMorran]

On 8/1/11 11:40 AM, Greg Sabino Mullane wrote:
> On Thu, Jun 09, 2011 at 02:26:31PM -0400, Roy McMorran wrote:
>> ...with this, where I use "--include" to specify the "edit" schema:
>>
>> $ /usr/local/nagios/libexec/check_postgres_bloat --showtime=0
>> --showperf=0 --host=db.example.org --dbname=prd30 --include=edit.
>> --warning=150% --critical=200%
>> POSTGRES_BLOAT WARNING: DB "prd30" (host:db.example.org) (db prd30)
>> index list_db_report_pk rows:? pages:4 shouldbe:1 (4.0X) wasted
>> bytes:24576 (24 kB) * (db prd30) index db_report_ak1 rows:? pages:4
>> shouldbe:1 (4.0X) wasted bytes:24576 (24 kB)
>>
>> I would expect in the second case that the two indexes shown would not
>> cause a warning because they are smaller than 15 pages (per the man
>> page).  Indeed they were supressed in the first example.
> The MINPAGES and MINIPAGES are not used if you explicitly include
> or exclude anything. Whether this was the right design decision I'm
> not sure of, but that's what the code is doing. :) If you want to
> get the behavior you desire, take a look at the if blocks inside
> sub check_bloat right after the giant SQL statement.
>
> I'm also curious why it is giving a warning rather than a
> critical here. Anyone?
>
>
Thanks for the pointer Greg. What I've done is simply this:

diff -u check_postgres_bloat.orig check_postgres_bloat
--- check_postgres_bloat.orig 2011-08-11 13:54:42.291441000 -0400
+++ check_postgres_bloat 2011-08-11 13:55:22.045040000 -0400
@@ -2829,13 +2829,8 @@
) AS sml
};

- if (! defined $opt{include} and ! defined $opt{exclude}) {
$SQL .= " WHERE sml.relpages - otta > $MINPAGES OR ipages - iotta > 
$MINIPAGES";
$SQL .= " ORDER BY wastedbytes DESC LIMIT $LIMIT";
- }
- else {
- $SQL .= ' ORDER BY wastedbytes DESC';
- }

if ($psql_version <= 7.4) {
$SQL =~ s/BLOCK_SIZE/(SELECT 8192) AS bs/;

...and now I'm getting the behavior I had hoped for. BTW, it might be 
nice for MINPAGES/MINIPAGES to be a command line option.

I'll keep an eye out for that warning/critical discrepancy; have not 
noticed it since. Thanks again and best wishes,

-- 
Roy McMorran
Systems Administrator
MDI Biological Laboratory
mcmorran at mdibl.org