autovacuum, xid wraparound, pg_database column values

From: "shakahshakah(at)gmail(dot)com" <shakahshakah(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: autovacuum, xid wraparound, pg_database column values
Date: 2006-06-06 18:24:04
Message-ID: 1149618244.094670.18550@j55g2000cwa.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to make sense of Section 22.1.3 ("Preventing transaction ID
wraparound failures") in the on-line docs to make sure the DB's
autovacuum settings are adequate and are having an effect.

The docs state:
"The age column measures the number of transactions from the cutoff
XID to the current transaction's XID."
and:
"With the standard freezing policy, the age column will start at one
billion for a freshly-vacuumed database."

I don't see numbers larger than 1 billion in my pg_database columns,
though. Can anyone offer any information or pointers to a good on-line
explanation of vacuuming? The database in question is doing about 3M
inserts/day (in about 500K transactions/day) and has been up for a
little more than 3 months.

The database config:

postgres(at)rs02:~$ egrep autovacuum /data02/pgsql/data/postgresql.conf
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 120 # time between autovacuum runs,
in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for

postgres(at)rs02:~$ /usr/local/pgsql/bin/psql reporting
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

reporting=# select version() ;
version

----------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)
(1 row)

reporting=# select datname, datvacuumxid, datfrozenxid from pg_database
;
datname | datvacuumxid | datfrozenxid
-----------+--------------+--------------
postgres | 499 | 499
reporting | 499 | 499
template1 | 499 | 499
template0 | 499 | 499
(4 rows)

reporting=# SELECT datname, age(datfrozenxid) FROM pg_database ;
datname | age
-----------+----------
postgres | 27995112
reporting | 27995112
template1 | 27995112
template0 | 27995112
(4 rows)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sergio Duran 2006-06-06 18:54:01 Trigger function to audit any kind of table
Previous Message Jessica M Salmon 2006-06-06 17:40:05 composite data type question