From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Westmacott <ianw@intellivid.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: 8.1.8 autovacuum missing databases
Date: Thu, 01 May 2008 09:57:28 -0400
Message-ID: <7698.1209650248@sss.pgh.pa.us> (raw)
In-Reply-To: <1209646882.16174.15.camel@vega.intellivid.com>
References: <1209499373.18622.47.camel@vega.intellivid.com>
<20080429203335.GK5652@alvh.no-ip.org>
<1209503103.18622.68.camel@vega.intellivid.com>
<20080429211244.GM5652@alvh.no-ip.org>
<1209560546.5209.12.camel@vega.intellivid.com>
<20080430161942.GC21286@alvh.no-ip.org>
<19778.1209572869@sss.pgh.pa.us>
<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>
Ian Westmacott <ianw@intellivid.com> writes:
> I imagine if a database's last process time is in the future, that would
> mess up autovacuum's choice algorithm (at the least). Can I confirm
> this is the problem? Is there a way to fix it short of dump/restore?
Hmm, that would explain a lot wouldn't it ... except that it looks to
me like your databases have old enough datvacuumxid to force vacuuming
regardless of last_autovac_time. So I'm not fully convinced.
AFAICS there is no convenient way in 8.1 to examine the per-database
last_autovac_time entries, which'd be needed to confirm this theory.
We should check that just to be sure. Please do the following:
1. Stop the postmaster.
2. Move the file $PGDATA/global/pgstat.stat somewhere else.
3. Restart the postmaster.
4. Send pgstat.stat as a binary attachment to me or Alvaro.
This will reset all your statistics counters and also the
last_autovac_time settings. If autovac starts to behave more
normally then we'll know that was it.
> I mentioned earlier that I had two installations like this. The second
> one doesn't seem to have any time issues. But in that case the only
> database being skipped is postgres. Do I need to worry about that?
Is the age() getting unreasonably large for postgres? It might be
skipping it because there's been no activity.
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, ianw@intellivid.com, alvherre@commandprompt.com
Subject: Re: 8.1.8 autovacuum missing databases
In-Reply-To: <7698.1209650248@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