X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AF05F5372B for ; Tue, 5 Apr 2005 18:47:13 +0100 (BST) 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 25728-04 for ; Tue, 5 Apr 2005 17:47:09 +0000 (GMT) Received: from mailhost.intellivid.com (mailhost.intellivid.com [64.32.200.11]) by svr1.postgresql.org (Postfix) with ESMTP id 1A3155371B for ; Tue, 5 Apr 2005 18:47:08 +0100 (BST) Received: from [192.168.2.68] (spectre.intellivid.com [192.168.2.68]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by newmail.intellivid.com (Postfix) with ESMTP id 97A41F18174; Tue, 5 Apr 2005 13:47:08 -0400 (EDT) Subject: Re: VACUUM and read-mostly tables From: Ian Westmacott To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <13828.1112715280@sss.pgh.pa.us> References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net> <10364.1112683320@sss.pgh.pa.us> <1112713990.8115.83.camel@spectre.intellivid.com> <13828.1112715280@sss.pgh.pa.us> Content-Type: text/plain Message-Id: <1112723228.8115.110.camel@spectre.intellivid.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Tue, 05 Apr 2005 13:47:08 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/64 X-Sequence-Number: 17077 On Tue, 2005-04-05 at 11:34, Tom Lane wrote: > Ian Westmacott writes: > > But the question is whether vacuum freezing tables will > > help me reduce the frequency of a full vacuum, or reduce > > its cost when we do it? That is, if more transactions > > are frozen, will a full vacuum be more efficient > > (primarily in the I/O)? > > I'm not sure if you are actually confused, or are just using confusing > terminology ... but there's a significant difference between VACUUM FULL > (ie, vacuum and try to reclaim space) and a database-wide vacuum. I > *think* you are using "full vacuum" to mean "database-wide vacuum" but > it's not entirely clear. Yes, sorry, bad choice of words. By "full vacuum" I meant "database-wide". > Anyway, the frequency with which you have to do database-wide vacuums to > avoid XID wraparound is determined entirely by the rate at which you use > up XIDs. Doing piecemeal VACUUM FREEZEs would reduce the amount of work > that needs to be done in the eventual database-wide vacuum, but it's not > clear that it'd be a net win given the added work of the extra VACUUM > scans. But potentially I could spread the cost out over time, making it less disruptive when it occurs, right? > Have you looked at whether you can slow down the rate of XID consumption > (ie, by bundling operations into larger transactions)? That might be a > more useful route to limiting the costs involved. Yes, I'd like to explore that. Essentially what happens now is that a number of rows are written to each of about two dozen tables, each with a COPY FROM STDIN, and then there are a few INSERTS and UPDATES to boot. As far as we are concerned, all of this could be a single transaction. My understanding is that all the inserts resulting from a COPY are a single transaction, but is it possible to make multiple COPYs a single transaction? Or would I have to do them all as individual INSERTs and make the whole thing a single transaction? Would that be more costly? Thanks, --Ian