Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 75FA01337BFB for ; Tue, 3 May 2011 14:32:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 68725-06-5 for ; Tue, 3 May 2011 17:32:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 463071337C40 for ; Tue, 3 May 2011 14:31:46 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Tue, 03 May 2011 12:31:45 -0500 Message-Id: <4DBFF5AE020000250003D1D9@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Tue, 03 May 2011 12:31:42 -0500 From: "Kevin Grittner" To: "Todd Gugler" , Subject: Re: Can autovacuum result in large surges in WAL files References: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/33 X-Sequence-Number: 37470 Todd Gugler wrote: > Should a vacuum analyze lead to large amounts of data being > written to WAL files? Yes, if it has to "freeze" the rows to prevent transaction wraparound. > We are seeing 10x the number WAL files that we would expect. > > Any ideas on the best approach to diagnose what is going on? Is it vacuuming a table which was bulk loaded at some time in the past? If so, this can happen any time later (usually during busy periods when many transactions numbers are being assigned). To prevent this in the future, run VACUUM FREEZE ANALYZE after any bulk load. There has been much talk about possible ways to avoid this behavior by initially setting bulk loaded data to "frozen", but there are technical problems which haven't been adequately addressed yet. -Kevin