pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedunderstand the process of ID wraparound
4+ messages / 2 participants
[nested] [flat]
* understand the process of ID wraparound
@ 2008-09-29 13:40 KKreuzer@kbtoys.com
0 siblings, 1 reply; 4+ messages in thread
From: KKreuzer@kbtoys.com @ 2008-09-29 13:40 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: understand the process of ID wraparound
@ 2008-09-29 14:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: KKreuzer@kbtoys.com
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-09-29 14:00 UTC (permalink / raw)
To: KKreuzer@kbtoys.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: understand the process of ID wraparound
@ 2008-09-29 14:16 KKreuzer@kbtoys.com
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: KKreuzer@kbtoys.com @ 2008-09-29 14:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
To
KKreuzer@kbtoys.com
cc
pgsql-admin@postgresql.org
Subject
Re: [ADMIN] understand the process of ID wraparound
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: understand the process of ID wraparound
@ 2008-09-29 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: KKreuzer@kbtoys.com
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2008-09-29 14:23 UTC (permalink / raw)
To: KKreuzer@kbtoys.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-09-29 14:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-09-29 13:40 understand the process of ID wraparound KKreuzer@kbtoys.com
2008-09-29 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-09-29 14:16 ` KKreuzer@kbtoys.com
2008-09-29 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
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