Redo log in Mysql
1. What is redo log
The redo log is called the redo log, and the purpose is to ensure a mechanism for transaction durability. Ensure that after the mysql server crashes unexpectedly or goes down, it is guaranteed that the transaction that has been committed can be determined as a measure to be persisted to the disk.
2. Why do you need redo log
Innodb manages storage space in units of pages. Any addition, deletion, and modification operation will eventually operate on a complete page, load the entire page into the buffer pool, and then modify the records that need to be modified. The modification will not be immediate. Refresh to disk, because the refresh at this time is a random io, and only one record is modified, it would be too wasteful to refresh a complete data page. However, if it is not refreshed immediately, the data is still in the memory at this time. If a system crash occurs at this time, the data will eventually be lost. Therefore, weighing the pros and cons, the redo log is introduced, that is, after the modification, it is not refreshed immediately, but Record a log, the content of the log is to record which page, how many offsets, and what data has changed. In this way, even if the system crashes, after recovery, data can be recovered based on the redo log. In addition, redo log is written cyclically to a fixed file, and is written to disk sequentially.
3. Write in group
In one thing, multiple data modifications may occur, which corresponds to field changes in multiple offset positions of multiple data pages, that is to say, multiple redo logs will be generated, and because in the same thing, these The redo log is also inseparable, that is to say, when a group's redo log is persisted, it cannot partially succeed and partially fail, otherwise, the atomicity of the transaction will be destroyed. In addition, in order to improve performance, the redo log is organized in blocks and then written to the disk, similar to data pages, and the redo log buffer is introduced, the default size is 16MB. There are many blocks in the buffer, each block is 512kb in size, and all redo logs generated by each transaction are called a group.
4. When to refresh the redo log
The redo log sounds pretty awesome, but it was something in the memory at the beginning. What to do if a system crash occurs before being persisted to the disk. This facilitating redo log flushing timing is related: through a parameter control: innodb_flush_log_at_trx_commit
- 1. Flash disk when committing: This is also the safest, because if it crashes at this time, it means that the commit was not successful, so there is no need to restore any data.
- 2. When committing, it just refreshes the kernel buffer of the near os, and the specific refresh timing is uncertain.
- 0. The background thread is flushed to the disk every second.
In order to ensure the durability of the transaction, it is recommended to use 1.