pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcheck_postgres_bloat
4+ messages / 3 participants
[nested] [flat]
* check_postgres_bloat
@ 2010-06-11 14:50 dx k9 <bitsandbytes88@hotmail.com>
2010-06-11 14:54 ` Re: check_postgres_bloat Kenneth Marshall <ktm@rice.edu>
2010-06-11 15:41 ` Re: check_postgres_bloat Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 4+ messages in thread
From: dx k9 @ 2010-06-11 14:50 UTC (permalink / raw)
To: pgsql-admin
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.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2=
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: check_postgres_bloat
2010-06-11 14:50 check_postgres_bloat dx k9 <bitsandbytes88@hotmail.com>
@ 2010-06-11 14:54 ` Kenneth Marshall <ktm@rice.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Kenneth Marshall @ 2010-06-11 14:54 UTC (permalink / raw)
To: dx k9 <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
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) 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
>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: check_postgres_bloat
2010-06-11 14:50 check_postgres_bloat dx k9 <bitsandbytes88@hotmail.com>
@ 2010-06-11 15:41 ` Greg Smith <greg@2ndquadrant.com>
1 sibling, 0 replies; 4+ messages in thread
From: Greg Smith @ 2010-06-11 15:41 UTC (permalink / raw)
To: dx k9 <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* check_postgres_bloat
@ 2010-06-11 17:26 dx k9 <bitsandbytes88@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: dx k9 @ 2010-06-11 17:26 UTC (permalink / raw)
To: pgsql-admin
Thanks everyone for the responses.
It seems like the problem is actually that the check_postgres_bloat does not check for index bloat, just table bloat, even though it says it checks for both.
Below is my proof. Notice how this first check finds the indexes with idx in their name, but the second one doesn't.
check_postgres.pl version 2.14.3
postgresq version is 8.3.9
/usr/local/nagios/libexec # ./check_postgres_index_size --dbservice=thing -w 50 --include=~idx -c 100
POSTGRES_INDEX_SIZE CRITICAL: service=thing largest index is "thing1_idx": 288 kB | time=1.00
/usr/local/nagios/libexec # ./check_postgres_bloat --dbservice=thing -w 50 --include=~idx -c 100 POSTGRES_BLOAT UNKNOWN: service=thing No matching relations found due to exclusion/inclusion options | time=0.18
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL...
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-06-11 17:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-11 14:50 check_postgres_bloat dx k9 <bitsandbytes88@hotmail.com>
2010-06-11 14:54 ` Kenneth Marshall <ktm@rice.edu>
2010-06-11 15:41 ` Greg Smith <greg@2ndquadrant.com>
2010-06-11 17:26 check_postgres_bloat dx k9 <bitsandbytes88@hotmail.com>
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