X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5356BD9E9C for ; Mon, 3 Oct 2005 18:48:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45412-06 for ; Mon, 3 Oct 2005 21:48:19 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 9A25BD9AC5 for ; Mon, 3 Oct 2005 18:48:20 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id 03B4515257; Mon, 3 Oct 2005 16:48:22 -0500 (CDT) Date: Mon, 3 Oct 2005 16:48:22 -0500 From: "Jim C. Nasby" To: Kevin Grittner Cc: alvherre@alvh.no-ip.org, tgl@sss.pgh.pa.us, pgsql-admin@postgresql.org, Jeff Kirby Subject: Re: Vacuum Full Analyze Stalled Message-ID: <20051003214822.GM40138@pervasive.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 required=5 tests=[AWL=0.004] X-Spam-Level: X-Archive-Number: 200510/45 X-Sequence-Number: 19117 On Mon, Oct 03, 2005 at 04:37:17PM -0500, Kevin Grittner wrote: > We will use gdb and strace the next time we see this. > > I've tried to be specific about which vacuum is running in all cases. If > the posts have been confusing on that issue, I apologize. I'll try to be > clear on this in future posts. > > To summarize past events, the case involving the constraint index > was indeed a "vacuum full" of the entire database under heavy load. > Autovacuum failed to keep the small, high-update table clean in that > scenario, but I am not sure whether that caused the failure of the > vacuum full, or was the result of it. This weekend, it seemed like the > first thing which failed (and the last) were autovacuum attempts. > Vacuum full was run through psql during attempts to recover > performance after the failure of autovacuum caused performance > to slow noticably. We didn't capture info which would tell us whether > the explicit vacuum was blocked by an autovacuum process. Keep in mind that vacuum full is *very* aggressive for use in a production environment. It aquires exclusive locks on tables, which means everything else will grind to a complete halt while it's running. Unless you have a very specific reason to use vacuum full, you should just use plain vacuum (not related to autovacuum). If you are going to vacuum full, you should consider using the cluster command which has some added benefits. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461