From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: preventing transaction wraparound
Date: Thu, 13 Jan 2011 17:52:47 -0500
Message-ID: <87k4i8e7r4.fsf@cbbrowne.afilias-int.info> (raw)
References: <AANLkTi=FY3GksfSoU44k57=pgojbDrBgjDqeyrt4v7cK@mail.gmail.com>
mbroers@gmail.com (Mike Broers) writes:
> Lately I have been paranoid about the possibility of transaction wrap
> around failure due to a potential orphaned toast table. I have yet to
> prove that I have such an object in my database.. but I am running
> Postgres 8.3 with auto_vacuum enabled and am doing nightly manual
> vacuums as well and cannot explain the results of this query. Any
> assistance is greatly appreciated.
>
>
> Yesterday I ran:
>
> production=# select datname, age(datfrozenxid) from pg_database;
> datname | age
> ------------+-----------
> template1 | 100260769
> template0 | 35997820
> postgres | 100319291
> stage | 100263734
> production | 100319291
>
> and today after the nightly vacuum ran I got this:
>
> production=# select datname, age(datfrozenxid) from pg_database;
> datname | age
> ------------+-----------
> template1 | 100677381
> template0 | 37594611
> postgres | 100738854
> stage | 100680248
> production | 100738770
>
> Am I just counting down to 2,000,000,000 and the postgresapocolypse?
> Is there a way for me to determine what the actual transaction
> threshold is going to be? I've read the postgresql docs and greg
> smiths section in high performance and have to admit i am having
> difficulty understanding how this number is not retreating after a
> database manual vacuum.
Nothing is likely to be problematic here.
Tuples only get frozen once they're Rather Old. By default, the
"freeze" won't happen until the age reaches 150 million. See the value
of GUC vacuum_freeze_table_age.
http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
I'd expect to see the age increase towards 150M before anything more
happens.
I suggest you poke into this at a bit more detailed level, and peek at
the states of the tables in one of those databases via:
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'
order by 2;
This will tell you which tables have what going on with their freezing.
You could explicitly run VACUUM FREEZE against one or another of the
databases, which would cause all the affected tables' data to get
frozen, and if you did that against all the tables in (say) the
"postgres" database, you might anticipate seeing the age fall to near 0.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/
"You can measure a programmer's perspective by noting his attitude on
the continuing vitality of FORTRAN." -- Alan J. Perlis
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: cbbrowne@acm.org
Subject: Re: preventing transaction wraparound
In-Reply-To: <87k4i8e7r4.fsf@cbbrowne.afilias-int.info>
* 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