Обсуждение: check_postgres_bloat

Hi,
 
I'm running a the check_postgres.pl --action=bloat on a database and finding that there is wasted space.
 
I'm using 95% for the crtical %.  If I use 110% I get the same things, but 115% shows everything is OK.
 
check_postgres_bloat -H host -p port -db thing -t thing1 -c 95%
check_postgres.pl version 2.14.3
postgres version 8.3.9
 
 

POSTGRES_BLOAT CRITICAL: service=thing1 (db thing) table public.thing1 rows:25537152 pages:212311 shouldbe:187545 (1.1X) wasted size:202883072 (193 MB) * (db thing) table public.thing2 rows:10109173 pages:101832 shouldbe:94016 (1.1X) wasted size:64028672 (61 MB) * (db thing) table thing3 rows:1184946 pages:28701 shouldbe:21434 (1.3X) wasted size:59531264 (56 MB) * 

 

 

I have run a reindexdb on the table, full vacuum (fze), still I can't get the wasted size to go down on these, so I continue to get critical alerts that my tables are bloated.

 

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.

 

Thanks for any insight,

~DjK

 

 

---

Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. See how.

On Fri, Jun 11, 2010 at 10:50:20AM -0400, dx k9 wrote:
>
> Hi,
>
>
>
> I'm running a the check_postgres.pl --action=bloat on a database and finding that there is wasted space.
>
>
>
> I'm using 95% for the crtical %.  If I use 110% I get the same things, but 115% shows everything is OK.
>
>
>
> check_postgres_bloat -H host -p port -db thing -t thing1 -c 95%
>
> check_postgres.pl version 2.14.3
>
> postgres version 8.3.9
>
>
>
>
>
> POSTGRES_BLOAT CRITICAL: service=thing1 (db thing) table public.thing1 rows:25537152 pages:212311 shouldbe:187545
(1.1X)wasted size:202883072 (193 MB) * (db thing) table public.thing2 rows:10109173 pages:101832 shouldbe:94016 (1.1X)
wastedsize:64028672 (61 MB) * (db thing) table thing3 rows:1184946 pages:28701 shouldbe:21434 (1.3X) wasted
size:59531264(56 MB) *  
>
>
> I have run a reindexdb on the table, full vacuum (fze), still I can't get the wasted size to go down on these, so I
continueto get critical alerts that my tables are bloated. 
>
> check_postgres checks for both index and table bloat.  It looks like my indexes are ok, this is just picking up on
tablebloat.  I'm not sure what I can do to reclaim the wasted space other than vacuum full & analyze.  Maybe a pgdump
willdo it. 
>
> Thanks for any insight,
> ~DjK
>
Because of the way PostgreSQL performs updates, your database will
perform better with a little bit of extra space over the amount
needed for your data. Unless you have a read-only database, just
use normal vacuum via autovacuum and forget about vacuum full and
trying to get "wasted" space to zero.

Regards,
Ken

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