Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 684092E014C for ; Sun, 2 Mar 2008 12:32:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 71258-01 for ; Sun, 2 Mar 2008 12:32:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id D4E182E003B for ; Sun, 2 Mar 2008 12:32:02 -0400 (AST) Received: from 71.233.123.244 ([71.233.123.244]) by agmednet-dc.boston.agmednet.local ([192.168.67.2]) with Microsoft Exchange Server HTTP-DAV ; Sun, 2 Mar 2008 16:26:01 +0000 User-Agent: Microsoft-Entourage/12.0.0.071130 Date: Sun, 02 Mar 2008 11:31:58 -0500 Subject: Re: Is my vacuumdb stuck in a loop? From: Michael Goldner To: Tom Lane CC: "pgsql-admin@postgresql.org" Message-ID: Thread-Topic: [ADMIN] Is my vacuumdb stuck in a loop? Thread-Index: Ach8gu8n4HXsdWQHIUGEiJRxW/lyuA== In-Reply-To: <20299.1204474559@sss.pgh.pa.us> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.01 tagged_above=0 required=5 tests=AWL=-0.589, RCVD_NUMERIC_HELO=2.599 X-Spam-Level: ** X-Archive-Number: 200803/6 X-Sequence-Number: 28489 On 3/2/08 11:15 AM, "Tom Lane" wrote: > "Michael Goldner" writes: >> Am I stuck in a loop, or is this happening because the size of the relation >> is so large that postgres is operating on smaller chunks? > > It's removing as many dead rows at a time as it can handle. Arithmetic > suggests that you've got maintenance_work_mem set to 64MB, which would > be enough room to process 11184810 rows per index scanning cycle. > That is exactly correct regarding maintenance_work_mem. Maybe I should double this. I think my server has sufficient memory. Is there any downside? > The fact that there are so many dead large objects is what I'd be > worrying about. Does that square with your sense of what you've > removed, or does it suggest you've got a large object leak? Do you > use contrib/lo and/or contrib/vacuumlo to manage them? I am in the process of archiving off about 1.4 million large objects from a total of 1.7 million. I removed about 700,000 before running the vacuum. Each large object averages about 256k, with outliers as big as 100MB. At 2k per row in pg_largeobject (if I understand correctly), the number of dead large objects doesn't seem unreasonable. > > The numbers also suggest that you might be removing all or nearly > all of the rows in pg_largeobject. If so, a CLUSTER on it might > be more effective than VACUUM as a one-shot cleanup method. > My understanding is that CLUSTER is a blocking operation. My database supports a 24x7 operation, so I cannot bring the system offline for extended periods. How fast is the "CLUSTER" operation? Given my database, am I looking at minutes, hours, or days? Can a CLUSTER be cancelled without negative consequences if I see it is taking longer than I can allow? Thanks -- Mike Goldner