I used to use VACUUM FULL periodically to resolve the issue, but the
problem arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.
dcdb=# select date, relpages, reltuples, table_len, tuple_count,
tuple_percent, dead_tuple_count, dead_tuple_len, free_space,
free_percent, autovacuum_count from public.table_statistics where
relname = 'pg_attribute' order by date;
date | relpages | reltuples | table_len | tuple_count |
tuple_percent | dead_tuple_count | dead_tuple_len | free_space |
free_percent | autovacuum_count
------------+----------+-----------+-----------+-------------+---------------+------------------+----------------+------------+--------------+------------------
2013-08-08 | 39029 | 109096 | 319725568 | 37950 |
1.66 | 52540 | 7355600 | 296440048 | 92.72
| 6359
2013-08-09 | 12382 | 95848 | 101433344 | 38232 |
5.28 | 57443 | 8042020 | 83862864 | 82.68
| 6711
2013-08-10 | 11365 | 105073 | 93102080 | 37789 |
5.68 | 65599 | 9183860 | 74483104 | 80
| 7002
2013-08-12 | 9447 | 95289 | 77389824 | 37811 |
6.84 | 57154 | 8001560 | 60479736 | 78.15
| 7161
2013-08-13 | 47841 | 82877 | 391913472 | 38536 |
1.38 | 30461 | 4264540 | 369093756 | 94.18
| 7347
2013-08-14 | 70265 | 104926 | 575610880 | 38838 |
0.94 | 34649 | 4850860 | 546449480 | 94.93
| 7398
(6 rows)
Autovacuum is running on this table, however it keeps growing.
On 08/06/2013 09:35 PM, Tom Lane wrote:
Vlad Arkhipov <arhi...@dc.baikal.ru> writes:
On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
What pgstattuple shows on this table?
dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
6363938816 | 48786 | 6830040 | 0.11 | 1459439 |
204321460 | 3.21 | 5939017376 | 93.32
(1 row)
So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space. I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses. There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you. Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
