Re: Measuring table and index bloat

On Dec 8, 2007, at 1:06 AM, Greg Smith wrote:
> One of those things that comes up regularly on this list in
> particular are people whose performance issues relate to "bloated"
> tables or indexes. What I've always found curious is that I've
> never seen a good way suggested to actually measure said bloat in
> any useful numeric terms--until today.
>
> Greg Sabino Mullane just released a Nagios plug-in for PostgreSQL
> that you can grab at http://bucardo.org/nagios_postgres/ , and
> while that is itself nice the thing I found most remarkable is the
> bloat check.  The majority of that code is an impressive bit of SQL
> that anyone could use even if you have no interest in Nagios, which
> is why I point it out for broader attention.  Look in
> check_postgres.pl for the "check_bloat" routine and the big
> statement starting at the aptly labled "This was fun to write"
> section.  If you pull that out of there and replace $MINPAGES and
> $MINIPAGES near the end with real values, you can pop that into a
> standalone query and execute it directly.  Results look something
> like this (reformatting for e-mail):
>
> schemaname | tablename | reltuples | relpages | otta  | tbloat |
> public     | accounts  |   2500000 |    41667 | 40382 |    1.0 |
>
> wastedpages | wastedbytes | wastedsize |      iname      | ituples |
>        1285 |    10526720 | 10 MB      | accounts_pkey   | 2500000 |
>
> ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize
>   5594 | 35488 |    0.2 |            0 |            0 | 0 bytes
>
> I'd be curious to hear from those of you who have struggled with
> this class of problem in the past as to whether you feel this
> quantifies the issue usefully.

I don't think he's handling alignment correctly...

           CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma

AFAIK that should also be 8 on 64 bit CPUs.

A somewhat more minor nit... the calculation of the null header
should be based on what columns in a table are nullable, not whether
a column actually is null. Oh, and otta should be oughta. :) Though
I'd probably just call it ideal.

Having said all that, this looks highly useful!
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828