pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtransaction id wraparound
4+ messages / 3 participants
[nested] [flat]
* transaction id wraparound
@ 2006-09-28 18:16 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 2 replies; 4+ messages in thread
From: Sriram Dandapani @ 2006-09-28 18:16 UTC (permalink / raw)
To: pgsql-admin
When I run this query
fwdb01=# select current_timestamp,datname,age(datfrozenxid) from
pg_database;
now | datname | age
-------------------------------+-----------+------------
2006-09-28 18:04:24.489935+00 | postgres | 1087834006
2006-09-28 18:04:24.489935+00 | fwdb01 | 2039254861
2006-09-28 18:04:24.489935+00 | template1 | 2039253122
2006-09-28 18:04:24.489935+00 | template0 | 1542808250
(4 rows)
fwdb01=# select current_timestamp,datname,age(datfrozenxid) from
pg_database;
now | datname | age
------------------------------+-----------+------------
2006-09-28 18:10:45.64452+00 | postgres | 1088357075
2006-09-28 18:10:45.64452+00 | fwdb01 | 2039777930
2006-09-28 18:10:45.64452+00 | template1 | 2039776191
2006-09-28 18:10:45.64452+00 | template0 | 1543331319
In approximately 6 minutes, the fwdb01 count has gone up by about 500K.
I am generating about 250K rows for every 6 . I am also running
vacuumdb.
Does vacuumdb generate a lot of transactions that affects this counter.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: transaction id wraparound
@ 2006-09-28 18:58 Talha Khan <talha.amjad@gmail.com>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 1 reply; 4+ messages in thread
From: Talha Khan @ 2006-09-28 18:58 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
Hi sriram,
datfrozenxid column of pg_database row is updated at the completion of any
database wide vacuum operation. The value store here is the cuttoff xid used
by the vacuum operation all xid's older then this cutoffxid are replaced by
theis xid so i think the behaviour being shown by your database is quite
normal.
Regards
Talha Khan
The datfrozenxid column of a database's pg_database row is updated at the
completion of any database-wide VACUUM operation (i.e., VACUUM that does not
name a specific table). The value stored in this field is the freeze cutoff
XID that was used by that VACUUM command. All normal XIDs older than this
cutoff XID are guaranteed to have been replaced by FrozenXID within that
database
On 9/28/06, Sriram Dandapani <sdandapani@counterpane.com> wrote:
>
> When I run this query
>
>
>
> fwdb01=# select current_timestamp,datname,age(datfrozenxid) from
> pg_database;
>
> now | datname | age
>
> -------------------------------+-----------+------------
>
> 2006-09-28 18:04:24.489935+00 | postgres | 1087834006
>
> 2006-09-28 18:04:24.489935+00 | fwdb01 | 2039254861
>
> 2006-09-28 18:04:24.489935+00 | template1 | 2039253122
>
> 2006-09-28 18:04:24.489935+00 | template0 | 1542808250
>
> (4 rows)
>
>
>
> fwdb01=# select current_timestamp,datname,age(datfrozenxid) from
> pg_database;
>
> now | datname | age
>
> ------------------------------+-----------+------------
>
> 2006-09-28 18:10:45.64452+00 | postgres | 1088357075
>
> 2006-09-28 18:10:45.64452+00 | fwdb01 | 2039777930
>
> 2006-09-28 18:10:45.64452+00 | template1 | 2039776191
>
> 2006-09-28 18:10:45.64452+00 | template0 | 1543331319
>
>
>
>
>
> In approximately 6 minutes, the fwdb01 count has gone up by about 500K. I
> am generating about 250K rows for every 6 . I am also running vacuumdb.
>
>
>
> Does vacuumdb generate a lot of transactions that affects this counter.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: transaction id wraparound
@ 2006-09-28 19:29 Jim C. Nasby <jim@nasby.net>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-09-28 19:29 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
Interesting... age(xid) isn't documented anywhere.
No, vacuum shouldn't be generating a lot of xid's. My guess is that your
generating process actually does 2 transactions per row.
On Thu, Sep 28, 2006 at 11:16:24AM -0700, Sriram Dandapani wrote:
> When I run this query
>
>
>
> fwdb01=# select current_timestamp,datname,age(datfrozenxid) from
> pg_database;
>
> now | datname | age
>
> -------------------------------+-----------+------------
>
> 2006-09-28 18:04:24.489935+00 | postgres | 1087834006
>
> 2006-09-28 18:04:24.489935+00 | fwdb01 | 2039254861
>
> 2006-09-28 18:04:24.489935+00 | template1 | 2039253122
>
> 2006-09-28 18:04:24.489935+00 | template0 | 1542808250
>
> (4 rows)
>
>
>
> fwdb01=# select current_timestamp,datname,age(datfrozenxid) from
> pg_database;
>
> now | datname | age
>
> ------------------------------+-----------+------------
>
> 2006-09-28 18:10:45.64452+00 | postgres | 1088357075
>
> 2006-09-28 18:10:45.64452+00 | fwdb01 | 2039777930
>
> 2006-09-28 18:10:45.64452+00 | template1 | 2039776191
>
> 2006-09-28 18:10:45.64452+00 | template0 | 1543331319
>
>
>
>
>
> In approximately 6 minutes, the fwdb01 count has gone up by about 500K.
> I am generating about 250K rows for every 6 . I am also running
> vacuumdb.
>
>
>
> Does vacuumdb generate a lot of transactions that affects this counter.
>
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: transaction id wraparound
@ 2006-09-29 00:40 Jim C. Nasby <jim@nasby.net>
parent: Talha Khan <talha.amjad@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-09-29 00:40 UTC (permalink / raw)
To: Talha Khan <talha.amjad@gmail.com>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-admin
On Thu, Sep 28, 2006 at 11:58:12AM -0700, Talha Khan wrote:
> datfrozenxid column of pg_database row is updated at the completion of any
> database wide vacuum operation. The value store here is the cuttoff xid used
> by the vacuum operation all xid's older then this cutoffxid are replaced by
> theis xid so i think the behaviour being shown by your database is quite
> normal.
True, but if age(xid) is showing how many transactions have occured
since xid then his conclusion that he did 500k transactions between
those two snapshots is correct.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-09-29 00:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-28 18:16 transaction id wraparound Sriram Dandapani <sdandapani@counterpane.com>
2006-09-28 18:58 ` Talha Khan <talha.amjad@gmail.com>
2006-09-29 00:40 ` Jim C. Nasby <jim@nasby.net>
2006-09-28 19:29 ` Jim C. Nasby <jim@nasby.net>
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