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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828