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

On 8/14/13 12:31 AM, Vlad Arkhipov wrote:
> 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.

How much non-temporary DDL do you do? It's possible that you end up with a tuple at the end of the table for a
non-temporaryobject. One of those would stay valid for quite some time, and if you're unlucky then you'll end up with
anotherlong-lived row farther down the table, etc, etc.
 

Depending on how frequently you're creating temp objects, autovac might not be able to keep up. Assuming that a manual
vacuumdoesn't take too long it might be a good idea to cron a manual vacuum (NOT FULL) of that table once a minute.
 

> 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


-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net