X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 64F0D9FA294 for ; Fri, 12 May 2006 22:13:30 -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 27211-03 for ; Fri, 12 May 2006 22:13:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from chilco.textdrive.com (chilco.textdrive.com [207.7.108.242]) by postgresql.org (Postfix) with ESMTP id 8EDC89FA15C for ; Fri, 12 May 2006 22:13:20 -0300 (ADT) Received: from [192.168.1.109] (unknown [209.149.57.250]) by chilco.textdrive.com (Postfix) with ESMTP id A3886DBB2E for ; Sat, 13 May 2006 01:13:18 +0000 (UTC) Mime-Version: 1.0 (Apple Message framework v750) Content-Transfer-Encoding: 7bit Message-Id: Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed To: pgsql-admin@postgresql.org From: "Thomas F. O'Connell" Subject: vacuumdb vs. max_connections: SELECT waiting Date: Fri, 12 May 2006 20:13:14 -0500 X-Mailer: Apple Mail (2.750) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/163 X-Sequence-Number: 21695 Tonight as part of a scheduled maintenance operation, I was going to perform a VACUUM FULL ANALYZE on a postgres 8.1.3 instance that had just had it's FSM settings increased to account for about 2 years' worth of growth (particularly in number of relations). Shortly after I kicked it off, I watched the number of connections trend upward as a result of the aggressive locking of FULL. I didn't want to let this continue without notifying the developers about a potential downtime for their application, so I killed the vacuumdb process I had started, figuring that the existing connections would go right through. Unfortunately, my expectations were not met, and I wound up with a bunch of connections in a waiting state. It seems as though the connections that were waiting for the VACUUM locks were still waiting even once the VACUUM was killed. Is that expected behavior? Eventually, a timeout threshold must've been hit because everything went back to normal, but it was not immediate. statement_timeout is set to 0, so it wasn't that. This postmaster does have autovacuum enabled. Could that have interfered in any way? On the one hand I'm curious about the behavior of postgres in this scenario. On the other hand, though, I'm wondering if this operation is necessary. The postmaster has been restarted such that the new FSM settings are in effect. Can autovacuum recover for the months where the FSM settings were not sufficient to cover the number of relations in this cluster? Is a vacuumdb/reindexdb cycle necessary to reclaim disk space? -- Thomas F. O'Connell Database Architecture and Programming Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)