pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMeasuring table and index bloat
2+ messages / 2 participants
[nested] [flat]
* Measuring table and index bloat
@ 2007-12-08 07:06 Greg Smith <gsmith@gregsmith.com>
0 siblings, 1 reply; 2+ messages in thread
From: Greg Smith @ 2007-12-08 07:06 UTC (permalink / raw)
To: pgsql-performance
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.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Measuring table and index bloat
@ 2007-12-20 01:45 Decibel! <decibel@decibel.org>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 2+ messages in thread
From: Decibel! @ 2007-12-20 01:45 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: 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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-12-20 01:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-08 07:06 Measuring table and index bloat Greg Smith <gsmith@gregsmith.com>
2007-12-20 01:45 ` Decibel! <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox