Re: System catalog vacuum issues

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 <arhipov(at)dc(dot)baikal(dot)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
>