Skip site navigation (1) Skip section navigation (2)

Re: Measuring table and index bloat

From: Decibel! <decibel(at)decibel(dot)org>
To: Greg Smith <gsmith(at)gregsmith(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Measuring table and index bloat
Date: 2007-12-20 01:45:25
Message-ID: 703FBDA9-D9C3-4735-A903-8210630C94BB@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Decibel!Date: 2007-12-20 01:51:13
Subject: Re: Dual core Opterons beating quad core Xeons?
Previous:From: Tom LaneDate: 2007-12-20 00:39:45
Subject: Re: Minimizing dead tuples caused by update triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group