What is the concept of MySQL redo logs

In the ACID characteristics of transactions, atomicity (A), consistency (C), and durability (D) are implemented by undo log and redo log, and isolation (I) is implemented by lock MVCC

undo log: The transaction has not been committed yet, and the execution is abnormal in the middle. You can use undo log to restore the data to the state before the transaction execution to ensure the atomicity of the transaction

redo log: The transaction commit is successful, because it takes a while to update the disk data Time, if an exception occurs at this time, you can use redo log to re-execute the SQL of this transaction to ensure the durability of the transaction (As long as the transaction commit is successful, no matter what abnormal events occur, as long as the MySQL service proceeds normally next time, then the The data of a commit must be restored)

1. Redo log concept

redo log: It is called a physical log and records the final modified data stored by page. page, which directly stores the final state of the data and is used to ensure the durability of the transaction.

The logical log, also known as the undo log, records the specific content of the corresponding SQL statement. If insert is executed now, delete will be executed when rolling back; if update is executed now, the original old value will be updated back.

The redo log is placed in /var/lib/mysql by default 下

The redo log starts recording when the transaction begins (it is not recorded when the transaction commits, because the entire transaction does the operation There may be many. If the redo log is written at the time of commit, once an exception occurs at this time, the redo log has not been written. It is too late and the durability of the transaction cannot be ensured.) No matter whether the transaction is committed or not, it will be recorded. When the exception occurs (such as a power outage during data persistence), InnoDB will use the redo log to restore to the moment before the power outage to ensure the integrity of the data

innodb_log_buffer_size defaults to 16M, which is the size of the redo log buffer. As the transaction begins, the redo log begins to be written. If the transaction is relatively large, in order to avoid spending too much disk IO during transaction execution, a relatively large redo log cache can be set to save disk IO. There is a time to refresh when flushing to the disk. When the time is reached, disk IO is consumed. If the buffer is relatively large, the time to refresh will be reached more slowly and the efficiency will be higher.

InnoDB modifies the operation data, not directly modifying the data on the disk, but actually only modifying the data in the Buffer Pool. InnoDB always first records the data changes in the Buffer Pool to the redo log for data recovery after a crash. Record the redo log first, and then find an opportunity to slowly refresh the dirty data in the Buffer Pool to the disk.

Two files in the directory specified by innodb_log_group_home_dir: ib_logfile0, ib_logfile1, this file is called the redo log

buffer pool Cache pool: Can store index cache, Data caching, etc., can speed up reading and writing, directly operate the data page, write the redo log modification, and even if it is completed, there will be a dedicated thread to write the dirty page in the buffer pool to the disk

The default size of the buffer pool is 134M ( MySQL 5.7)

The general structure is as shown in the figure:

Transaction reads and modifications all prioritize the cache operation Data in the pool. In actual projects, mysqld will run on a separate machine, and a large amount of memory can be allocated specifically for InnoDB's buffer pool to speed up CRUD

2. Cache and disk structure

When a transaction is committed, the operation on the relationship diagram is to write the contents of the InnoDB Log Buffer to the disk. If the writing is successful, the redo log on the disk will record the status - commit, if not If the writing is successful or completed, the record status - prepare

log may also have exceptions, power outages and other problems during the process of writing to the disk, resulting in the writing of the redo log not being completed (this Equivalent to the transaction not being committed successfully). At this time, MySQL does not need to consider the integrity of the transaction when it recovers next time, because the status is not commit. Only when everything is written to the disk does it mean that the redo log is written successfully, and the status becomes commit. . After the status changes to commit, the ACID characteristics of the transaction need to be maintained.

Is it true that the dirty data in the buffer poll (the data has been modified) is written to the disk only when committing?

You don’t need to wait for commit to start. The amount of data that the transaction may modify is relatively large, and the cache capacity is limited. For the data cached by buffer poll, there will be a dedicated thread to refresh the disk at the appropriate time. If there is a power outage, the next time MySQL starts, it will be refreshed according to the redo The data recorded in the log is restored.

undo log itself is also recorded in redo log

The undo log supports transaction rollback, and it cannot be completed in an instant. The data on the disk will eventually be modified. In order to prevent exceptions during the rollback process, the undo log must be recorded in the redo log. For the bottom layer, after the operation is successfully written to the redo log, whether the transaction is successfully committed (commit) or successfully rolled back (rollback), it is considered successful.

What is a real transaction commit success?

Instead of flushing all the data to the disk, the redo log recording the complete operation of the transaction is written to the disk from the log buffer, and then the status of the modified data is set to commit. The transaction commit was successful. Although the data is still in the buffer poll at this time, as long as our redo log is saved completely, the data can be restored. There will be a dedicated thread responsible for writing the data in the buffer poll to the disk.

Transactions are carried out When operating, always write the redo log first, and then write to the buffer pool; if the transaction is successfully committed, it is necessary to ensure that the redo log is completely recorded on the disk

As for the changes to the table data, the dirty data pages of the buffer pool We don’t have to worry about whether the transaction is flushed to the disk. As long as the redo log is completely written to the disk, we can use the redo log to restore the data status of the successfully committed transaction at any time (The most important thing about the database is the log. instead of data)

The above is the detailed content of What is the concept of MySQL redo logs. For more information, please follow other related articles on the PHP Chinese website!