Обсуждение: understand the process of ID wraparound
I am hoping someone can help a novice understand the process of ID wraparound, I have read many of the articles
on the web but don't understand why my age(datfrozenxid) never gets reset. I am not sure if I even have a
problem, just trying to be proactive.
First the details:
select version() ;
"PostgreSQL 8.2.6 on powerpc-ibm-aix5.2.0.0, compiled by GCC gcc (GCC) 4.0.0"
show vacuum_freeze_min_age;
"100,000,000"
show autovacuum_freeze_max_age;
"200,000,000"
show autovacuum;
"off"
SELECT datname, age(datfrozenxid) FROM pg_database;
"postgres" 31041670
"dprodxml" 31041670
"dflash" 31041670
"pg_dprodcca" 31041670
"template1" 31041670
"template0" 31041670
"dstorens" 31041670
"dprod360" 31041670
We run a vacuum every morning at 2:45 am: vacuumdb --all --analyze --echo
vacuumdb: vacuuming database "postgres"
SELECT datname FROM pg_database WHERE datallowconn;
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dprodxml"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dflash"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "pg_dprodcca"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "template1"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dstorens"
VACUUM ANALYZE;
VACUUM
vacuumdb: vacuuming database "dprod360"
VACUUM ANALYZE;
VACUUM
I run the query "SELECT datname, age(datfrozenxid) FROM pg_database;" every morning with the
values continue to rise.
age(datfrozenxid)
9/24 27,280,414
9/25 27,688,967
9/26 28,166,896
9/29 31,040,346
If someone could help me understand the process, it would be greatly appreciated.
Keith Kreuzer
ext 3424
KKreuzer@kbtoys.com writes:
> I am hoping someone can help a novice understand the process of ID
> wraparound, I have read many of the articles
> on the web but don't understand why my age(datfrozenxid) never gets reset.
> I am not sure if I even have a
> problem, just trying to be proactive.
You don't have a problem. The datfrozenxid values you are showing are
around 31 million transactions. Nothing is going to happen until they
exceed vacuum_freeze_min_age, which is 100 million transactions.
regards, tom lane
Thank you Tom..
Can you recommend any documentation that explains the process?
Keith Kreuzer
ext 3424
| Tom Lane <tgl@sss.pgh.pa.us> 09/29/2008 10:00 AM |
|
KKreuzer@kbtoys.com writes:
> I am hoping someone can help a novice understand the process of ID
> wraparound, I have read many of the articles
> on the web but don't understand why my age(datfrozenxid) never gets reset.
> I am not sure if I even have a
> problem, just trying to be proactive.
You don't have a problem. The datfrozenxid values you are showing are
around 31 million transactions. Nothing is going to happen until they
exceed vacuum_freeze_min_age, which is 100 million transactions.
regards, tom lane
KKreuzer@kbtoys.com writes: > Can you recommend any documentation that explains the process? Did you read http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html (adjust URL if you are running some other major PG version) regards, tom lane