Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 0B1B063275E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 11 Jun 2010 12:41:33 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 16945-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 11 Jun 2010 15:41:24 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.186])
	by mail.postgresql.org (Postfix) with ESMTP id F1ECF63249C
	for <pgsql-admin@postgresql.org>; Fri, 11 Jun 2010 12:41:23 -0300 (ADT)
Received: from [192.168.1.6] (pool-96-244-14-10.bltmmd.fios.verizon.net
	[96.244.14.10])
	by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis)
	id 0MW8Zl-1OlVvq1fzN-00XgOI; Fri, 11 Jun 2010 17:41:20 +0200
Message-ID: <4C12591E.8010607@2ndquadrant.com>
Date: Fri, 11 Jun 2010 11:41:18 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: dx k9 <bitsandbytes88@hotmail.com>
CC: posgres support <pgsql-admin@postgresql.org>
Subject: Re: check_postgres_bloat
References: <SNT123-W2DB788D752D60727FF1D8D1D90@phx.gbl>
In-Reply-To: <SNT123-W2DB788D752D60727FF1D8D1D90@phx.gbl>
Content-Type: multipart/alternative;
	boundary="------------020009080707070807090100"
X-Provags-ID: V01U2FsdGVkX1+ryQ0Fj8IpFQNH+9fYFKbewf4ZZk73WjXiV/z
	9sjw+4uEnuw5fJBLL+0v71GWwoDQgKPQ1+yZdTjZ7VKBVR5lsE
	XjynqD1vH+KOzDdfabN8tbNgQOpJorK
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201006/100
X-Sequence-Number: 34878

This is a multi-part message in MIME format.
--------------020009080707070807090100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

dx k9 wrote:
>
> check_postgres checks for both index and table bloat.  It looks like 
> my indexes are ok, this is just picking up on table bloat.  I'm not 
> sure what I can do to reclaim the wasted space other than vacuum full 
> & analyze.  Maybe a pgdump will do it.
>

CLUSTER will rebuild a new copy of the table without any table bloat, 
and it's much faster than VACUUM FULL.  See 
http://wiki.postgresql.org/wiki/VACUUM_FULL

Note that the bloat estimate from check_postgres is extremely rough and 
it's quite possible to get misleading results from it.  I wouldn't do 
anything just based on an initial report from it that a table is bloated 
other than move the thresholds up until it stops complaining.  The idea 
is that once calibrated usefully to what is normal bloat levels for your 
app by its measurement technique, you then monitor for excess bloat 
outside of historical norms.  You should not assume the number itself is 
really accurate, and you should do a manual VACUUM VERBOSE against the 
table to see if it's right or not before taking drastic action (like 
VACUUM FULL or CLUSTER).  You might also use pg_freespacemap instead to 
compute more accurate bloat numbers, but most people consider that too 
much work relative to the improvement you get over the simpler 
check_postgres estimate.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


--------------020009080707070807090100
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
dx k9 wrote:
<blockquote cite="mid:SNT123-W2DB788D752D60727FF1D8D1D90@phx.gbl"
 type="cite">
  <style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
--></style>
  <p style="margin: 0in 0in 0pt;" class="MsoPlainText"><font
 face="Consolas" size="3">check_postgres checks for both index and
table bloat.&nbsp; It looks like my indexes are ok, this is just picking up
on table bloat.&nbsp; I'm not sure what I can do to reclaim the wasted space
other than vacuum full &amp;&nbsp;analyze.&nbsp; Maybe a pgdump will do it.</font></p>
</blockquote>
<br>
CLUSTER will rebuild a new copy of the table without any table bloat,
and it's much faster than VACUUM FULL.&nbsp; See
<a class="moz-txt-link-freetext" href="http://wiki.postgresql.org/wiki/VACUUM_FULL">http://wiki.postgresql.org/wiki/VACUUM_FULL</a><br>
<br>
Note that the bloat estimate from check_postgres is extremely rough and
it's quite possible to get misleading results from it.&nbsp; I wouldn't do
anything just based on an initial report from it that a table is
bloated other than move the thresholds up until it stops complaining.&nbsp;
The idea is that once calibrated usefully to what is normal bloat
levels for your app by its measurement technique, you then monitor for
excess bloat outside of historical norms.&nbsp; You should not assume the
number itself is really accurate, and you should do a manual VACUUM
VERBOSE against the table to see if it's right or not before taking
drastic action (like VACUUM FULL or CLUSTER).&nbsp; You might also use
pg_freespacemap instead to compute more accurate bloat numbers, but
most people consider that too much work relative to the improvement you
get over the simpler check_postgres estimate.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
<a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   <a class="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
</pre>
</body>
</html>

--------------020009080707070807090100--