“This is the 25th day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”
Author: Tangyuan
Personal blog: Javalover.cc
preface
The redo log and bin log are both used to store updates, but they store updates in different ways.
The redo log stores the changes made to the redo log. For example, name=”javalover” was changed to name=”admin”.
The bin log stores not only the changes made, but also the original logic of the changes. For example, update t set name=admin where ID =1 stores the statement and the latest value of name=”admin”.
Here we introduce the working process and differences between the two
directory
- redo log
- bin log
- change buffer
- Log execution flow
- Difference between redo log and bin log
The body of the
1. redo log
The redo log is a storage engine layer. It is unique to InnoDB and is not supported by MyISAM.
Space size:
The size of the redo log is fixed. For example, if you assigned 4 redo logs to 1 GB each, the fixed size of the redo log is 4 GB.
When the redo log is full, a merge operation is performed. That is, records in the redo log are merged to the database disk.
This also introduces the concept of WAL in passing;
WAL mechanism:
Full name is Write Ahead Logging, which roughly means logging first, then disk writing, in order to improve performance;
If every update operation is written to disk, data pages need to be fetched from disk, then updated, and finally written to disk.
As a result, the random I/O cost of the disk is high;
WAL records update operations in the redo log and then merges them to disk when the system is idle. The merges are performed sequentially and the DISK I/O cost is low.
Write_point and check_point:
Write_point point and check_point erase point, can be understood as two Pointers, write_point is responsible for the location of the record to be written, and check_point is responsible for the location of the record to be erased.
Think of these two dots as a pencil and an eraser;
At the beginning, both Pointers refer to the starting point of the redo log as follows:
Write_point moves forward one time while check_point does not move:
When the record is full and cannot be continued, check_point moves forward, merges the record into the database, and clears that part of the record.
crash-safe:
Crash-safe means that if the MySQL service restarts unexpectedly, the previously submitted records will not be lost.
Crash-safe is implemented based on the redo log. The redo log is unique to InnoDB, so most of the time we recommend InnoDB because it is more secure.
2. bin log
Bin log Archive log, which belongs to the Service layer and is independent of the storage engine.
In other words, bin Log supports both InnoDB and MyISAM.
Why do we have a bin log when we have a redo log?
There was a bin log and then a redo log;
At the beginning, the MySQL storage engine was MyISAM, and MyISAM only supported bin log, that is, archived log, which did not have crash-safe capability.
Then came the InnoDB engine, which supported redo logs and crash-safe capabilities.
It seems that the bin log is useless, so why doesn’t MySQL just cancel the bin log?
Because some MySQL still use MyISAM engine, if you disable bin log, they will not even the most basic logging;
3. change buffer
In the last article, we introduced the change buffer. During the update operation, the update record is stored in the Change Buffer.
What does change buffer have to do with redo logs?
First of all, let’s say the same thing: in fact, their purpose is the same, in order to reduce disk IO operations;
Secondly, the differences:
- Update: Updates are written to the change_buffer and then synchronized to the redo log.
- Read operation: If data is read in change Buffer, the dirty pages in Changge buffer need to be flushed to disk before reading. If no data is read from the change buffer, the redo log is continued.
- Disk IO: Change buffer reduces the number of random disk reads and redo log reduces the number of random disk writes. If there is no change buffer, each update operation will read disks randomly, resulting in low efficiency.
Write to the change buffer and redo log.
4. Log execution process
The following statements are used as an example: Redo log and bin log
update t set age=10 where id=1;
Copy the code- If the page where the row of data is located is in memory, it will be returned directly. If not, the corresponding data page needs to be read from disk and then returned;
- The executor took the data returned by the engine, set the age to 10, updated it to the data page in memory, and saved the log to the redo log. The redo log was in the prepare state.
- Next, the operation records are stored in the bin log;
- When the executor commits the transaction, the redo log becomes COMMIT and the update is complete.
There is a two-phase commit concept involved;
Two-phase commit:
For redo logs, you don’t write once, you write twice;
Set it to prepare for the first time, and wait for the bin log to be written.
The transaction is not committed until the redo log is successfully written. The engine sets the redo log to commit.
The purpose of this is to ensure transaction consistency, i.e. redo log and bin log have consistent update records;
5. Differences between redo log and bin log
Basically all of them are covered above. Here is a table to illustrate them clearly:
| redo log | bin log | |
|---|---|---|
| The storage engine | InnoDB | InnoDB, MyISAM |
| MySQL architecture layers | Storage engine layer | Service Service layer |
| storage | Physical logs that store changes made | Logical logs, which store the logical statements and results of changes |
| Space occupied | The size is fixed. Clear some records when the data is full | Append write |
conclusion
The redo log is unique to InnoDB and supports crash-safe.
Bin log belongs to the Service layer and is supported by all engines. The size of bin log is not fixed and can be appended.
The change buffer and redo log complement each other: updated data is written to the change buffer before the redo log. Redo log: Change buffer; redo log: change buffer