Re: 8.1.8 autovacuum missing databases

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ian Westmacott <ianw(at)intellivid(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: 8.1.8 autovacuum missing databases
Date: 2008-05-01 13:57:28
Message-ID: 7698.1209650248@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Ian Westmacott <ianw(at)intellivid(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Carol Walter 2008-05-01 14:09:31 Re: Error on pg_dumpall
Previous Message Ian Westmacott 2008-05-01 13:01:22 Re: 8.1.8 autovacuum missing databases