Skip site navigation (1) Skip section navigation (2)

Oldest xmin is far in the past

From: Christian Rosnes <christian(dot)rosnes(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Oldest xmin is far in the past
Date: 2012-06-30 05:56:58
Message-ID: CAMgLkJMtdX01PLSr5FjbR-3Cgr1XvR3XXGPzTojjBV67LuxRMg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

Coming back from vacation, I find a PostgreSQL database cluster (Pg
version: 9.0.4)
with 5 databases, which for the past 5 days have logged these messages:

  WARNING:  oldest xmin is far in the past
  HINT:  Close open transactions soon to avoid wraparound problems.

The cluster has been running continously for over 9 months with autovacuum
enabled.

Doing a:

  vacuumdb -h <host> -U postgres -v -a

vacuums the tables, and also emits the same messages while vacuuming:

  WARNING:  oldest xmin is far in the past
  HINT:  Close open transactions soon to avoid wraparound problems.

Checking with:

 "SELECT datname, age(datfrozenxid) FROM pg_database;"

vacuum did not reduce the age of datfrozenxid:

  datname   |    age
------------+-----------
 template1  | 208249541
 template0  | 208249541
 postgres   | 208249541
 db1        | 208249541
 db2        | 208249541
 db3        | 208249541
 db4        | 208249541
 db5        | 208249541


Running

 "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' order
by age(relfrozenxid) desc;"

on each database, I see that the age is still high for some of the shared
catalogs:


**pg_class:
         relname         |    age
-------------------------+-----------
 pg_db_role_setting      | 208249542
 pg_authid               | 208249542
 pg_database             | 208249542
 pg_shdescription        | 208249542
 pg_shdepend             | 208249542
 pg_auth_members         | 208249542
 pg_pltemplate           | 208249542
 pg_tablespace           | 208249542


Max age of usergenerated tables (ie not pg_ tables):

  max age(relfrozenxid)
  db1: 169036643
  db2: 208249548
  db3: 169036553
  db4: 191294331
  db5: 64496585


Looking at 'db2' in the cluster, I see that in fact _every relation_ listed
with:

  "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'
order by age(relfrozenxid) desc;"

have

  age(relfrozenxid) = 208249548    (at the time of running the query)

Could the problem be in this 'db2' ?

However running 'vacuumdb -h <host> -U postgres -a' does not seem to fix
the problem.

Using:

  "select procpid, datname,usename,query_start from pg_stat_activity order
by query_start;"

I cannot find any 'idle in transaction' connected, and I can see one 'idle'
connection
(but not 'idle in transaction') which is almost 24 hours old, the rest is
from early this morning.

Also:

  ps auxww|grep 'trans'

finds no 'idle in transactions'.

So, I'm looking for ways to fix this 'wraparound' problem for this database
cluster.

Are any of these options candidates for solving the wraparound issue ?

OPTION 1) Shutdown all application brokers connected to the databases and
then do another

  vacuumdb -h <host> -U postgres -v -a

OPTION 2) Shutdown all application brokers connected, stop the database
cluster, start the database cluster
   and then do another

  vacuumdb -h <host> -U postgres -v -a

OPTION 3) Close all brokers connected to the database,
   do a full dump of db2 (which has user tables with highest xid age),
   drop database db2
   restore db2

OPTION 4) Close all brokers connected to the database,
   do a full dump of all 5 databases,
   rename the the datadb cluster dir: mv /dbcluster /dbcluster.old
   do a new initdb /dbcluster
   restore the 5 databases to new /dbcluster


Or are there anything else I could try to fix this ?


Thank you

Christian

Responses

pgsql-admin by date

Next:From: Jerry SieversDate: 2012-06-30 13:55:05
Subject: Re: Oldest xmin is far in the past
Previous:From: Magnus HaganderDate: 2012-06-29 10:22:18
Subject: Re: [ADMIN] pg_basebackup blocking all queries with horrible performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group