2

I was reading about InnoDB undo logs, and in the process, the articles i was reading ended up confusing me on what undo logs are, and how they are different from the history list, or rather the same.

So quoting article one by the MariaDB team, they defined history list as:

There is also a global history list of the data. When a transaction is committed, its history is added to this history list. The order of the list is the chronological order of the commits.

Then in the same article, they went on to say this, which now brought even more confusion what the relationship is between undo logs, history list, and views of rows in the apparent undo logs:

Understanding how the undo log works helps with understanding the negative effects long transactions.

The second article [2] by Percona describes the history list as:

History list length 6 is number of unpurged transactions in undo space. It is increased as transactions which have done updates are commited and decreased as purge runs.

And lastly, the third article [3] tried to address the confusion by many of what the history list is, but in my opinion, just brought more confusion in distinguishing the undo logs from the history list, and how they both relate to views of rows that innodb transactions us to enforce consistency by MVCC support.Here is how the third article defined the history list:

The best name for the unit (history list) is undo logs, which are “update undo logs for committed transactions”, to quote the source. But an “undo log” is a special term with specific meaning in InnoDB. An undo log is a single set of atomic changes a transaction makes, which may actually modify multiple records.

The first article [1] added this to even confuse me more:

Understanding how the undo log works helps with understanding the negative effects long transactions.

  • Long transactions generate several old versions of the rows in the undo log. Those rows will probably be needed for a longer time, because other long transactions will need them. Since those transactions will generate more modified rows, a sort of combinatory explosion can be observed. Thus, the undo log requires more space.

  • Transaction may need to read very old versions of the rows in the history list, thus their performance will degrade.

Does the InnoDB undo log contain two types of data, one being views of rows for transaction to use stored as linked-lists, and the other being the history list, which keeps track of committed transaction in the buffer pool (not yet flushed to disk)? Can someone please help make a clear distinction between the undo logs, and how they relate to views of rows (created for running transaction to use) and the history list?

[1] https://mariadb.com/kb/en/library/xtradbinnodb-undo-log/
[2] https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
[3] https://www.vividcortex.com/blog/2015/07/20/what-is-innodb-history-list-length/
giogio
  • 23
  • 7

1 Answers1

2

I'll take a swing at this :) My colleagues and I are pouring over how to make a succinct but accurate answer for this;

on what undo logs are, and how they are different from the history list,

Undo logs are produced when data modifications are made. If a transaction modifies data with an INSERT, UPDATE, DELETE for example it will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction. There's a one to many relationship between Transaction and Undo Log (one per operation, remember).

My understanding is that the Innodb History List Length is the count of the discrete operations in the Undo Log(s). Undo logs are part of a structure called Rollback Segments and live in the global table space (ibdata1) or in UNDO tablespaces (a recent feature in MySQL 8.0).

Does the InnoDB undo log contain two types of data, one being views of rows for transaction to use stored as linked-lists, and the other being the history list

No. The undo log will contain instructions on how to revert data to previous state and if other transactions need to see an old version of a row, it will need to use the undo logs (instructions to rollback) in order to honour the scope of it's read view and present previous versions of the data.

There is a background purge operation (analogous to Postgres vacuum) that garbage collects aged undo logs. It may only purge up to the oldest live transaction.

I hope my answer helps you to visualise things a little clearer. If I can tidy up further I'll make edits or if any InnoDB experts wish to chime in, please do so.

eroomydna
  • 1,031
  • 5
  • 5