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