Re: System catalog vacuum issues - Mailing list pgsql-hackers

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@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
>