4 ms·

So the root issue here is that postgres is using a 32-bit transaction ID?

by hendzen 11y ago

polskibus 11y ago
Exactly my thought, I wonder if anyone can shed some light on this matter.
snuxoll 11y ago
To an extent, you wouldn't want the transaction ID to be any bigger either because of the clog - which postgres needs to determine row visibility.
- anarazel 11y ago
  That's the 'clog' (commit log), not the xlog (WAL, for durability).
  - snuxoll 11y ago
    Bah, you're right, I've edited my post. You'd think after working with PostgreSQL for years I'd have my terminology straight.
claar 11y ago
Some Googling reveals that they've discussed 64-bit TXID since 2005, but resisted due to performance and complexity concerns. The two biggest discussion threads w/Tom Lane seem to be: 2005 http://postgresql.nabble.com/Re-PATCHES-lastval-td2213404.html http://postgresql.nabble.com/Re-PATCHES-lastval-td2213404.ht... 2012 http://postgresql.nabble.com/PostgreSQL-64-Bit-XIDs-Transaction-IDs-td5588598.html http://postgresql.nabble.com/PostgreSQL-64-Bit-XIDs-Transact...
- claar 11y ago
  Oops, got that first link wrong, sorry. 2005 http://postgresql.nabble.com/A-couple-of-patches-for-PostgreSQL-64bit-support-td2214264.html http://postgresql.nabble.com/A-couple-of-patches-for-Postgre...
