pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBloat and Slow Vacuum Time on Toast
7+ messages / 3 participants
[nested] [flat]
* Bloat and Slow Vacuum Time on Toast
@ 2011-07-19 20:37 Ken Caruso <ken@ipl31.net>
0 siblings, 2 replies; 7+ messages in thread
From: Ken Caruso @ 2011-07-19 20:37 UTC (permalink / raw)
To: pgsql-admin
Hello,
I am trying to trouble shoot a couple of issue related to vacuum and db
size. I currently have a postgres DB that is around 390GB on disk (du -hs in
$PG_DATA/base). If I look at the total relation size using the following
query:
SELECT SUM(pg_relation_size(pg_class.oid))
FROM pg_class ;
This says the total size is around 191GB. That query should sum all of the
tables, toast and indexes AFAIK.
The following query:
select pg_size_pretty(pg_database_size('dbname')); returns:
pg_size_pretty
----------------
390 GB
There is one table using TOAST that gets alot of updates. It generates
around 40-50 million dead tuples a day.
Here is the output of pgstattuple for toast portion of the table:
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count
| dead_tuple_len | dead_tuple_percent | free_space | free_percent
--------------+-------------+-------------+---------------+------------------+----------------+--------------------+-------------+--------------
150103572480 | 22166132 | 30900832474 | 20.59 | 35732563
| 51134576972 | 34.07 | 67022123976 | 44.65
So the first question how do I find out where all the disk space going and
what next step do I need to perform to trouble shoot it. I am assuming that
the 200GB is dead space from vacuum not keeping up however the above queries
don't seem to confirm that.
Or at least I would expect pgstattuple to report a number closer to 200GB.
So I must be missing something here. I have looked at all the other tables
and they are all unremarkable size wise.
The second question is related to autovacuum. On both the table and the
toast and I have explicitly set the autovacuum threshold to 0.02. So it
should kick off around %2. This seems to be working great on the non TOAST
part of this table, but on the TOAST vacuum takes anywhere from 12hrs to 24
hrs depending on how busy the database is. Is there anything I can do to fix
this outside of increasing the IO if the underlying storage?
In addition I have the following settings in postgresql.conf:
max_connections = 500
max_prepared_transactions = 2000
shared_buffers = 4GB
effective_cache_size = 8GB
work_mem = 8MB
maintenance_work_mem = 1600MB
wal_buffers = 16MB
checkpoint_segments = 64
checkpoint_timeout = 600s
autovacuum_max_workers = 6
log_autovacuum_min_duration = 600
Hardware is an HPDL380 G5, dual quad core Xeon, 32GB of RAM, P800 RAID
controller. Pg_xlog is a RAID1 on local storage. PG_DATA is on an external
MSA70 with a 18 disk (146GB 10k) RAID10. There are other volumes on the MSA
but they are not actively used for anything.
Thanks
-Ken
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bloat and Slow Vacuum Time on Toast
@ 2011-07-19 20:50 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Ken Caruso <ken@ipl31.net>
1 sibling, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2011-07-19 20:50 UTC (permalink / raw)
To: Ken Caruso <ken@ipl31.net>; pgsql-admin
Ken Caruso <ken@ipl31.net> wrote:
> SELECT SUM(pg_relation_size(pg_class.oid))
> FROM pg_class ;
Perhaps you want pg_total_relation_size()?
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bloat and Slow Vacuum Time on Toast
@ 2011-07-19 21:00 Ken Caruso <ken@ipl31.net>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Ken Caruso @ 2011-07-19 21:00 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
On Tue, Jul 19, 2011 at 1:50 PM, 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()?
>
> -Kevin
>
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?
Thanks
-Ken
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bloat and Slow Vacuum Time on Toast
@ 2011-07-19 21:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken Caruso <ken@ipl31.net>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2011-07-19 21:26 UTC (permalink / raw)
To: Ken Caruso <ken@ipl31.net>; +Cc: pgsql-admin
Ken Caruso <ken@ipl31.net> writes:
> I am trying to trouble shoot a couple of issue related to vacuum and db
> size. I currently have a postgres DB that is around 390GB on disk (du -hs in
> $PG_DATA/base). If I look at the total relation size using the following
> query:
> SELECT SUM(pg_relation_size(pg_class.oid))
> FROM pg_class ;
> This says the total size is around 191GB. That query should sum all of the
> tables, toast and indexes AFAIK.
What PG version?
If it's a version with multiple table forks, you're only counting the
main forks, not FSM or VM.
Theoretically the latter two should be pretty small relative to the main
fork, but obviously you are missing something with this query ...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bloat and Slow Vacuum Time on Toast
@ 2011-07-19 21:27 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Ken Caruso <ken@ipl31.net>
0 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2011-07-19 21:27 UTC (permalink / raw)
To: Ken Caruso <ken@ipl31.net>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bloat and Slow Vacuum Time on Toast
@ 2011-07-20 02:17 Ken Caruso <ken@ipl31.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Ken Caruso @ 2011-07-20 02:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Tue, Jul 19, 2011 at 2:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ken Caruso <ken@ipl31.net> writes:
> > I am trying to trouble shoot a couple of issue related to vacuum and db
> > size. I currently have a postgres DB that is around 390GB on disk (du -hs
> in
> > $PG_DATA/base). If I look at the total relation size using the following
> > query:
>
> > SELECT SUM(pg_relation_size(pg_class.oid))
> > FROM pg_class ;
>
> > This says the total size is around 191GB. That query should sum all of
> the
> > tables, toast and indexes AFAIK.
>
> What PG version?
>
9.0.4
-Ken
>
> If it's a version with multiple table forks, you're only counting the
> main forks, not FSM or VM.
>
> Theoretically the latter two should be pretty small relative to the main
> fork, but obviously you are missing something with this query ...
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bloat and Slow Vacuum Time on Toast
@ 2011-07-20 03:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken Caruso <ken@ipl31.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2011-07-20 03:12 UTC (permalink / raw)
To: Ken Caruso <ken@ipl31.net>; +Cc: pgsql-admin
Ken Caruso <ken@ipl31.net> writes:
> On Tue, Jul 19, 2011 at 2:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Ken Caruso <ken@ipl31.net> writes:
>>> If I look at the total relation size using the following query:
>>> SELECT SUM(pg_relation_size(pg_class.oid)) FROM pg_class ;
>>> This says the total size is around 191GB.
>> What PG version?
> 9.0.4
OK, so you need to worry about alternate forks. Your query is the same
as
select sum(pg_relation_size(oid, 'main')) from pg_class;
I bet you'll find that the difference is accounted for by one of
select sum(pg_relation_size(oid, 'fsm')) from pg_class;
select sum(pg_relation_size(oid, 'vm')) from pg_class;
Drill down and see which table is responsible ...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-07-20 03:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-19 20:37 Bloat and Slow Vacuum Time on Toast Ken Caruso <ken@ipl31.net>
2011-07-19 20:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-07-19 21:00 ` Ken Caruso <ken@ipl31.net>
2011-07-19 21:27 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-07-19 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-07-20 02:17 ` Ken Caruso <ken@ipl31.net>
2011-07-20 03:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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