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 ABA1BD9EF8 for ; Mon, 3 Oct 2005 19:28:08 -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 57166-01 for ; Mon, 3 Oct 2005 22:28:03 +0000 (GMT) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.91]) by svr1.postgresql.org (Postfix) with ESMTP id 721F5D9EFE for ; Mon, 3 Oct 2005 19:28:03 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 03 Oct 2005 17:28:06 -0500 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.4 Date: Mon, 03 Oct 2005 17:27:51 -0500 From: "Kevin Grittner" To: Cc: , , , "Jeff Kirby" Subject: Re: Vacuum Full Analyze Stalled Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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/46 X-Sequence-Number: 19118 My goal is to avoid vacuum full in production. My understanding is that it is never necessary if vacuums are done aggressively enough, but I felt that while we were in beta test mode, it was worthwhile for me to have it run periodically, with the verbose option, to provide information about where we might need to adjust our vacuum schedule or fsm settings. =20 Since the long-term blocking on the constraint index occurred, I have asked that we run these during non-peak loads, and I'm getting to the point where I think I can be satisfied with the verbose results of a normal vacuum for these purposes, even though it provides less detail. Are there any tools which provide the level of detail you get from vacuum full verbose without the problems? =20 When our small table has bloated, we have tried vacuum full in an attempt to eliminate the bloat, but we have had to resort to reindex table to clean things up adequately. We have tried cluster, which also worked -- but there doesn't seem to me to be any real advantage over vacuum followed by reindex for our small, frequently updated table, since it is rarely accessed sequentially. Am I missing something there? =20 -Kevin =20 =20 >>> "Jim C. Nasby" 10/03/05 4:48 PM >>> 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. > =20 > 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. > =20 > 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. --=20 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