From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: 8.1.8 autovacuum missing databases
Date: Fri, 02 May 2008 12:40:58 -0400
Message-ID: <7391.1209746458@sss.pgh.pa.us> (raw)
In-Reply-To: <20080502161709.GH2320@alvh.no-ip.org>
References: <1209574908.22509.6.camel@vega.intellivid.com>
<20185.1209575233@sss.pgh.pa.us>
<1209576488.22509.9.camel@vega.intellivid.com>
<22093.1209588237@sss.pgh.pa.us>
<1209646882.16174.15.camel@vega.intellivid.com>
<7698.1209650248@sss.pgh.pa.us>
<1209664799.16174.51.camel@vega.intellivid.com>
<17328.1209667453@sss.pgh.pa.us>
<1209668403.16174.60.camel@vega.intellivid.com>
<17606.1209669106@sss.pgh.pa.us>
<20080502161709.GH2320@alvh.no-ip.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I'm wondering if there could be a hidden bug in the age arithmetic
> somehow -- say because it's using unsigned comparison of signed
> variables, or the Max() macro is flawed, or something weird like that.
Umm ... actually, there's a bug in the hand calculations I did at the
start of this thread. The threshold that 8.1 uses is
(int32) ((MaxTransactionId >> 3) * 3 - 100000)
which works out to 1610512733, which is considerably more than the 300M
or so age() values that Ian is showing. I had somehow convinced myself
that it was less and he should be getting whole-db vacuums, but in
reality he shouldn't yet. It's only in 8.2 and up that we have a much
smaller threshold for trying to advance datfrozenxid.
So actually, what we are looking at is the not-whole-db logic, and
it's working exactly as designed. The only unexpected behavior was
the choice not to vacuum some of the DBs, which we now know is explained
by their having a last_autovac_time in the future.
Perhaps it is worth adding a defense to autovac to not believe last
autovac times that are in the future, since the system clock is not
under our control and could go backwards. Other than that I don't
think there's any bug here.
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, alvherre@alvh.no-ip.org, ianw@intellivid.com
Subject: Re: 8.1.8 autovacuum missing databases
In-Reply-To: <7391.1209746458@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