Subject: Re: 8.1.8 autovacuum missing databases
From: Ian Westmacott <ianw@intellivid.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@commandprompt.com>, pgsql-admin@postgresql.org
In-Reply-To: <7698.1209650248@sss.pgh.pa.us>
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>
	<7698.1209650248@sss.pgh.pa.us>
Content-Type: text/plain
Organization: IntelliVid Corp.
Date: Thu, 01 May 2008 13:58:11 -0400
Message-Id: <1209664691.16174.49.camel@vega.intellivid.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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