jandrewrogers 11y ago
Not really. There are good technical reasons for why most MVCC database engines intentionally avoid using transaction ids with enough bits such that they will never plausibly wraparound. More on that in a moment. Empirically, you need at least a 64-bit transaction id to practically mitigate the possibility of wraparound; even MVCC systems with 48-bit transaction ids have wrapped around in real systems. Consequently, most databases like PostgreSQL are designed under the assumption that wraparound will happen and provide mechanisms for smoothly managing that in the hands of someone that knows what they are doing. No matter how much automatic safety you build into the database, there are performance optimization knobs that enable an incompetent or unskilled DBA to effectively disable or render ineffective the transaction wraparound management mechanisms. This is clearly explained in database documentation. So back to why transaction id wraparound exists as a thing: In an MVCC database, each logical record you store usually has multiple transaction ids associated with it that are stored with the record. Because most relational database records are typically quite small, the transaction id overhead in the heap row headers can be a significant percentage of the total storage cost. If you store transactional information in your indexes as well (some do, some don't) the relative space consumption is even worse. This has an enormous performance impact. A typical row header in an OLTP database is 20-30 bytes. In cases where implementors have shaved a couple bytes off the row header size with heroic efforts, it has generated a 5-10% performance boost across the board due to the more compact representation. The difference between a 32-bit transaction id and a 64-bit transaction id is at least 8 bytes! Yes, you could use 64-bit transaction ids but at the cost of a database that is much more bloated -- and therefore slower -- than the 32-bit version. Since there are good technical solutions to transaction id wraparound, it would be silly to make everyone suffer from poor performance to keep a few poor DBAs from shooting themselves in the foot. That is not to say you should never use a 64-bit transaction id in a database engine design -- I have, in fact -- but that you should understand the technical tradeoffs and implications.
- caf 11y ago
  Is there a reason that you couldn't store the transaction IDs in the row headers in a more compact form - for example using the full 64 bits for txmin, but storing txmax as a 24 bit delta from txmin?
Kassandry 11y ago
From their article: "Additionally we had been running with far too much delay in vacuuming which meant lower load on the system, but more idle time in maintenance." So, vacuum wasn't given the resources to keep up with their load, and it's not clear if they were supplementing with manual vacuums during quiet times. Nor was it clear when they started reacting, as PostgreSQL (also outlined in their documentation link), will start squawking about wraparound well before it shuts down, or the monitoring they had in place for whether or not autovacuum/vacuum was keeping up with the workload, or the number of locks their application was taking (locks can block autovacuum). Adjusting the autovacuum settings by table will give you finer control over the postgresql.conf parameters to better match the workload for specific tables, as well. Partitioning would have also helped make the actively written part of the table smaller, and older data could be vacuumed with VACUUM FREEZE, or deleted later. There are extensions to help make that easier. https://github.com/keithf4/pg_partman https://github.com/keithf4/pg_partman Josh Berkus of PgExperts has some great advice here: https://dba.stackexchange.com/questions/21068/aggressive-autovacuum-on-postgresql https://dba.stackexchange.com/questions/21068/aggressive-aut... So does Jim Nasby, here: http://bluetreble.com/2014/10/postgres-mvcc-and-a-look-at-vacuuming/ http://bluetreble.com/2014/10/postgres-mvcc-and-a-look-at-va... Regarding monitoring, check_postgres.pl would give them an idea that their vacuuming settings needed adjustment sooner, or their application locking needed to be adjusted to not block autovacuum. https://bucardo.org/check_postgres/check_postgres.pl.html#txn_wraparound https://bucardo.org/check_postgres/check_postgres.pl.html#tx... https://bucardo.org/check_postgres/check_postgres.pl.html#last_autovacuum https://bucardo.org/check_postgres/check_postgres.pl.html#la... https://bucardo.org/check_postgres/check_postgres.pl.html#locks https://bucardo.org/check_postgres/check_postgres.pl.html#lo... It's easy to mention things after the fact, though, and it's good they got things up and running again for their customers.
- zeeg 11y ago
  David from Sentry here. We actually knew about the problem with delay and had been working to improve it. We were a couple days away from failing over to the new hardware (safely) and unfortunately we didn't have any early warnings in the logs. I haven't yet looked at why.
juliangregorian 11y ago
Shame you're being downvoted. 2 billion is not that big a number and I found it surprising since other databases are using GUID's and UUID's and similar to deal with that problem. There was an unfortunate lack of explanation for that in the article.

So the root issue here is that postgres is using a 32-bit transaction ID?

by hendzen 11y ago

So the root issue here is that postgres is using a 32-bit transaction ID?

polskibus 11y ago

Exactly my thought, I wonder if anyone can shed some light on this matter.

snuxoll 11y ago

To an extent, you wouldn't want the transaction ID to be any bigger either because of the clog - which postgres needs to determine row visibility.

anarazel 11y ago
That's the 'clog' (commit log), not the xlog (WAL, for durability).
- snuxoll 11y ago
  Bah, you're right, I've edited my post. You'd think after working with PostgreSQL for years I'd have my terminology straight.

claar 11y ago

Some Googling reveals that they've discussed 64-bit TXID since 2005, but resisted due to performance and complexity concerns. The two biggest discussion threads w/Tom Lane seem to be: 2005 http://postgresql.nabble.com/Re-PATCHES-lastval-td2213404.html http://postgresql.nabble.com/Re-PATCHES-lastval-td2213404.ht... 2012 http://postgresql.nabble.com/PostgreSQL-64-Bit-XIDs-Transaction-IDs-td5588598.html http://postgresql.nabble.com/PostgreSQL-64-Bit-XIDs-Transact...

claar 11y ago
Oops, got that first link wrong, sorry. 2005 http://postgresql.nabble.com/A-couple-of-patches-for-PostgreSQL-64bit-support-td2214264.html http://postgresql.nabble.com/A-couple-of-patches-for-Postgre...

jandrewrogers 11y ago

Not really. There are good technical reasons for why most MVCC database engines intentionally avoid using transaction ids with enough bits such that they will never plausibly wraparound. More on that in a moment. Empirically, you need at least a 64-bit transaction id to practically mitigate the possibility of wraparound; even MVCC systems with 48-bit transaction ids have wrapped around in real systems. Consequently, most databases like PostgreSQL are designed under the assumption that wraparound will happen and provide mechanisms for smoothly managing that in the hands of someone that knows what they are doing. No matter how much automatic safety you build into the database, there are performance optimization knobs that enable an incompetent or unskilled DBA to effectively disable or render ineffective the transaction wraparound management mechanisms. This is clearly explained in database documentation. So back to why transaction id wraparound exists as a thing: In an MVCC database, each logical record you store usually has multiple transaction ids associated with it that are stored with the record. Because most relational database records are typically quite small, the transaction id overhead in the heap row headers can be a significant percentage of the total storage cost. If you store transactional information in your indexes as well (some do, some don't) the relative space consumption is even worse. This has an enormous performance impact. A typical row header in an OLTP database is 20-30 bytes. In cases where implementors have shaved a couple bytes off the row header size with heroic efforts, it has generated a 5-10% performance boost across the board due to the more compact representation. The difference between a 32-bit transaction id and a 64-bit transaction id is at least 8 bytes! Yes, you could use 64-bit transaction ids but at the cost of a database that is much more bloated -- and therefore slower -- than the 32-bit version. Since there are good technical solutions to transaction id wraparound, it would be silly to make everyone suffer from poor performance to keep a few poor DBAs from shooting themselves in the foot. That is not to say you should never use a 64-bit transaction id in a database engine design -- I have, in fact -- but that you should understand the technical tradeoffs and implications.

caf 11y ago
Is there a reason that you couldn't store the transaction IDs in the row headers in a more compact form - for example using the full 64 bits for txmin, but storing txmax as a 24 bit delta from txmin?

Kassandry 11y ago

From their article: "Additionally we had been running with far too much delay in vacuuming which meant lower load on the system, but more idle time in maintenance." So, vacuum wasn't given the resources to keep up with their load, and it's not clear if they were supplementing with manual vacuums during quiet times. Nor was it clear when they started reacting, as PostgreSQL (also outlined in their documentation link), will start squawking about wraparound well before it shuts down, or the monitoring they had in place for whether or not autovacuum/vacuum was keeping up with the workload, or the number of locks their application was taking (locks can block autovacuum). Adjusting the autovacuum settings by table will give you finer control over the postgresql.conf parameters to better match the workload for specific tables, as well. Partitioning would have also helped make the actively written part of the table smaller, and older data could be vacuumed with VACUUM FREEZE, or deleted later. There are extensions to help make that easier. https://github.com/keithf4/pg_partman https://github.com/keithf4/pg_partman Josh Berkus of PgExperts has some great advice here: https://dba.stackexchange.com/questions/21068/aggressive-autovacuum-on-postgresql https://dba.stackexchange.com/questions/21068/aggressive-aut... So does Jim Nasby, here: http://bluetreble.com/2014/10/postgres-mvcc-and-a-look-at-vacuuming/ http://bluetreble.com/2014/10/postgres-mvcc-and-a-look-at-va... Regarding monitoring, check_postgres.pl would give them an idea that their vacuuming settings needed adjustment sooner, or their application locking needed to be adjusted to not block autovacuum. https://bucardo.org/check_postgres/check_postgres.pl.html#txn_wraparound https://bucardo.org/check_postgres/check_postgres.pl.html#tx... https://bucardo.org/check_postgres/check_postgres.pl.html#last_autovacuum https://bucardo.org/check_postgres/check_postgres.pl.html#la... https://bucardo.org/check_postgres/check_postgres.pl.html#locks https://bucardo.org/check_postgres/check_postgres.pl.html#lo... It's easy to mention things after the fact, though, and it's good they got things up and running again for their customers.

zeeg 11y ago
David from Sentry here. We actually knew about the problem with delay and had been working to improve it. We were a couple days away from failing over to the new hardware (safely) and unfortunately we didn't have any early warnings in the logs. I haven't yet looked at why.

juliangregorian 11y ago

Shame you're being downvoted. 2 billion is not that big a number and I found it surprising since other databases are using GUID's and UUID's and similar to deal with that problem. There was an unfortunate lack of explanation for that in the article.