Обсуждение: autovacuum ignore tables
Hi
Is it possible to tell autovacuum to ignore certain tables (These tables are high traffic daily partition tables that get dropped every other day).
I am trying to get autovacuum to complete quicker and prevent Xactn wraparound (currently, it takes very long and due to the fact that I have to restart the database for maintenance purposes), autovacuum never finishes.)
"Sriram Dandapani" <sdandapani@counterpane.com> writes: > I am trying to get autovacuum to complete quicker and prevent Xactn > wraparound (currently, it takes very long and due to the fact that I > have to restart the database for maintenance purposes), autovacuum never > finishes.) Why are you restarting the database? There is no "maintenance purpose" I'm aware of that requires routine postmaster restarts. As for suppressing autovac on specific tables, see the pg_autovacuum system catalog. regards, tom lane
I have a high traffic table which is populated by a trigger on another table. This high traffic table is partitioned by day. When I issue a drop table on a child table..it goes into a wait state AND puts all jdbc inserts in a wait state.(in short..a deadlock). I can repeat this use case any time. The only way to guarantee all drops to work is to briefly shutdown , modify pg_hba.conf to disallow client access, restart and issue the drop commands (followed by restoration of original pg_hba.conf) -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, September 18, 2006 1:32 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] autovacuum ignore tables "Sriram Dandapani" <sdandapani@counterpane.com> writes: > I am trying to get autovacuum to complete quicker and prevent Xactn > wraparound (currently, it takes very long and due to the fact that I > have to restart the database for maintenance purposes), autovacuum never > finishes.) Why are you restarting the database? There is no "maintenance purpose" I'm aware of that requires routine postmaster restarts. As for suppressing autovac on specific tables, see the pg_autovacuum system catalog. regards, tom lane
Sriram Dandapani wrote: > > Hi > > > > Is it possible to tell autovacuum to ignore certain tables (These > tables are high traffic daily partition tables that get dropped every > other day). > > > > I am trying to get autovacuum to complete quicker and prevent Xactn > wraparound (currently, it takes very long and due to the fact that I > have to restart the database for maintenance purposes), autovacuum > never finishes.) > If you are running PostgreSQL 8.1 with integrated autovacuum, then yes, you can disable autovacuum on a per table basis, if you are using an older version with the contrib autovacuum, then no. Matt
If I tell autovacuum to ignore the high traffic daily partition tables that get dropped regularly, will it reset the transaction id wraparound counter when it is done? -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, September 18, 2006 1:32 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] autovacuum ignore tables "Sriram Dandapani" <sdandapani@counterpane.com> writes: > I am trying to get autovacuum to complete quicker and prevent Xactn > wraparound (currently, it takes very long and due to the fact that I > have to restart the database for maintenance purposes), autovacuum never > finishes.) Why are you restarting the database? There is no "maintenance purpose" I'm aware of that requires routine postmaster restarts. As for suppressing autovac on specific tables, see the pg_autovacuum system catalog. regards, tom lane