Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1EB969FB266 for ; Wed, 25 Oct 2006 16:54:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 49959-09 for ; Wed, 25 Oct 2006 16:53:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from boston.agmednet.com (boston.agmednet.com [64.156.16.20]) by postgresql.org (Postfix) with ESMTP id 8F1F49FB1D5 for ; Wed, 25 Oct 2006 16:53:56 -0300 (ADT) Received: from 70.91.219.145 ([70.91.219.145]) by agmednet-dc.boston.agmednet.local ([10.10.39.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 25 Oct 2006 19:48:14 +0000 Received: from localhost.localdomain by boston.agmednet.com; 25 Oct 2006 19:54:17 +0000 Subject: Vacuum Problems (locking) From: Mike Goldner To: pgsql-admin@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Wed, 25 Oct 2006 15:54:17 -0400 Message-Id: <1161806057.2095.154.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-27.rhel4.6) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.636 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, RCVD_NUMERIC_HELO, UNPARSEABLE_RELAY X-Spam-Level: * X-Archive-Number: 200610/163 X-Sequence-Number: 23466 I have a nightly vacuum scheduled as follows: su - postgres -c "/usr/bin/vacuumdb --analyze --dbname=mydb" Last night, it appears that the vacuum blocked db access from my application server (JBoss). Here is the logfile snippet: [3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]NOTICE: number of page slots needed (559472) exceeds max_fsm_pages (200000) [3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 559472. [3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]LOG: number of page slots needed (559472) exceeds max_fsm_pages (200000) [3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 559472. [3693-jbossdb-postgres-2006-10-25 06:52:29.530 EDT]LOG: transaction ID wrap limit is 1073813592, limited by database "benchpg" [3693-jbossdb-postgres-2006-10-25 06:52:29.530 EDT]LOG: transaction ID wrap limit is 1073813592, limited by database "benchpg" [3693-jbossdb-postgres-2006-10-25 06:52:29.530 EDT]LOG: duration: 10224738.050 ms statement: VACUUM ANALYZE; First of all, my max_fsm_pages is obviously way off. However, every time I increase my max_fsm_pages the next vacuum says that it requires more. Will there every be a plateau in the requested pages? What is the impact of exceeding the pages slots needed? Secondly, in this case, it almost looks like there were two vacuum process running based on the duplicated log messages. There is only one cron job, so there shouldn't have been a second vacuum running. Should I just ignore this? Finally, and most important is the blocking. The vacuum duration reported in the log converts to about 170 minutes. I can track backwards in the log and the only messages prior to the 6:52am completion of the vacuum end at 3:57am (almost exactly 170 minutes prior). So, all indications point to postgres blocking all access during the entire vacuum. The only semi-unusual characteristic of my database is that I have one table in particular that grows and shrinks dramatically. My entire database after a vacuum --full is about 8GB. However, after a day or two, the db will grow as large as 75GB. Truncating that one table (since it empties regularly) will shrink the db right back to the 8GB. Is it possible that the extreme table size (maybe requiring the high number of page slots) is causing postgres to block more than it should in order to process that table? Thanks, Mike