Bloat and Slow Vacuum Time on Toast

From: Ken Caruso <ken(at)ipl31(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Bloat and Slow Vacuum Time on Toast
Date: 2011-07-19 20:37:47
Message-ID: CAMg8r_o=sHXqOzPgNw9+ndp+-0bAK6DBcjCu0poZn2se5qC-vQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2011-07-19 20:50:15 Re: Bloat and Slow Vacuum Time on Toast
Previous Message Ken Caruso 2011-07-19 19:27:22 Re: 9.0.4 Data corruption issue