The previous article described several common logs in MySQL system. In fact, there are transaction related logs, redo log and undo log. 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
As we all know, one of the four features of a transaction is
persistence ， Specifically, as long as the transaction is submitted successfully, 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 way is to refresh all the modified data pages involved in the transaction to disk every time the transaction is submitted. However, there will be serious performance problems, mainly reflected in two aspects:
- Because InnoDB interacts with disk based on pages, and a transaction may only modify a few bytes in a data page. It is a waste of resources to brush the complete data page to disk at this time.
- A transaction may involve modifying multiple data pages, and these data pages are not continuous physically, so the performance of random IO writing is poor.
So MySQL designed
redo log ， Specifically, it only records what changes the transaction has made to the data page, which can perfectly solve the performance problem (relatively speaking, the file is smaller and sequential IO).
Redo log includes two parts: one is the redo log buffer in memory, the other is the redo log file on disk. 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 later time point.
By default, the redo log is created on disk by
ib_logfile1 Two physical files are shown. The parameters related to redo log are described as follows:
- innodb_log_files_in_group: the number of redo log files, such as IB_ logfile0，iblogfile1… iblogfilen。 The default number is 2, and the maximum number 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 files, namely (InnoDB)_ log_ files_ in_ group * innodb_ log_ file_ Size) cannot be greater than the maximum of 512g.
- innodb_log_group_home_dir: Specifies the path of the redo log file group. By default, /, it is in the data directory of the database.
- innodb_log_buffer_size: redo log buffer size, 16m by default. Delay writing transaction log to disk, put redo log into the buffer, and then according to InnoDB_ flush_ log_ at_ trx_ Set the commit parameter, and then flush the log from the buffer to the disk.
- innodb_flush_log_at_trx_commit: control the policy of redo log refreshing to disk. The default is 1. The value is 1. Each commit writes the redo log from the redo log buffer to the system, and fsync refreshes it to the disk file. The value is 2. Each time a transaction is committed, MySQL will write the log from the redo log buffer to the system, but only to the file system buffer. If the database instance crashes, the redo log will not be lost. However, if the server crashes, because the file system buffer has no time to fsync to the disk file, this part of the data will be lost. A value of 0 indicates that the redo log will not be written when the transaction is submitted. This operation is only completed in the master thread, while the fsync operation of redo log is performed every 1 second in the master thread. Therefore, the transaction within 1 second can be lost in the instance crash at most.
To change the redo log and its buffer size, it is necessary to restart the database instance. It is recommended to evaluate it during initialization. You can increase the number and size of redo log groups appropriately, especially when your database instance is updated frequently. However, it is not recommended to set the redo log too large.
2. Undo log
undo log It is mainly used to ensure the atomicity of data. It saves a version of the data before the transaction and can be used for rollback. For example, an insert statement corresponds to a delete undo log. For each update statement, it corresponds to an opposite update undo log. In this way, when an error occurs, it can be rolled back to the data state before the transaction. At the same time, undo log is also the key to mvcc (multi version concurrency control).
In MySQL 5.7, undo log is stored in the shared table space ibdata by default. You can also change the configuration parameters into independent files during initialization, and briefly introduce several undo log related parameters
- innodb_max_undo_log_size: control the maximum undo tablespace file size when InnoDB is started_ undo_ log_ When truncate, undo tablespace exceeds InnoDB_ max_ undo_ log_ Only when the size threshold is reached will truncate be attempted. The default size of this value is 1g, and the default size after truncate is 10m.
- innodb_undo_tablespaces: set the number of undo independent table spaces in the range of 0-128. The default value of version 5.7 is 0. 0 means that independent undo table spaces are not enabled. This parameter can only be specified when the MySQL instance is initially initialized.
- innodb_undo_directory: set the storage directory of undo table space, the default data directory.
- innodb_undo_log_truncate: set whether the undo table space is automatically truncated and recycled. The precondition for this parameter to take effect is that independent table spaces have been set and the number of independent table spaces is greater than or equal to 2.
Undo log parameters are rarely changed. MySQL 8.0 enables independent table space by default. Maybe the size of undo log table space is more flexible.
This article mainly introduces the role of redo log and undo log and related parameter settings. The article is written in a hurry. If there is any error, you can leave a message to point out it. As for the deeper content of these two types of logs, the author may not have enough skills to write more thoroughly. Well, two articles about MySQL logs have been written. I hope you can learn a little.