Redo log origin

The introduction of Buffer Pool does improve the efficiency of data reading and writing. Of course, it also brings problems. My account balance was 200 yuan, and my company wired 5000 yuan on payday.

update account set price = 5200 where id = 24

Copy the code

We know that after the update, the data is only stored in the Buffer Pool, and the disk file is still the old data. But unfortunately, this time the server power, memory data suddenly no. What can I do? Take-home pay is gone. Of course, Mysql does not allow this to happen. Committed transactions must be persistent. In view of the above situation, Mysql must ensure that if the update statement is successful, then the subsequent power failure, crash and other situations, the server restart data is still modified after the data. To do this, redo logs were created. We can say that redo logs provide persistence for InnoDB. With the redo log, the update flow becomes:

1. Modify the information in the corresponding page of the Buffer Pool. (Assuming the page already exists in the Buffer Pool)
2. Redo log: change the balance of account 24 to 5200. (Of course, the actual situation can be complicated, such as logging indexes and other changes. By the way, there are 53 types of redo log in Mysql 5.7.21.)
3. Return updated results

In this way, even if the Buffer Pool does not have enough time to flush to disk after the modification, the redo log can be read and redo when the database restarts. This ensures that lost data in the Buffer Pool can be recovered.

redo log buffer

Redo logs are not written to disk. Redo logs are written to the redo log Buffer before they are written to disk. Again, how do YOU ensure that the redo log buffer is not lost in a redo log crash? Redo log buffer flush time:

• Description The redo log buffer space was insufficient.
• When the transaction commits
• Background threads are constantly brushing
• When the service is normally shut down
• checkpointwhen

In terms of flush timing, redo log buffers are flushed more frequently, especially during transaction commits. This ensures that the redo log of the committed transaction is stored on disk. Innodb_flush_log_at_trx_commit value range: 0,1,2 innodb_flush_log_at_trx_commit controls whether the redo log buffer is flushed to disk during a transaction commit. 0: indicates that the redo log is not synchronized to disk. 1: the default value. The redo log is synchronized to the disk each time a transaction is committed. 2: Each commit transaction writes the redo log to the operating system buffer, but not to disk. Each of these three methods has its own advantages and disadvantages: 0: Although writing to disk in batch order improves the efficiency of disk operation, there is still some performance loss in the end. If the variable is set to 0, the disk operation is completely avoided when the transaction is committed, and the background thread is left to refresh the disk. This can improve performance in a large number of frequently modified business scenarios, but also face the risk of data loss when the server is down. 1: the performance is not as good as 0, but the committed transaction data is not lost. 2: the performance is between 0 and 1. The guarantee is that if the operating system doesn’t hang, the database hangs, the persistence of the transaction is guaranteed, but if the operating system also hangs, the data will be lost. (Picking innodb_flush_LOG_AT_trx_COMMIT based on your business scenario and server configuration can also improve performance.)

conclusion

• Redo log is used to ensure InnoDB persistence.
• The redo log is stored in a disk file, but is passedWrite in sequence by groupTo improve disk I/O efficiency
• The redo log buffer flushed to disk more frequently. Can be achieved byinnodb_flush_log_at_trx_commitControls whether the transaction commit is flushed.
• Because the redo log has limited space, it checkpoint after checkpoint to mark the space that can be overwritten.