Hello People, Before the question, this is the scenario: I have a postgresql 9.1 cluster with a size of 1.5 TB and composed of 70 databases. In every database I have 50 tables (master partition), each one have an associated trigger that insert the record into a child table of its own. The partition is based on a week period, so every newly created tables is of the form: tablename_YYYYwWW The above configuration works great under postgres version 8.4 and postgres version 9.1, except for one thing in the case of 9.1: The autovacuum process on version 9.1 keeps vacuuming the master tables and that takes a lot of time considering the master table have no records of its own. The trigger itself insert into the master table, then into the child and then remove the record from the master, we do that way because we need to get the inserted row info. Actually we don't use any features of version 9.1 that are not available under version 8.4, however I don't want to downgrade to version 8.4 as I consider that I still have not understood completely how auto vacuuming process works, and I need to improve on this. Earlier in this list a user experienced the same behavior: http://www.postgresql.org/message-id/flat/CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@xxxxxxxxxxxxxx On the same thread another user wrote it could be an issue that is to be resolved at minor version 9.1.8 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5454344b968d6a189219cfd49af609a3e7d6af33 I currently have PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit running on an eight-core processor and 24 GB RAM with the following options: maintenance_work_mem = 1GB # pgtune wizard 2014-08-29 checkpoint_completion_target = 0.7 # pgtune wizard 2014-08-29 effective_cache_size = 15GB # pgtune wizard 2014-08-29 work_mem = 40MB # pgtune wizard 2014-08-29 wal_buffers = 4MB # pgtune wizard 2014-08-29 checkpoint_segments = 8 # pgtune wizard 2014-08-29 shared_buffers = 5GB # pgtune wizard 2014-08-29 max_connections = 500 # pgtune wizard 2014-08-29 In order to alleviate the I/O problem I disable autovacuum on all the master tables like so: ALTER TABLE public.tablename SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false); But I know I can't left the database without vacuuming enabled because of transaction ID wraparround. So I need to set up a cron script for this; for every master table there is a new child table every week then I can start a vacuum process via cron for the table before the newly created, these tables are only used for reading after a week. But I need some clarification on this: Why is postgresql starting a vacuum on master tables too often ? Why it takes too much time on version 9.1 ? I guess because it needs to reclaim unused space due to the insert/remove process, but in version 8.4 that is unnoticeable. How do I know which tables needs to be vacuumed ? any sql recipe ? How do I check when I am near the limit of the transaction ID ? Do I need to vacuum tables that haven't change a long time ago ? Anybody have experienced the same behavior and would like to comment on this ? Slds. -- Nestor A. Diaz -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
