[check_postgres] Issue with check_postgres bloat check.

[Jehan-Guillaume de Rorthais]

On Tue, 3 Nov 2015 15:47:39 -0500
"Shaik, Baji" <baji.shaik at openscg.com> wrote:

> Hi Jehan,
> 
> Thanks for the update.
> 
> I will try to test this with enterprisedb database, however could you
> please confirm if my understanding is correct on bloat percentage. We still
> see alerts, though tables are not bloated to 50%.

I don't remember for check_postgres. If I remember correctly, it mixes both
table AND index bloat in the output. Moreover, the bloat index was way under
estimated last time I checked (that fact actually triggered my work on bloat
estimation).

> Specifying the threshold as a percentage results in comparing the ratio of
> actual size to expected size rather than the ratio of bloat to expected
> size. Is that correct?

In check_pgactivity, the ratio express the size of the bloat in the table
(heap+toast). A table with a bloat ratio of 50% means that half of its size is
bloat.

> 
> On Tue, Oct 27, 2015 at 5:23 AM, Jehan-Guillaume de Rorthais <
> jgdr at dalibo.com> wrote:
> 
> > On Fri, 16 Oct 2015 20:11:24 +0530
> > "Shaik, Baji" <baji.shaik at openscg.com> wrote:
> >
> > > Hi Team,
> >
> > Hi,
> >
> > > I have configured bloat_check for 50% of the table as WARNING, however
> > even
> > > if tables is bloated less than the percentage configured, it is throwing
> > an
> > > alert. If you see below example, ONLY 30% of the table is bloated,
> > however
> > > still I can see the table in the bloated list with 50% as critical
> > > threshold.
> > >
> > > Kindly let me know if I am missing anything.
> > >
> > > bloat_test=# \dt+ t
> > >                      List of relations
> > >  Schema | Name | Type  |    Owner     | Size  | Description
> > > --------+------+-------+--------------+-------+-------------
> > >  public | t    | table | enterprisedb | 69 MB |
> > > (1 row)
> > >
> > > bloat_test=# \q
> > > root at ubuntu:/home/bajishaik/check_postgres-2.22.0#
> > ./check_postgres_bloat
> > > --port=5445 --critical='500%' --db='bloat_test' --dbuser='enterprisedb'
> > > POSTGRES_BLOAT OK: DB "bloat_test" (port=5445) (db bloat_test) table
> > > public.t rows:1500001 pages:8850 shouldbe:6608 (1.3X) wasted
> > size:18366464
> > > (17 MB) | public.t=18366464B
> > > root at ubuntu:/home/bajishaik/check_postgres-2.22.0#
> > > root at ubuntu:/home/bajishaik/check_postgres-2.22.0#
> > > root at ubuntu:/home/bajishaik/check_postgres-2.22.0#
> > ./check_postgres_bloat
> > > --port=5445 --critical='50%' --db='bloat_test' --dbuser='enterprisedb'
> > > POSTGRES_BLOAT CRITICAL: DB "bloat_test" (port=5445) (db bloat_test)
> > table
> > > public.t rows:1500001 pages:8850 shouldbe:6608 (1.3X) wasted
> > size:18366464
> > > (17 MB) | public.t=18366464B
> >
> > I see you are using entreprisedb ? Could you have a look at
> > check_pgactivity by
> > any chance and tell me if it is compatible ? :-)
> >
> > See:
> >
> > https://exchange.nagios.org/directory/Plugins/Databases/PostgresQL/check_pgactivity/details

-- 
Jehan-Guillaume de Rorthais
Dalibo
http://www.dalibo.com