Autovacuums running on unused tables/databases

From: Chris Barber <chris(dot)barber(at)zonarsystems(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Autovacuums running on unused tables/databases
Date: 2011-01-31 23:22:54
Message-ID: 40F477EAB73C974B92AE2E7EFFE57574320C3649CD@exchange-001.zcorp.zonarsystems.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I am having some issues with forced autovacuum kicking on and vacuuming tables that should not need to be vacuum'd. Initially we believed this to just be high usage on these tables and dug into what could be causing xid's to increase so quickly. We were never able to find querys that were doing this. Now I have seen autovacuum running on a database that had been marked for deletion a week prior. (Once a database in our system has been marked for deletion all access from our applications are blocked to it). Just to be clear though, we currently have a system set up to vacuum tables on the weekends and smaller tables every night. Autovacuum is disabled and the ones that are kicking on are forced to prevent xid rollover.

For our custom vacuum scripts we are using relfrozenid with a query similar to what is mentioned in the postgres docs.

SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
SELECT datname, age(datfrozenxid) FROM pg_database;

After creating a new database however and watching the xid's on a particular table we can see that they grow even though nothing is accessing them. Vacuuming them returns them back down but they continue growing.

We are currently running postgres 8.3.10

Any help as to why the xid's seem to be growing out of control would be very appreciated.

Confidentiality Notice: This e-mail may contain proprietary information some of which may be legally privileged. It is for the intended recipient(s) only. If you believe that it has been sent to you in error, please notify the sender by reply e-mail and delete the message. Any disclosure, copying, distribution or use of this information by someone other than the intended recipient(s) is prohibited and may be unlawful.

Browse pgsql-admin by date

  From Date Subject
Next Message Do, Elvis L 2011-02-01 00:35:03 pgagent fails to start by service command on redhat enterprise 5
Previous Message Do, Elvis L 2011-01-31 22:46:31 pgagent fails to start by service command on redhat enterprise 5