Re: 8.1.8 autovacuum missing databases

On Thu, 2008-05-01 at 09:57 -0400, Tom Lane wrote:
> 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.

Done; I'll send pgstat.stat separately.

> 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 don't know if it is normal, but it is different. Now its just going
back and forth between itvtrackdata and itvtrackdatapos:

2008-05-01 13:30:37 EDT 10482 LOG: transaction ID wrap limit is
2147484146, limited by database "postgres"
2008-05-01 13:32:03 EDT 11295 LOG: autovacuum: processing database
"itvtrackdata"
2008-05-01 13:34:03 EDT 12384 LOG: autovacuum: processing database
"itvtrackdatapos"
2008-05-01 13:35:03 EDT 12897 LOG: autovacuum: processing database
"itvtrackdata"
2008-05-01 13:36:33 EDT 13769 LOG: autovacuum: processing database
"itvtrackdatapos"
2008-05-01 13:37:33 EDT 14292 LOG: autovacuum: processing database
"itvtrackdata"
2008-05-01 13:39:03 EDT 15124 LOG: autovacuum: processing database
"itvtrackdatapos"
2008-05-01 13:40:03 EDT 15713 LOG: autovacuum: processing database
"itvtrackdata"
2008-05-01 13:41:03 EDT 16387 LOG: autovacuum: processing database
"itvtrackdatapos"
2008-05-01 13:42:03 EDT 16925 LOG: autovacuum: processing database
"itvtrackdata"
2008-05-01 13:43:03 EDT 17510 LOG: autovacuum: processing database
"itvtrackdatapos"
2008-05-01 13:44:03 EDT 18085 LOG: autovacuum: processing database
"itvtrackdata"
2008-05-01 13:45:03 EDT 18722 LOG: autovacuum: processing database
"itvtrackdatapos"
2008-05-01 13:46:03 EDT 19447 LOG: autovacuum: processing database
"itvtrackdata"

> > 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.

itvtrackdata=> select datname,age(datfrozenxid),age(datvacuumxid) from
pg_database;
datname | age | age
------------------+-----------+-----------
postgres | 157900061 | 157900061
itvtrackdata | 157900061 | 157900061
itvtrackdatauser | 157900061 | 157900061
itvtrackdatapos | 157900061 | 157900061
template1 | 157900061 | 157900061
template0 | 157900061 | 157900061
(6 rows)

itvtrackdata=>

(they're all 499 too). Thanks,

--Ian