Re: Transaction Id Space, Freezing and Wraparound
On 11/20/18 13:00, Tom Lane wrote: > If the stored XIDs were 64 bits wide, we'd not have to bother with all > of this mess ... but adding another 64 bits to tuple headers would be > a painful space cost, not to mention the storage compatibility issues. People keep saying that. But didn't someone come up with a way to do this by storing the top 32 bits just once somewhere in the block, rather than adding 64 bits to every tuple? I can't remember if there was an email thread. Maybe some other drawbacks to that approach, but lets at least point out adding 64 bits to the tuple header isn't the only way to accomplish this. And the other thread about memory management... if I'm going to start religious wars, might as well just start them all at once right? ;) -J -- http://about.me/jeremy_schneider
Re: Transaction Id Space, Freezing and Wraparound
Martín On Tue, Nov 20th, 2018 at 6:0 PM, Tom Lane wrote: > > > > =?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= < fmarti...@gmail.com > writes: > > First thing that generated a lot of noise in my head was the following, > if pg assigns contiguous numeric values for the txid, how does pg deal > with fragmentation issues ? Then I later found that the txid space is > actually circular and not linearly as I originally thought it was. This > exposed me to the fact that the txid is actually exported as a 64bit value > where the last 32bits are an epoch. My understanding is that the epoch is > the component that allows the circularity of the data structure. I then > started analyzing how pg decides if a given tuple is eligible for > freezing. I found out that pg will compare a cutoff_tx (I assume this is > the last committed tx) with the xmin value of the given tuple, if xmin > precedes the cutoff_tx the tuple is eligible (I’m ignoring > HEAP_XMAX_IS_MULTI and HEAP_MOVED cases). Now, the xmin of a tuple is an > 32 bit integer, so, how is the epoch part of an exported txid considered > here ? What if we had a database really old where a txid with integer > value 10 is greater than a txid of value 1000 ? > > Actually, XID epoch is an artifact that's bolted on for possible use by > replication or what have you. So far as the core database is concerned, > XIDs are 32 bits in a circular space, and the way that we deal with your > question is we don't let the case arise. Every old tuple must be marked > "frozen" before its XID gets to be 2 billion XIDs old; after that, we > don't particularly care just how old it is. The whole "wraparound" > business just exists to make sure that happens in time. > > If the stored XIDs were 64 bits wide, we'd not have to bother with all > of this mess ... but adding another 64 bits to tuple headers would be > a painful space cost, not to mention the storage compatibility issues. > > regards, tom lane > > > > Tom, Thanks for the insight!! I got confused with the comment under "Transaction IDs and Snapshots"( https://www.postgresql.org/docs/current/functions-info.html ) "The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an "epoch" counter so it will not wrap around during the life of an installation. "
Re: Transaction Id Space, Freezing and Wraparound
=?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= writes: > First thing that generated a lot of noise in my head was the following, if pg > assigns contiguous numeric values for the txid, how does pg deal with > fragmentation issues ? Then I later found that the txid space is actually > circular and not linearly as I originally thought it was. This exposed me to > the fact that the txid is actually exported as a 64bit value where the last > 32bits are an epoch. My understanding is that the epoch is the component that > allows the circularity of the data structure. I then started analyzing how pg > decides if a given tuple is eligible for freezing. I found out that pg will > compare a cutoff_tx (I assume this is the last committed tx) with the xmin > value of the given tuple, if xmin precedes the cutoff_tx the tuple is > eligible (I’m ignoring HEAP_XMAX_IS_MULTI and HEAP_MOVED cases). Now, the > xmin of a tuple is an 32 bit integer, so, how is the epoch part of an > exported txid considered here ? What if we had a database really old where a > txid with integer value 10 is greater than a txid of value 1000 ? Actually, XID epoch is an artifact that's bolted on for possible use by replication or what have you. So far as the core database is concerned, XIDs are 32 bits in a circular space, and the way that we deal with your question is we don't let the case arise. Every old tuple must be marked "frozen" before its XID gets to be 2 billion XIDs old; after that, we don't particularly care just how old it is. The whole "wraparound" business just exists to make sure that happens in time. If the stored XIDs were 64 bits wide, we'd not have to bother with all of this mess ... but adding another 64 bits to tuple headers would be a painful space cost, not to mention the storage compatibility issues. regards, tom lane
Transaction Id Space, Freezing and Wraparound
Hello everyone, second time writing to this awesome mailing list. I’m helping manage a postgresql 9.2.24 high volume transaction database and yesterday we were literally one hour away of having to deal with transaction id wraparound. We were really lucky about identifying the issue one hour before getting materialized, that gave us some time to reduce the transaction load so that we could run VACUUM FREEZE on our hottest table. By running VACUUM FREEZE on our hottest table we were able to move our oldest txid by almost 1 billion. After almost experiencing transaction id wraparound I decided to start investigating deeper around how postgresql works with transactions ids and wraparound in general. First thing that generated a lot of noise in my head was the following, if pg assigns contiguous numeric values for the txid, how does pg deal with fragmentation issues ? Then I later found that the txid space is actually circular and not linearly as I originally thought it was. This exposed me to the fact that the txid is actually exported as a 64bit value where the last 32bits are an epoch. My understanding is that the epoch is the component that allows the circularity of the data structure. I then started analyzing how pg decides if a given tuple is eligible for freezing. I found out that pg will compare a cutoff_tx (I assume this is the last committed tx) with the xmin value of the given tuple, if xmin precedes the cutoff_tx the tuple is eligible (I’m ignoring HEAP_XMAX_IS_MULTI and HEAP_MOVED cases). Now, the xmin of a tuple is an 32 bit integer, so, how is the epoch part of an exported txid considered here ? What if we had a database really old where a txid with integer value 10 is greater than a txid of value 1000 ? I’m probably missing something here so bare with me if my previous explanation doesn’t make sense at all. As usual, thanks before hand, any insight will be appreciated. PD: I based my research looking at 9.2.24 code base. Best, Martín
