From: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> |
---|---|
To: | "Ken Caruso" <ken(at)ipl31(dot)net> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Bloat and Slow Vacuum Time on Toast |
Date: | 2011-07-19 21:27:40 |
Message-ID: | 4E25B07C020000250003F518@gw.wicourts.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Ken Caruso <ken(at)ipl31(dot)net> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov wrote:
>
>> Ken Caruso <ken(at)ipl31(dot)net> wrote:
>>
>>
>> > SELECT SUM(pg_relation_size(pg_class.oid))
>> > FROM pg_class ;
>>
>> Perhaps you want pg_total_relation_size()?
> The number returned when using pg_total_relation size makes more
> sense: 346GB. However it looked like pg_class has toast and
> indexes listed individually, so I assumed that using
> pg_total_relation_size would count values twice, because the total
> relation size of table would include toast and indexes, and then
> they would get counted again when I ran pg_total_relation_size on
> the toast and indexes themselves. Is that an incorrect assumption
> on my part?
No that's a good point. On one of our production databases,
externally:
ccsa(at)SOCRATES:~> du -sh /var/pgsql/data/cc/
2.2T /var/pgsql/data/cc/
internally:
cir=# select
pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint) from
pg_class;
pg_size_pretty
----------------
2176 GB
(1 row)
cir=# select
pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))::bigint)
from pg_class;
pg_size_pretty
----------------
4223 GB
(1 row)
cir=# select
pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))::bigint)
from pg_class where relkind = 'r';
pg_size_pretty
----------------
2176 GB
(1 row)
You must have a lot of something outside of relations showing as
active in pg_class taking up space. WAL files? Server logs? Temp
files left over from crashes?
-Kevin
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Caruso | 2011-07-20 02:17:13 | Re: Bloat and Slow Vacuum Time on Toast |
Previous Message | Tom Lane | 2011-07-19 21:26:50 | Re: Bloat and Slow Vacuum Time on Toast |