Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 78BD09FB255 for ; Wed, 25 Oct 2006 17:00:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 65775-06 for ; Wed, 25 Oct 2006 20:00:12 +0000 (UTC) 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 CA5949FB258 for ; Wed, 25 Oct 2006 17:00:07 -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:54:26 +0000 Received: from localhost.localdomain by boston.agmednet.com; 25 Oct 2006 20:00:29 +0000 Subject: Re: Vacuum Problems (locking) From: Mike Goldner To: pgsql-admin@postgresql.org In-Reply-To: <1161806057.2095.154.camel@localhost.localdomain> References: <1161806057.2095.154.camel@localhost.localdomain> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Wed, 25 Oct 2006 16:00:29 -0400 Message-Id: <1161806429.2095.157.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/165 X-Sequence-Number: 23468 On Wed, 2006-10-25 at 15:54 -0400, Mike Goldner wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend I just noticed that my nightly vacuum failed the previous three nights with the following error: [2032-jbossdb-postgres-2006-10-24 04:12:30.019 EDT]ERROR: failed to re- find parent key in "jms_messages_pkey" [2032-jbossdb-postgres-2006-10-24 04:12:30.019 EDT]STATEMENT: VACUUM ANALYZE; I'm not sure if it is related, but it seems pertinent. Thanks, Mike