Thread: System catalog vacuum issues
Hello, We are suffering from a long-standing issue with autovacuuming/vacuuming system catalogs on the production server. We are actively using temporary tables in the legacy application, so system catalogs grows unbounded in time. Autovacuum does not remove dead tuples and neither do the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64. Nobody's holding an open transaction for long periods. dcdb=# select xact_start, query_start, state, query from pg_stat_activity where state <> 'idle'; xact_start | query_start | state | query -------------------------------+-------------------------------+--------+------------------------------------------------------------------------------------------- 2013-08-0614:46:56.303261+09 | 2013-08-06 14:46:56.303261+09 | active | select xact_start, query_start, state, query from pg_stat_activity where state <> 'idle'; (1 row) dcdb=# select count(*) from pg_attribute; count ------- 51279 (1 row) dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute'; relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum --------------+------------+------------+-------------------------------+------------------------------- pg_attribute | 39318143 | 427798 | 2013-08-06 14:46:09.323187+09 | 2013-08-06 13:43:03.162286+09 (1 row) dcdb=# vacuum analyze verbose pg_attribute; INFO: vacuuming "pg_catalog.pg_attribute" INFO: index "pg_attribute_relid_attnam_index" now contains 492122 row versions in 166671 pages DETAIL: 0 index row versions were removed. 163952 index pages have been deleted, 162834 are currently reusable. CPU 0.69s/0.21u sec elapsed 0.94 sec. INFO: index "pg_attribute_relid_attnum_index" now contains 492253 row versions in 118119 pages DETAIL: 0 index row versions were removed. 116071 index pages have been deleted, 115269 are currently reusable. CPU 0.51s/0.14u sec elapsed 0.67 sec. INFO: "pg_attribute": found 0 removable, 460354 nonremovable row versions in 9570 out of 776848 pages DETAIL: 440706 dead row versions cannot be removed yet. There were 1788424 unused item pointers. 0 pages are entirely empty. CPU 1.28s/0.43u sec elapsed 1.75 sec. INFO: analyzing "pg_catalog.pg_attribute" INFO: "pg_attribute": scanned 30000 of 776848 pages, containing 1918 live rows and 15226 dead rows; 1878 rows in sample, 39318086 estimated total rows VACUUM dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute'; relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum --------------+------------+------------+-------------------------------+------------------------------- pg_attribute | 39318086 | 395478 | 2013-08-06 14:47:48.187259+09 | 2013-08-06 13:43:03.162286+09 (1 row)
On 08/06/2013 01:56 PM, Vlad Arkhipov wrote: > Hello, > > We are suffering from a long-standing issue with autovacuuming/vacuuming > system catalogs on the production server. We are actively using > temporary tables in the legacy application, so system catalogs grows > unbounded in time. Autovacuum does not remove dead tuples and neither do > the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64. > > Nobody's holding an open transaction for long periods. Got any prepared transactions? SELECT * FROM pg_prepared_xacts; SHOW max_prepared_transactions; -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 08/06/2013 04:00 PM, Craig Ringer wrote: > On 08/06/2013 01:56 PM, Vlad Arkhipov wrote: >> Hello, >> >> We are suffering from a long-standing issue with autovacuuming/vacuuming >> system catalogs on the production server. We are actively using >> temporary tables in the legacy application, so system catalogs grows >> unbounded in time. Autovacuum does not remove dead tuples and neither do >> the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64. >> >> Nobody's holding an open transaction for long periods. > Got any prepared transactions? > > SELECT * FROM pg_prepared_xacts; > > SHOW max_prepared_transactions; dcdb=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -------------+-----+----------+-------+---------- (0 rows) dcdb=# show max_prepared_transactions; max_prepared_transactions --------------------------- 100 (1 row)
On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov <arhipov@dc.baikal.ru> wrote: > dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum > from pg_stat_sys_tables where relname = 'pg_attribute'; > relname | n_live_tup | n_dead_tup | last_vacuum | > last_autovacuum > --------------+------------+------------+-------------------------------+------------------------------- > pg_attribute | 39318086 | 395478 | 2013-08-06 14:47:48.187259+09 | > 2013-08-06 13:43:03.162286+09 What pgstattuple shows on this table? http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
On 08/06/2013 04:26 PM, Sergey Konoplev wrote: > On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov <arhipov@dc.baikal.ru> wrote: >> dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum >> from pg_stat_sys_tables where relname = 'pg_attribute'; >> relname | n_live_tup | n_dead_tup | last_vacuum | >> last_autovacuum >> --------------+------------+------------+-------------------------------+------------------------------- >> pg_attribute | 39318086 | 395478 | 2013-08-06 14:47:48.187259+09 | >> 2013-08-06 13:43:03.162286+09 > What pgstattuple shows on this table? > http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html > 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)
On Tue, Aug 6, 2013 at 12:37 AM, Vlad Arkhipov <arhipov@dc.baikal.ru> wrote: >> What pgstattuple shows on this table? >> http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html >> > > 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) I guess you need to VACUUM FULL pg_attribute, if it is possible in your situation of course. If it is not, let me know, I have another one tricky way of solving this problem in my mind. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
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
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
>
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
On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov <arhipov@dc.baikal.ru> 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. > > 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 | Are you sure you did "VACUUM FULL pg_attribute" on Aug 7, could you please confirm that free_percent arises from 0 to 92% in one day? Do you have some processes that intensively create tables or columns and then delete them or create them in transaction and rollback the transaction? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On 08/16/2013 10:44 AM, Vlad Arkhipov wrote: > On 08/15/2013 03:27 AM, Jim Nasby wrote: >> 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. > Not much. 1-2 tables per day.
On 08/15/2013 05:06 AM, Sergey Konoplev wrote:
> On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov <arhipov@dc.baikal.ru> 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.
>>
>> 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 |
> Are you sure you did "VACUUM FULL pg_attribute" on Aug 7, could you
> please confirm that free_percent arises from 0 to 92% in one day?
>
> Do you have some processes that intensively create tables or columns
> and then delete them or create them in transaction and rollback the
> transaction?
>
Absolutely. Here is 1-minute statistics on this table. VACUUM FULL was
done on 2013-08-16 00:35:00.
There are many processes that create and drop temporary tables.
time | reltuples | table_len | tuple_count
| tuple_percent | dead_tuple_count | dead_len | free_space |
free_percent | autovacuum_count
-------------------------------+-------------+-----------+-------------+---------------+------------------+------------+------------+--------------+------------------
2013-08-1600:33:01.977405+09 | 1.50021e+07 | 2211 MB | 38981
| 0.24 | 15505917 | 2070 MB | 8339 kB | 0.37
| 7463 2013-08-16 00:34:01.718696+09 | 1.50021e+07 | 2211 MB | 38875
| 0.23 | 15505952 | 2070 MB | 8349 kB | 0.37
| 7463 2013-08-16 00:35:01.570965+09 | 38875 | 5664 kB | 38875
| 93.84 | 46 | 6440 bytes | 19 kB | 0.34
| 7463 2013-08-16 00:36:01.658131+09 | 38875 | 5664 kB | 38875
| 93.84 | 46 | 6440 bytes | 19 kB | 0.34
| 7463
... 2013-08-16 08:10:01.201473+09 | 47950 | 52 MB | 47685
| 12.22 | 318481 | 43 MB | 229 kB | 0.43
| 7493 2013-08-16 08:11:01.411891+09 | 47950 | 54 MB | 47776
| 11.86 | 329589 | 44 MB | 333 kB | 0.6
| 7493 2013-08-16 08:12:01.623495+09 | 48036 | 56 MB | 47816
| 11.47 | 343932 | 46 MB | 199 kB | 0.35
| 7495 2013-08-16 08:13:01.837192+09 | 48036 | 58 MB | 47903
| 11.11 | 356488 | 48 MB | 286 kB | 0.48
| 7495 2013-08-16 08:14:02.041228+09 | 48036 | 59 MB | 47899
| 10.82 | 366939 | 49 MB | 370 kB | 0.61
| 7495 2013-08-16 08:15:01.254325+09 | 48036 | 61 MB | 48065
| 10.61 | 376192 | 50 MB | 420 kB | 0.68
| 7495 2013-08-16 08:16:01.557785+09 | 48210 | 62 MB | 48290
| 10.36 | 386019 | 52 MB | 696 kB | 1.09
| 7496 2013-08-16 08:17:01.774188+09 | 48210 | 64 MB | 48330
| 10.14 | 392236 | 52 MB | 1188 kB | 1.82
| 7496 2013-08-16 08:18:01.977503+09 | 48210 | 65 MB | 48370
| 9.87 | 79643 | 11 MB | 46 MB | 70.07
| 7496 2013-08-16 08:19:01.154589+09 | 48210 | 68 MB | 48550
| 9.55 | 27483 | 3757 kB | 55 MB | 81.55
| 7496 2013-08-16 08:20:01.321973+09 | 48333 | 69 MB | 48694
| 9.41 | 42512 | 5812 kB | 54 MB | 78.83
| 7497 2013-08-16 08:21:01.48612+09 | 48333 | 69 MB | 48831
| 9.43 | 43172 | 5902 kB | 54 MB | 78.67
| 7497 2013-08-16 08:22:01.668103+09 | 48926 | 69 MB | 48947
| 9.46 | 22677 | 3100 kB | 57 MB | 82.72
| 7498 2013-08-16 08:23:01.83524+09 | 48962 | 69 MB | 48914
| 9.45 | 8655 | 1183 kB | 59 MB | 85.5
| 7499
... 2013-08-16 10:22:01.590888+09 | 52114 | 131 MB | 52395
| 5.33 | 866015 | 116 MB | 1045 kB | 0.78
| 7550 2013-08-16 10:23:01.908792+09 | 52114 | 133 MB | 52579
| 5.29 | 560495 | 75 MB | 44 MB | 33.44
| 7550 2013-08-16 10:24:01.207538+09 | 52114 | 134 MB | 52566
| 5.22 | 222138 | 30 MB | 92 MB | 68.77
| 7550 2013-08-16 10:25:01.485565+09 | 52114 | 136 MB | 52637
| 5.17 | 25493 | 3485 kB | 121 MB | 88.95
| 7550 2013-08-16 10:26:01.747405+09 | 52114 | 138 MB | 52673
| 5.11 | 34411 | 4705 kB | 121 MB | 88.14
| 7550 2013-08-16 10:27:01.025129+09 | 52114 | 139 MB | 52733
| 5.05 | 47331 | 6471 kB | 121 MB | 86.98
| 7550 2013-08-16 10:28:01.301775+09 | 52114 | 142 MB | 52776
| 4.97 | 61860 | 8457 kB | 121 MB | 85.73
| 7550 2013-08-16 10:29:01.600223+09 | 52577 | 142 MB | 52805
| 4.97 | 72307 | 9886 kB | 120 MB | 84.72
| 7551 2013-08-16 10:30:01.883806+09 | 52577 | 142 MB | 52809
| 4.97 | 81656 | 11 MB | 119 MB | 83.81
| 7551 2013-08-16 10:31:01.165272+09 | 52577 | 142 MB | 52869
| 4.98 | 93342 | 12 MB | 117 MB | 82.67
| 7551 2013-08-16 10:32:01.463636+09 | 52577 | 142 MB | 52919
| 4.98 | 108460 | 14 MB | 115 MB | 81.19
| 7551 2013-08-16 10:33:01.772635+09 | 52577 | 142 MB | 52999
| 4.99 | 74781 | 10224 kB | 120 MB | 84.44
| 7551 2013-08-16 10:34:01.059824+09 | 52577 | 142 MB | 52986
| 4.99 | 86221 | 12 MB | 118 MB | 83.33
| 7551 2013-08-16 10:35:01.337787+09 | 52577 | 142 MB | 53163
| 5.01 | 38615 | 5279 kB | 125 MB | 87.92
| 7551 2013-08-16 10:36:01.624472+09 | 18577 | 142 MB | 53226
| 5.01 | 53105 | 7260 kB | 123 MB | 86.52
| 7552 2013-08-16 10:37:01.911377+09 | 25501 | 142 MB | 53056
| 5 | 11712 | 1601 kB | 128 MB | 90.55
| 7553
On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipov <arhipov@dc.baikal.ru> wrote: >> Do you have some processes that intensively create tables or columns >> and then delete them or create them in transaction and rollback the >> transaction? >> > There are many processes that create and drop temporary tables. That is the problem. Exactly what Jim was writing about. Autovacuum have no chance to clean dead tuples at the end of the table because they are created too intensively. In the latest versions autovacuum behaves so it would stop working when a concurrent lock is acquired. As he suggested you should use vacuum in cron, however it might make other procecess, that create/drop tables to wait. Another solution would be to factor out the temp tables usage from the logic. Could you please describe what are you using temp tables for? There might be another, more effective solution of this issue. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On 8/19/13 7:23 PM, Sergey Konoplev wrote: > On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipov<arhipov@dc.baikal.ru> wrote: >>> >>Do you have some processes that intensively create tables or columns >>> >>and then delete them or create them in transaction and rollback the >>> >>transaction? >>> >> >> >There are many processes that create and drop temporary tables. > That is the problem. Exactly what Jim was writing about. Autovacuum > have no chance to clean dead tuples at the end of the table because > they are created too intensively. In the latest versions autovacuum > behaves so it would stop working when a concurrent lock is acquired. > As he suggested you should use vacuum in cron, however it might make > other procecess, that create/drop tables to wait. Hrm... even if vacuum cost delay is set? I recall some talk about doing some minimal waiting for the lock, but thought that'donly happen if cost delay was 0. That really doesn't matter though. The whole idea of a cron'd vacuum is to *stop bloat from happening to begin with*. Ifthere's no bloat to begin with, getting the lock to truncate will be a non-issue. -- Jim C. Nasby, Data Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On Wed, Aug 21, 2013 at 2:33 PM, Jim Nasby <jim@nasby.net> wrote: >> That is the problem. Exactly what Jim was writing about. Autovacuum >> have no chance to clean dead tuples at the end of the table because >> they are created too intensively. In the latest versions autovacuum >> behaves so it would stop working when a concurrent lock is acquired. >> As he suggested you should use vacuum in cron, however it might make >> other procecess, that create/drop tables to wait. > > > Hrm... even if vacuum cost delay is set? I recall some talk about doing some > minimal waiting for the lock, but thought that'd only happen if cost delay > was 0. > > That really doesn't matter though. The whole idea of a cron'd vacuum is to > *stop bloat from happening to begin with*. If there's no bloat to begin > with, getting the lock to truncate will be a non-issue. Well, according to the pgstattuple log OP showed, free percent jumps from 1.82 to 70.07 in one minute, so I suppose an empty tail is inevitable anyway, so there should be locks to truncate by vacuum, if I understand things correct. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com