From: Tom Lane <tgl@sss.pgh.pa.us>
To: Natalie Wenz <nataliewenz@ebureau.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum to prevent wraparound data loss
Date: Fri, 09 Mar 2012 00:19:34 -0500
Message-ID: <20453.1331270374@sss.pgh.pa.us> (raw)
In-Reply-To: <0008A0CD-58B3-4FC8-9976-E5D2B41E5E82@ebureau.com>
References: <8CC19810-BB3B-4511-9DC4-0C38AE120B79@ebureau.com>
<0008A0CD-58B3-4FC8-9976-E5D2B41E5E82@ebureau.com>
Natalie Wenz <nataliewenz@ebureau.com> writes:
> [ waiting for autovacuum to deal with an impending XID wraparound ]
> My first question is: Is it even possible for this vacuum to finish? It began before the database stopped accepting connections, but now that it's in this state, will the database allow the vacuum to complete? Can the vacuum successfully freeze old xids and advance the datfrozenxid? I suspect no, based on
Yes. yes, and yes. But ... the problem with letting autovacuum do this
is that it's intentionally rate-limited so that it doesn't interfere
with foreground query processing. Since you are waiting around for it
to finish before you can use the DB at all, this is a bad thing.
I think it might be possible to disable the autovacuum cost limit
settings in postgresql.conf and SIGHUP the postmaster to make that
update take effect ... but I'm not at all sure that the autovac worker
would notice the changes until it finished the table it's working on.
Try it, but if you don't see either CPU or I/O usage maxed out within a
minute or so, the best bet is likely to kill the autovac worker (with
SIGINT) and let a new one start. Doing that will lose some fraction of
the work done so far, so don't do it unnecessarily --- but if the
machine is just sitting there 90% idle, you need to do something.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, nataliewenz@ebureau.com
Subject: Re: Vacuum to prevent wraparound data loss
In-Reply-To: <20453.1331270374@sss.pgh.pa.us>
* 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