Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9E6B39FB681 for ; Tue, 26 Jun 2007 15:49:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79827-08 for ; Tue, 26 Jun 2007 15:49:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 45B729FB769 for ; Tue, 26 Jun 2007 15:49:23 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-218-82.bk12-dsl.surnet.cl [201.221.218.82]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l5QInAPH000686; Tue, 26 Jun 2007 11:49:10 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 9E1A847C42; Tue, 26 Jun 2007 14:49:06 -0400 (CLT) Date: Tue, 26 Jun 2007 14:49:06 -0400 From: Alvaro Herrera To: Steven Flatt Cc: Jim Nasby , Bill Moran , Francisco Reyes , pgsql-performance@postgresql.org Subject: Re: Database-wide VACUUM ANALYZE Message-ID: <20070626184906.GE11609@alvh.no-ip.org> References: <357fa7590706211009w2ef6071fn83c5759db6be1e80@mail.gmail.com> <357fa7590706211236y2856ed96q5394b9302b9a528f@mail.gmail.com> <20070621160732.58497e2b.wmoran@collaborativefusion.com> <357fa7590706211337i55e9b20nea5a1bd4c5e41268@mail.gmail.com> <357fa7590706261025l5b9cf016p7c61505643aabb9c@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <357fa7590706261025l5b9cf016p7c61505643aabb9c@mail.gmail.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV 0.90.3/3526/Tue Jun 26 02:14:26 2007 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 26 Jun 2007 11:49:16 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/602 X-Sequence-Number: 25635 Steven Flatt escribió: > Most of our large (partitioned) tables are insert-only (truncated > eventually) so will not be touched by autovacuum until wraparound prevention > kicks in. However the tables are partitioned by timestamp so tables will > cross the 1.9 billion marker at different times (some not at all, as the > data will have been truncated). Note that as of 8.3, tables that are truncated do not need vacuuming for Xid wraparound purposes, because the counter is updated on TRUNCATE (as it is on CLUSTER and certain forms of ALTER TABLE). > Do you still think the 250 million transactions away from shutdown is > cutting it too close? Recall that the unintentional db-wide vacuum analyze > that was going on last week on our system took less than two days to > complete. Is this 8.1 or 8.2? In the latter you don't ever need db-wide vacuums at all, because Xid wraparound is tracked per table, so only tables actually needing vacuum are processed. To answer your question, the followup question is how many transactions normally take place in two days. If they are way less than 250 million then you don't need to worry. Otherwise, the database may shut itself down to protect from Xid wraparound. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "La soledad es compañía"