System catalog vacuum issues

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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-16 00: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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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'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.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers