From: Ian Westmacott <ianw@intellivid.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: 8.1.8 autovacuum missing databases
Date: Thu, 01 May 2008 13:58:11 -0400
Message-ID: <1209664691.16174.49.camel@vega.intellivid.com> (raw)
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>
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
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: ianw@intellivid.com, tgl@sss.pgh.pa.us, alvherre@commandprompt.com
Subject: Re: 8.1.8 autovacuum missing databases
In-Reply-To: <1209664691.16174.49.camel@vega.intellivid.com>
* 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