Click “Technical blog post of Brother Flathead” above and select “Follow the public account”
Technical articles first time delivery!
Redo logging is used for MySQL persistence. It is important to note that redo logging is unique to the InnoDB engine.
Why does the InnoDB engine introduce redo logging as an intermediate layer to ensure MySQL persistence rather than persisting directly to disk? Let’s take a look at a story mentioned in MySQL Practice 45.
In the kong Yiji article, the shopkeeper of a hotel has a powder board, which is specially used to keep track of customers’ credit records. If there are not many people on credit, he can write down the names of his customers and the accounts on the board. But if there are too many people on credit, there will always be a time when the powder board can’t keep track of it. At this time, the shopkeeper must have a special account book to record credit.
If someone wants credit or to pay the bill, the shopkeeper usually has two ways:
- One way is to open the books and add or subtract the credit.
- Another method is to write down the account on the powder board first, and then turn the account book out after closing time.
In business flourishing counter is very busy, shopkeeper will choose latter certainly, because former operation is too troublesome really. First, you need to find a record of this guy’s total credit. If you think about it, there are dozens of pages thick and dense. If you want to find the name, the shopkeeper may have to put on reading glasses and slowly search for it. After finding it, he will take out an abacus to calculate and finally write the result back into the ledger.
This whole process is difficult to think about. By contrast, it is convenient to write down on the powder board first. Think about it, if the shopkeeper did not have the help of powder board, every bookkeeping have to turn over the books, efficiency is not unbearable?
If every update operation needs to be written to disk, then disk also needs to find the corresponding record, and then update, the whole process IO costs, search costs are very high.
To solve this problem, the MySQL designers have used the same idea as the hotel pantry to improve update efficiency. The redo log is used as the hotel pantry for temporary storage and serves as a transit point.
In particular, when a record needs to be updated, the InnoDB engine writes the record to the redo log and updates the memory. The update is complete. At the same time, the InnoDB engine will update the operation record to disk when appropriate, and this update is usually done when the system is idle, which is like the shopkeeper after closing time.
From the above story, you can understand why redo logs are introduced. With a general understanding of redo logs, let’s introduce redo logs formally, starting with the structure of redo logs:
Redo logs log physical changes on each page. Therefore, redo logs are generally small and do not store much information.
- Type: indicates the redo log type.
- Space ID: indicates the ID of a tablespace.
- Page number: indicates the page number.
- Data: indicates the contents of the redo log.
Redo log is not so simple, it is very complicated, but we don’t need skilled and magical craftsmanship to it, because it does not do for us, as long as we remember the redo log in the execution of the transaction to the database record all changes, in a system crash after restart restore firm to do any modification.
The benefits of flushing all modified in-memory pages to disk at transaction commit are as follows, compared to flushing only redo logs generated during the transaction:
- Redo logs take up very little space: the storage space required to store tablespace ids, page numbers, offsets, and values that need to be updated is small
- Redo logs are written to disk sequentially: During a transaction, several redo logs may be written to disk in the order in which each statement is executed, using sequential IO.
How redo logs work
Redo logs are written in a cycle, so InnoDB’s redo log is fixed in size. For example, a group of 4 1GB redo logs can be configured to record 4GB of operations. Start at the beginning, then go back to the beginning at the end of the loop, as shown below:
Write pos is the current position of the record, moving backwards as you write, returning to the beginning of file 0 after the end of file 3. Checkpoint is the current point of erasure, which is also iterated backwards, updating the record to the data file before erasure.
Between write POS and checkpoint is the empty portion of the pink board that can be used to record new operations. If write POS catches up to checkpoint, the “fan board” is full, and no new updates can be performed. You have to stop and erase some records to push checkpoint forward.
This is roughly how redo logs work. Think of it as a chalkboard.
Redo log buffer
Redo logs are not written directly to disk. Instead, they are written to a buffer called the redo log buffer. A large contiguous memory area called the redo log buffer or log buffer is requested from the operating system at startup. This memory space is divided into several consecutive redo log blocks, as shown in the following figure:
The default size of the redo log buffer under MySQL Server 5.7 is 1 MB. We can set the size of the redo log buffer with innodb_log_buffer_size.
Redo logs are written to the log buffer in sequential order, starting with the previous block and moving to the next block when the free space in that block is exhausted.
If you write to the buffer first and then to the disk, you run into a problem that was also encountered with redis AOF persistence: how do you synchronize data between the buffer and disk?
The innodb_flush_log_at_trx_COMMIT parameter is used in the MySQL configuration file to control how data is synchronized between the buffer and disk. There are three options: 0, 1, and 2. A brief description of the differences between the three options:
- 0: indicates that when a transaction is committed, the redo log of the buffer is not written to the disk log file. Instead, it waits for the main thread to refresh every second.
- 1: Write the redo logs of the buffer to disk at transaction commit time.
- 2: Write redo logs to disk asynchronously at commit time. That is, there is no guarantee that the redo log file will be written at commit time.
We will use the default value of 1 to ensure that data is not lost if MySQL restarts unexpectedly.
To summarize, redo logs are unique to InnoDB. With redo logs, InnoDB guarantees that all previously committed records will not be lost in the event of an unexpected database restart.
This article discusses the reasons for introducing redo logs, the structure of redo logs, and the way redo logs are written. Redo logs may not be useful at work, but they may be useful in interviews. Thank you for reading, if you feel that the article has harvest, welcome to like and forward, thank you.
Selected articles of the Past
Why does TCP establish a connection with a three-way handshake, but close a connection with a four-way handshake?
You may know the four features of transactions, but you may not know how they are implemented
In 2020, the job is done
SQL > select * from user
