Detailed explanation of MySQL redo log (redo log) and rollback log (undo logo)

Preface:

The previous article described several common logs in the MySQL system. In fact, there are transaction-related logs, redo log and undo log, which have not been introduced. Compared with other logs, redo log and undo log are more mysterious and difficult to observe. This article will mainly introduce the functions and operation and maintenance methods of these two types of transaction logs.

1. Redo log

We all know that one of the four major characteristics of transactions is persistence. Specifically, as long as the transaction is successfully submitted, the changes made to the database will be permanently saved and it is impossible to return to the original state for any reason. So how does MySQL ensure consistency? The simplest approach is to refresh all modified data pages involved in the transaction to disk each time the transaction is committed. However, doing so will have serious performance problems, mainly in two aspects:

• Because Innodb interacts with the disk in pages, and a transaction may only modify a few bytes in a data page, it would be a waste of resources to flush the entire data page to the disk at this time.
• A transaction may involve modifying multiple data pages, and these data pages are not physically continuous, so the performance of using random IO writes is too poor.
  

Therefore, MySQL designed the redo log, which specifically records only the modifications made by the transaction to the data page. This can perfectly solve the performance problem (relatively speaking, the file is smaller and is sequential IO).

The redo log consists of two parts: one is the log buffer in memory (redo log buffer), and the other is the log file on the disk (redo log file). Each time MySQL executes a DML statement, it first writes the record to the redo log buffer, and then writes multiple operation records to the redo log file at a certain point in time.

By default, the redo log is represented on disk by two physical files named ib_logfile0 and ib_logfile1. The redo log related parameters are briefly introduced as follows:

• innodb_log_files_in_group: The number of redo log files, named as ib_logfile0, iblogfile1...iblogfilen. The default is 2, and the maximum is 100.
• innodb_log_file_size: Set the size of a single redo log file. The default value is 48M and the maximum value is 512G. Note that the maximum value refers to the sum of the entire redo log series of files, that is, (innodb_log_files_in_group * innodb_log_file_size) cannot be greater than the maximum value of 512G.
• innodb_log_group_home_dir: Specifies the path where the redo log file group is located. The default value is ./, which means it is in the database data directory.
• innodb_log_buffer_size: redo log buffer size, default 16M. Delay writing the transaction log to disk, put the redo log in the buffer, and then flush the log from the buffer to disk according to the setting of the innodb_flush_log_at_trx_commit parameter.
• innodb_flush_log_at_trx_commit: Controls the strategy for flushing redo log to disk. The default value is 1. If the value is 1, each commit will write the redo log from the redo log buffer to the system and fsync it to the disk file. When the value is 2, MySQL will write the log from the redo log buffer to the system each time a transaction is committed, but only to the file system buffer, which is fsynced to the disk file by the system itself. If the database instance crashes, the redo log will not be lost. However, if the server crashes, the file system buffer will not have time to fsync to the disk file, so this part of the data will be lost. A value of 0 indicates that the redo log is not written when the transaction is committed. This operation is only completed in the master thread, and the fsync operation of the redo log is performed once every 1 second in the master thread. Therefore, if the instance crashes, transactions within 1 second are lost at most.
  

Changing the redo log and its buffer size requires restarting the database instance. It is recommended to make an assessment during initialization. You can increase the number and size of redo log groups appropriately, especially if your database instance is updated frequently. However, it is not recommended to set the redo log size too large.

2. Undo log

Undo log is mainly used to ensure the atomicity of data. It saves a version of the data before the transaction occurs and can be used for rollback. For example, for an INSERT statement, there is a corresponding undo log of a DELETE, and for each UPDATE statement, there is a corresponding undo log of the opposite UPDATE, so that when an error occurs, the data can be rolled back to the state before the transaction. At the same time, undo log is also the key to the implementation of MVCC (multi-version concurrency control).

In MySQL 5.7, undo logs are stored in the shared tablespace ibdata by default. You can also change it to a separate file by configuring parameters during initialization. Here are some undo log related parameters:

• innodb_max_undo_log_size: controls the maximum size of the undo tablespace file. When innodb_undo_log_truncate is enabled, truncate is attempted only when the undo tablespace exceeds the innodb_max_undo_log_size threshold. The default value is 1G, and the default value after truncation is 10M.
• innodb_undo_tablespaces: Set the number of independent undo tablespaces, ranging from 0 to 128. The default value in version 5.7 is 0, which means that independent undo tablespace is not enabled. This parameter can only be specified when you first initialize the MySQL instance.
• innodb_undo_directory: Set the storage directory of the undo tablespace, the default data directory.
• innodb_undo_log_truncate: Sets whether the undo tablespace is automatically truncated and recycled. The premise for this parameter to take effect is that independent tablespaces have been set and the number of independent tablespaces is greater than or equal to 2.
  

Undo log related parameters are rarely changed. MySQL 8.0 enables independent tablespaces by default, which may make the size setting of the undo log tablespace more flexible.

Summarize:

This article mainly introduces the role of redo log and undo log and related parameter settings. The article was written in a hurry. If there are any errors, please leave a message to point them out. As for the deeper contents of these two types of logs, perhaps the author is not yet competent enough to write more thoroughly. Well, two articles about MySQL related logs have been written. I hope you can learn something.