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)
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 |