From: Alvaro Herrera <alvherre@commandprompt.com>
To: Steven Flatt <steven.flatt@gmail.com>
Cc: Jim Nasby <decibel@decibel.org>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Francisco Reyes <lists@stringsutils.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Database-wide VACUUM ANALYZE
Date: Tue, 26 Jun 2007 14:49:06 -0400
Message-ID: <20070626184906.GE11609@alvh.no-ip.org> (raw)
In-Reply-To: <357fa7590706261025l5b9cf016p7c61505643aabb9c@mail.gmail.com>
References: <357fa7590706211009w2ef6071fn83c5759db6be1e80@mail.gmail.com>
<cone.1182452313.269532.11564.5001@35st.simplicato.com>
<357fa7590706211236y2856ed96q5394b9302b9a528f@mail.gmail.com>
<20070621160732.58497e2b.wmoran@collaborativefusion.com>
<357fa7590706211337i55e9b20nea5a1bd4c5e41268@mail.gmail.com>
<FFD6DF28-B30D-47D0-BFB5-6485B32BF8AC@decibel.org>
<357fa7590706261025l5b9cf016p7c61505643aabb9c@mail.gmail.com>
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"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, steven.flatt@gmail.com, decibel@decibel.org, wmoran@collaborativefusion.com, lists@stringsutils.com
Subject: Re: Database-wide VACUUM ANALYZE
In-Reply-To: <20070626184906.GE11609@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox