I co-presented a tutorial at SIGMOD. My part was a description of MVCC GC using Postgres, InnoDB and RocksDB as examples. By chance there is a proper paper in SIGMOD on MVCC GC and it is worth reading.
"Long-lived Transactions Made Less Harmful"
dl.acm.org/doi/pdf/10.114
Conversation
In addition to writing an interesting paper, it is quite a feat to learn enough about InnoDB and Postgres internals to implement the ideas from the Long-lived Transactions paper.
1
2
The paper names the InnoDB approach as off-row versioning (old versions stored in undo) and the Postgres approach as in-row versioning (old versions co-located in heap table pages). Their idea is SIRO (most recent old version in-row, others use off-row).
1
SIRO enables fast access for many (most) readers without the full cost of in-row versioning. SIRO is not blocked by long-open transactions.
1
1
Long-lived transactions are harmful because they block Postgres vacuum and InnoDB purge. Long-lived means a long running statement with read committed (RC) or a long-open snapshot, running statement or not, with repeatable read (RR). Maybe RR is more likely with MySQL.
1
Making purge work around long open transactions is a long open feature request for MySQL. See bugs.mysql.com/bug.php?id=749.
Hey
1
2
RocksDB MVCC GC is part of compaction and is not blocked by long open snapshots. It just searches the snapshot list to determine whether an old version of a key cannot be dropped because it is visible to a snapshot. This is easy in an LSM.
1
Performance can suffer in Postgres and InnoDB when vacuum/purge are blocked. The paper has result. See also smalldatum.blogspot.com/2015/07/the-im
1
3
Finally, a short editorial. LSM internals are a bit less complicated than B-Tree internals.
2
8
