MySQL’s RedoLog and BinLog

Details of MySQL BinLog

Photo by Workperch on Unsplash

RedoLog

We know that when MySQL writes data, it may be appending data or locating an already existing piece of data to modify.

For random reads and writes from disk, this is slow and cannot meet the scenario of high IO operations.

To improve write efficiency, we can write to memory first and then write to disk when free.

But this creates a problem: the data in memory is not persistent, so if there is a power failure, the data will be lost?

To solve the data loss problem, MySQL has introduced redo logs to solve this problem.

This is called WAL (Write-Ahead Logging), a common practice to improve IO efficiency against non-in-memory databases.

This makes it possible to rely on logging to recover data in the event of a crash, ensuring data persistence.

Write-Ahead Logging

An efficient logging algorithm in databases. For non-memory databases, disk I/O operations are a major bottleneck in database efficiency.

With the same amount of data, a database system using WAL logging has only about half the disk write operations of traditional rollback logging at transaction commit time, greatly improving the efficiency of database disk I/O operations, thus improving database performance.

Advantages of WAL

• Reads and writes can be executed completely concurrently without blocking each other (but writes are still not concurrent with each other).
• WAL has better performance in most cases (because there is no need to write two files for each write).
• Disk I/O behavior is more predictable.
• Fewer fsync() operations are used, reducing system fragility issues.

RedoLog implementation

Let’s see how MySQL really does it.

When a record needs to be updated, the InnoDB engine writes the record redo log first and updates the memory. At the appropriate time, such as when the disk is free, the data in the redo log is flushed to the disk.