RE: Transaction ID wraparound: problem and proposed sol ution
This comparison will work as long as the range of interesting XIDs
never exceeds WRAPLIMIT/2. Essentially, we envision the actual value
of XID as being the low-order bits of a logical XID that always
increases, and we assume that no extant XID is more than WRAPLIMIT/2
transactions old, so we needn't keep track of the high-order bits.
So, we'll have to abort some long running transaction.
And before after-wrap XIDs will be close to aborted xid you'd better
ensure that vacuum *successfully* run over all tables in database
(and shared tables) aborted transaction could touch.
This scheme allows us to survive XID wraparound at the cost of slight
additional complexity in ordered comparisons of XIDs (which is not a
really performance-critical task AFAIK), and at the cost that the
original insertion XIDs of all but recent tuples will be lost by
VACUUM. The system doesn't particularly care about that, but old XIDs
do sometimes come in handy for debugging purposes. A possible
I wouldn't care about this.
compromise is to overwrite only XIDs that are older than, say,
WRAPLIMIT/4 instead of doing so as soon as possible. This would mean
the required VACUUM frequency is every WRAPLIMIT/4 xacts instead of
every WRAPLIMIT/2 xacts.We have a straightforward tradeoff between the maximum size of pg_log
(WRAPLIMIT/4 bytes) and the required frequency of VACUUM (at least
Required frequency of *successful* vacuum over *all* tables.
We would have to remember something in pg_class/pg_database
and somehow force vacuum over "too-long-unvacuumed-tables"
*automatically*.
every WRAPLIMIT/2 or WRAPLIMIT/4 transactions). This could be made
configurable in config.h for those who're intent on customization,
but I'd be inclined to set the default value at WRAPLIMIT = 1G.Comments? Vadim, is any of this about to be superseded by WAL?
If not, I'd like to fix it for 7.1.
If undo would be implemented then we could delete pg_log between
postmaster startups - startup counter is remembered in pages, so
seeing old startup id in a page we would know that there are only
long ago committed xactions (ie only visible changes) there
and avoid xid comparison. But ... there will be no undo in 7.1.
And I foresee problems with WAL based BAR implementation if we'll
follow proposed solution: redo restores original xmin/xmax - how
to "freeze" xids while restoring DB?
(Sorry, I have to run away now... and have to think more about issue).
Vadim
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
So, we'll have to abort some long running transaction.
Well, yes, some transaction that continues running while ~ 500 million
other transactions come and go might give us trouble. I wasn't really
planning to worry about that case ;-)
Required frequency of *successful* vacuum over *all* tables.
We would have to remember something in pg_class/pg_database
and somehow force vacuum over "too-long-unvacuumed-tables"
*automatically*.
I don't think this is a problem now; in practice you couldn't possibly
go for half a billion transactions without vacuuming, I'd think.
If your plans to eliminate regular vacuuming become reality, then this
scheme might become less reliable, but at present I think there's plenty
of safety margin.
If undo would be implemented then we could delete pg_log between
postmaster startups - startup counter is remembered in pages, so
seeing old startup id in a page we would know that there are only
long ago committed xactions (ie only visible changes) there
and avoid xid comparison. But ... there will be no undo in 7.1.
And I foresee problems with WAL based BAR implementation if we'll
follow proposed solution: redo restores original xmin/xmax - how
to "freeze" xids while restoring DB?
So, we might eventually have a better answer from WAL, but not for 7.1.
I think my idea is reasonably non-invasive and could be removed without
much trouble once WAL offers a better way. I'd really like to have some
answer for 7.1, though. The sort of numbers John Scott was quoting to
me for Verizon's paging network throughput make it clear that we aren't
going to survive at that level with a limit of 4G transactions per
database reload. Having to vacuum everything on at least a
1G-transaction cycle is salable, dump/initdb/reload is not ...
regards, tom lane
So, we'll have to abort some long running transaction.
Well, yes, some transaction that continues running while ~ 500 million
other transactions come and go might give us trouble. I wasn't really
planning to worry about that case ;-)
Agreed, I just don't like to rely on assumptions -:)
Required frequency of *successful* vacuum over *all* tables.
We would have to remember something in pg_class/pg_database
and somehow force vacuum over "too-long-unvacuumed-tables"
*automatically*.I don't think this is a problem now; in practice you couldn't possibly
go for half a billion transactions without vacuuming, I'd think.
Why not?
And once again - assumptions are not good for transaction area.
If your plans to eliminate regular vacuuming become reality, then this
scheme might become less reliable, but at present I think there's plenty
of safety margin.If undo would be implemented then we could delete pg_log between
postmaster startups - startup counter is remembered in pages, so
seeing old startup id in a page we would know that there are only
long ago committed xactions (ie only visible changes) there
and avoid xid comparison. But ... there will be no undo in 7.1.
And I foresee problems with WAL based BAR implementation if we'll
follow proposed solution: redo restores original xmin/xmax - how
to "freeze" xids while restoring DB?So, we might eventually have a better answer from WAL, but not for 7.1.
I think my idea is reasonably non-invasive and could be removed without
much trouble once WAL offers a better way. I'd really like to have some
answer for 7.1, though. The sort of numbers John Scott was quoting to
me for Verizon's paging network throughput make it clear that we aren't
going to survive at that level with a limit of 4G transactions per
database reload. Having to vacuum everything on at least a
1G-transaction cycle is salable, dump/initdb/reload is not ...
Understandable. And probably we can get BAR too but require full
backup every WRAPLIMIT/2 (or better /4) transactions.
Vadim
OK, 2^64 isn't mathematically unbounded, but let's see you
buy a disk that will hold it ;-). My point is that if we want
to think about allowing >4G transactions, part of the answer
has to be a way to recycle pg_log space. Otherwise it's still
not really practical.I kind of like vadim's idea of segmenting pg_log.
Segments in which all the xacts have been commited could be deleted.
Without undo we have to ensure that all tables are vacuumed after
all transactions related to a segment were committed/aborted.
Vadim
Import Notes
Resolved by subject fallback
Tom Lane wrote:
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
Required frequency of *successful* vacuum over *all* tables.
We would have to remember something in pg_class/pg_database
and somehow force vacuum over "too-long-unvacuumed-tables"
*automatically*.I don't think this is a problem now; in practice you couldn't possibly
go for half a billion transactions without vacuuming, I'd think.
ISTM you forgot that the XID counter (and usage) is global.
You need to have *any* table of *any* database in the
instance vacuumed before you are sure. Some low-traffic DB's
might not get vacuumed for years (for example template1).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #