From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Ken Caruso <ken@ipl31.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Bloat and Slow Vacuum Time on Toast
Date: Tue, 19 Jul 2011 16:27:40 -0500
Message-ID: <4E25B07C020000250003F518@gw.wicourts.gov> (raw)
In-Reply-To: <CAMg8r_qEB6eWBeTNbqnT+61xRT82KzkAXQBbEDduu1m0_byadw@mail.gmail.com>
References: <CAMg8r_o=sHXqOzPgNw9+ndp+-0bAK6DBcjCu0poZn2se5qC-vQ@mail.gmail.com>
<4E25A7B7020000250003F502@gw.wicourts.gov>
<CAMg8r_qEB6eWBeTNbqnT+61xRT82KzkAXQBbEDduu1m0_byadw@mail.gmail.com>
Ken Caruso <ken@ipl31.net> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov wrote:
>
>> Ken Caruso <ken@ipl31.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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, ken@ipl31.net
Subject: Re: Bloat and Slow Vacuum Time on Toast
In-Reply-To: <4E25B07C020000250003F518@gw.wicourts.gov>
References: <CAMg8r_o=sHXqOzPgNw9+ndp+-0bAK6DBcjCu0poZn2se5qC-vQ@mail.gmail.com> <4E25A7B7020000250003F502@gw.wicourts.gov> <CAMg8r_qEB6eWBeTNbqnT+61xRT82KzkAXQBbEDduu1m0_byadw@mail.gmail.com> <4E25B07C020000250003F518@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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