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