MySQL undo log and redo log
In the database system, there are both data files and log files. Logs also contain cached Log buffer and disk file log files in the memory. This document describes the files that store logs.
There are two types of log files in MySQL: undo logs and redo logs. 1 undo1.1 what is an undo log used to store the values before the data modification is modified. Assume that the row data with id = 2 in the dashboard is modified, change Name = 'B' to Name = 'b2', then the undo log will be used to store the Name = 'B' record. If this modification causes an exception, you can use undo logs for rollback to ensure transaction consistency. The data change operation mainly comes from insert update delete. the undo log is divided into two types: INSERT_UNDO (INSERT operation), which records the unique key values inserted; one is UPDATE_UNDO (including UPDATE and DELETE operations), which records the modified unique key value and the old column Record.
1.2 undo parameters MySQL undo-related parameter settings include:
Mysql> show global variables like '% undo % ';
+ -------------------------- + ------------ +
| Variable_name | Value |
+ -------------------------- + ------------ +
| Innodb_max_undo_log_size | 1, 1073741824 |
| Innodb_undo_directory |./|
| Innodb_undo_log_truncate | OFF |
| Innodb_undo_logs | 128 |
| Innodb_undo_tablespaces | 3 |
+ -------------------------- + ------------ +
Mysql> show global variables like '% truncate % ';
+ -------------------------------------- + ------- +
| Variable_name | Value |
+ -------------------------------------- + ------- +
| Innodb_purge_rseg_truncate_frequency | 128 |
| Innodb_undo_log_truncate | OFF |
+ -------------------------------------- + ------- +
Innodb_max_undo_log_size
Control the maximum size of the undo tablespace file. When innodb_undo_log_truncate is started, the undo tablespace will attempt truncate only when it exceeds the innodb_max_undo_log_size threshold. The default value is 1 GB, And the size after truncate is 10 MB.
Set the number of undo independent tablespaces in the range of 0-128. The default value is 0, 0 indicates that the independent undo tablespace is not enabled and the undo logs are stored in the ibdata file. This parameter can only be specified when the MySQL instance is initially initialized. If the instance has been created, this parameter cannot be changed. If the file is configured in the database. if the number of innodb_undo_tablespaces specified in cnf is greater than the number specified during instance creation, the system fails to start and prompts that this parameter is set incorrectly. If this parameter is set to n (n> 0), n undo files (undo001, undo002 ...... undo n), the default size of each file is 10 MB. when do I need to set this parameter? When the DB write pressure is high, you can set an independent UNDO tablespace to separate the undo log from the ibdata file and specify the innodb_undo_directory directory for storage, which can be determined on the high-speed disk, accelerate the read/write performance of the undo log.
The purge thread of InnoDB enables or disables the parameter values of innodb_undo_log_truncate, innodb_max_undo_log_size, and the frequency of truncate to reclaim space and reinitialize the undo file. This parameter takes effect only when two independent tablespaces have been set and the number of independent tablespaces is greater than or equal. In the process of truncate undo log file, the purge thread needs to check whether there are any active transactions in the file. If not, mark the undo log file as unallocable. At this time, undo logs are recorded on other files. Therefore, at least two independent tablespace files are required to perform the truncate operation, an independent file undo _ <space_id> _ trunc is created. log, record that an undo log file is currently being truncate, and then initialize the undo log file to 10 M. After the operation is completed, delete the undo _ <space_id> _ trunc that indicates the truncate action. log File. This file ensures that even if the database service is restarted after a fault occurs during the truncate process, the Service will continue to perform the truncate operation after the file is deleted, identifies the undo log file to be allocable.
- Innodb_purge_rseg_truncate_frequency
Used to control the frequency of purge rollback segments. The default value is 128. If n is set, it indicates that when the Innodb Purge operation's coordinating thread purge transaction is 128 times, a History purge is triggered to check whether the current undo log tablespace status triggers truncate. 1.3 undo space management if you need to set an independent tablespace, you must specify the number of independent tablespaces when initializing the database instance. UNDO consists of multiple Rollback segments, that is, Rollback segment. A total of 128 segments are stored in the ibdata system tablespace, from resg slot0-resg slot127 to each resg slot, that is, each rollback segment is composed of 1024 undo segments. Rollback segment is allocated as follows:
- Slot 0, reserved for system tablespace;
- Slot 1-32, reserved for temporary tablespace. The temporary tablespace will be rebuilt each time the database is restarted;
- Slot33-127, if there is an independent tablespace, it is reserved for the UNDO independent tablespace; if not, it is reserved for the system tablespace;
Except 32 rollback segments for temporary table transactions, the remaining 128-32 = 96 rollback segments allow 96*1024 concurrent transaction operations, each transaction occupies an undo segment slot. Note that if there is a temporary table transaction in the transaction, the undo segment slot in the temporary tablespace will also occupy an undo segment slot, it occupies two undo segment slots. If the error log contains:
Cannot find a free slot for an undo log。It indicates that too many concurrent transactions, so you need to consider whether to divert the business. Rollback segment uses the round-robin scheduling method for allocation. If an independent tablespace is set, the undo segment in the system tablespace rollback segment is not used, instead, the independent tablespace is used. If the review segment is being Truncate, It is not allocated. 2 redo2.1 what is redo? When the database modifies the data, it needs to read the data page from the disk to the buffer pool and then modify it in the buffer pool, in this case, the data page in the buffer pool is inconsistent with the data page content on the disk. The data page in the buffer pool is called dirty page dirty data, if an abnormal DB service is restarted at this time, the data is not in the memory and is not synchronized to the disk file (note that synchronizing data to the disk file is a random IO ), that is, data will be lost. If there is a file at this time, when the data page in the buffer pool changes, the corresponding modification records will be recorded in this file (note, record logs are sequential IO). When the Database Service recovers from crash, you can re-apply the log to the disk file based on the recorded content of the file to ensure data consistency. This file is the redo log, which is used to record the modified records and sequential records of data. It brings these benefits:
- When the dirty page in the buffer pool has not been refreshed to the disk, a crash occurs. After the service is started, you can find the redo log to refresh the disk file;
- The data in the buffer pool is flushed directly to the disk file, which is a random IO with poor efficiency. However, recording the data in the buffer pool to the redo log is a sequential IO, it can speed up transaction commit;
Assume that you modify the row data with id = 2 in the guid table and change Name = 'B' to Name = 'b2'. Then, the redo log is used to store records with Name = 'b2, if an exception occurs when the modification is flushed to the disk file, you can use redo log to redo the operation to ensure transaction persistence.
Note the difference between redo log and binary log. redo log is generated at the storage engine layer, while binary log is generated at the database layer. Assume that a large transaction inserts 0.1 million rows of records into the dashboard. During this process, records are continuously recorded in the redo log sequence, while binary log does not record the transaction and knows that the transaction is committed, will be written to the binary log file once. There are three record formats for binary log: row, statement and mixed. Different record formats are different. 2.2 redo Parameters
- Innodb_log_files_in_group
Number of redo log files, such as ib_logfile0, iblogfile1.. iblogfilen. The default value is 2. The maximum value is 100.
File size. The default value is 48 M and the maximum value is 512 GB. Note that the maximum value refers to the sum of the entire redo log series files, that is, the maximum value (innodb_log_files_in_group * innodb_log_file_size) cannot be greater than 512 GB.
- Innodb_log_group_home_dir
File Storage path
Redo Log cache. The default value is 8 Mb. The value range is 1-8 Mb. Delay transaction logs written to the disk, put the redo log in the buffer zone, and then flush the logs from the buffer to the disk based on the innodb_flush_log_at_trx_commit parameter settings.
- Innodb_flush_log_at_trx_commit
- Innodb_flush_log_at_trx_commit = 1. Each time the commit writes the redo log from the redo log buffer to the system, and fsync refreshes it to the disk file.
- Innodb_flush_log_at_trx_commit = 2. MySQL writes logs from the redo log buffer to the system when each transaction is committed, but only writes the logs to the file system buffer, and fsync from inside the system to the disk file. If the database instance crash, the redo log will not be lost, but if the server crash, because the file system buffer is too late to fsync to the disk file, this part of data will be lost.
- Innodb_flush_log_at_trx_commit = 0. During the transaction occurrence process, logs are always motivated in the redo log buffer, which is the same as other settings, but no redo write operation is generated when the transaction is committed, instead, MySQL operates once per second internally and writes data to the system from the redo log buffer. If a crash occurs, the transaction modification operation within 1 s is lost.
- Note: Due to a process scheduling policy issue, this "flush (flush to disk) operation per second" does not guarantee 100% "per second ".
2.3 redo Space
ib_logfile[number]The Redo log is written to the file in sequence. When the file is full, it is traced back to the first file for overwrite. (However, when performing redo checkpoint, it will also update the header checkpoint mark of the first log file, so strictly speaking, it is not sequential writing ). In fact, redo log consists of two parts: redo log buffer and redo log file. In the buffer pool, record data changes to the redo log buffer. In the following cases, redo log is flushed to the redo log file:
- Insufficient Redo log buffer space
- Transaction commit (dependent on innodb_flush_log_at_trx_commit parameter settings)
- Background thread
- Checkpoint
- Instance shutdown
- Binlog Switching
3. How does undo and redo record the simplified process of transaction 3.1 Undo + Redo transactions assume that there are two data types, A and B, with the values 1 and 2 respectively, starting A transaction. The operation content of the transaction is: modify 1 to 3 and 2 to 4. The actual record is as follows (simplified):. the transaction starts.
B. Record A = 1 to undo log.
C. modify A = 3.
D. Record A = 3 to redo log.
E. Record B = 2 to undo log.
F. Modify B = 4.
G. Record B = 4 to redo log.
H. Write the redo log to the disk. I. The design of the 3.2 IO impact on Undo + Redo of transaction commit mainly considers improving IO performance and increasing database throughput. It can be seen that B d e g h is a new operation, but B D E G is buffered into the buffer zone, and only G is an IO operation added, to ensure good IO performance of Redo logs, the Redo Log Design of InnoDB has the following features:. try to keep the Redo Log stored in a continuous space. Therefore, when the system starts for the first time, the space of the log file is fully allocated. Redo logs are recorded in sequential append mode, and performance is improved through sequential IO. B. Write logs in batches. Logs are not directly written to files, but are first written to redo log buffer. when the log needs to be refreshed to the disk (such as transaction commit), many logs are written to the disk together. c. concurrent transactions share the storage space of the Redo Log. Their Redo logs are recorded in an alternate order of statement execution,
To reduce the space occupied by logs. For example, the record content in the Redo Log may be as follows:
Record 1: <trx1, insert...>
Record 2: <trx2, update...>
Record 3: <trx1, delete…>
Record 4: <trx3, update...>
Record 5: <trx2, insert...>
D. Because of C, when a transaction writes the Redo Log to the disk, it also writes the logs of other uncommitted transactions to the disk. E. Only sequential append operations are performed on the Redo Log. When a transaction needs to be rolled back, its Redo Log records will not be deleted from the Redo Log. 3.3 recovery previously mentioned that uncommitted transactions and rolled back transactions also record the Redo Log. Therefore, special processing is required for these transactions during recovery. There are two different recovery policies: A. Only committed transactions are redone during restoration.
B. During recovery, redo all the transactions, including uncommitted transactions and rolled back transactions. Then, roll back the uncommitted transactions through the Undo Log. The InnoDB Storage engine of MySQL uses the B policy. The recovery mechanism in the InnoDB Storage engine has several features: A. When Redo logs are redone, transactions are not concerned. No BEGIN, COMMIT, or ROLLBACK actions are performed during restoration. It does not care which transaction each log belongs. Although transaction ID and other transaction-related content are recorded in the Redo Log, the content is only part of the data to be operated. B. To use the B policy, the Undo Log must be persistent and the corresponding Undo Log must be written to the disk before the Redo Log is written. This association between Undo and Redo logs makes persistence complex. To reduce the complexity, InnoDB regards the Undo Log as data, so the operations to record the Undo Log will also be recorded in the redo log. In this way, the undo log can be cached like the data, instead of being written to the disk before the redo log. The Redo Log containing the Undo Log operation looks like this:
Record 1: <trx1, Undo log insert <undo_insert…>
Record 2: <trx1, insert...>
Record 3: <trx2, Undo log insert <undo_update...>
Record 4: <trx2, update...>
Record 5: <trx3, Undo log insert <undo_delete…>
Record 6: <trx3, delete…>
C. At this point, there is another problem that has not been clarified. Since Redo is not transactional, wouldn't the transaction be rolled back again?
This is indeed the case. At the same time, Innodb also records the operations during transaction rollback to the redo log. The rollback operation is essentially
Modify the data. Therefore, operations on the data during rollback are also recorded in the Redo Log.
The Redo Log of a rolled back transaction looks like this:
Record 1: <trx1, Undo log insert <undo_insert…>
Record 2: <trx1, insert A…>
Record 3: <trx1, Undo log insert <undo_update...>
Record 4: <trx1, update B...>
Record 5: <trx1, Undo log insert <undo_delete…>
Record 6: <trx1, delete C…>
RECORD 7: <trx1, insert C>
Record 8: <trx1, update B to old value> record 9: <trx1, delete A> when A rolled back transaction is restored, redo is performed before undo, therefore, data consistency will not be damaged.
