Re: [HACKERS] System catalog vacuum issues

2013-08-22 Thread Jim Nasby
On 8/19/13 7:23 PM, Sergey Konoplev wrote: On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipovarhi...@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

Re: [HACKERS] System catalog vacuum issues

2013-08-22 Thread Sergey Konoplev
On Wed, Aug 21, 2013 at 2:33 PM, Jim Nasby j...@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

Re: [HACKERS] System catalog vacuum issues

2013-08-19 Thread Sergey Konoplev
On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipov arhi...@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.

Re: [HACKERS] System catalog vacuum issues

2013-08-15 Thread Vlad Arkhipov
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

Re: [HACKERS] System catalog vacuum issues

2013-08-15 Thread Vlad Arkhipov
On 08/15/2013 05:06 AM, Sergey Konoplev wrote: On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov arhi...@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

Re: [HACKERS] System catalog vacuum issues

2013-08-14 Thread Jim Nasby
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

Re: [HACKERS] System catalog vacuum issues

2013-08-14 Thread Sergey Konoplev
On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov arhi...@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 |

Re: [HACKERS] System catalog vacuum issues

2013-08-13 Thread Vlad Arkhipov
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,

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Craig Ringer
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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov
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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Sergey Konoplev
On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov
On 08/06/2013 04:26 PM, Sergey Konoplev wrote: On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Sergey Konoplev
On Tue, Aug 6, 2013 at 12:37 AM, Vlad Arkhipov arhi...@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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Tom Lane
Vlad Arkhipov arhi...@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 |