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-temporary object. One of those would stay valid for quite some time, and if you're unlucky then you'll end up with another long-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 vacuum doesn'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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 9 of 15 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 6, '13 at 5:56a
activeAug 22, '13 at 11:15p
posts15
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase