pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (7+ messages)  latest in thread

Message-ID: <4E25B07C020000250003F518@gw.wicourts.gov>
Permalink:  ../4E25B07C020000250003F518@gw.wicourts.gov/
Also on:    postgresql.org/message-id/4E25B07C020000250003F518@gw.wicourts.gov

 · 

reply

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