Reading directory (content)
- 1 undo
- What's 1.1 undo?
- 1.2 Undo Parameter
- 1.3 Undo Space management
- 2 Redo
- What's 2.1 redo?
- 2.2 Redo Parameters
- 2.3 Redo Space Management
- 3 Undo and redo how to record transactions
- 3.1 Undo + Redo Transaction Simplification process
- 3.2 IO Impact
- 3.3 Recovery
In the database system, both the file that holds the data and the file that holds the log. Logs are also cached in memory log buffer, there are also disk files log file, this article mainly describes the files stored in the log. There are two types of log files in MySQL that are often discussed: The Undo log and the redo log. Top (go to top)
1 undo
What's 1.1 undo?The Undo log is used to store the value before the data modification is modified, assuming that the id=2 row data in the TBA table is modified and name= ' B ' is modified to name = ' B2 ', then the undo log will be used to hold the record for name= ' B ', if the modification is abnormal, You can use the undo log to implement rollback operations to ensure transactional consistency. The change to the data operation, mainly from insert UPDATE DELETE, and UNDO log is divided into two types, one is Insert_undo (insert operation), record the inserted unique key value; one is Update_ UNDO, which contains the update and delete operations, records the modified unique key value and the old column record.
1.2 undo ParameterMySQL has these parameters set for undo:
1 mysql> show global variables like '%undo% '; 2 +--------------------------+------------+ 3 | Variable_name | Value | 4 +--------------------------+------------+ 5 | innodb_max_undo_log_size | 1073741824 | 6 | innodb_undo_direc Tory |./ | 7 | innodb_undo_log_truncate | OFF | 8 | innodb_undo_logs | 9 | innodb_undo_tablespaces | 3 |10 +--------------------------+ ------------+11 mysql> show global variables like '%truncate% '; +--------------------------------------+- ------+14 | Variable_name | Value |15 +--------------------------------------+-------+16 | innodb_purge_rseg_truncate_frequency | |17 | innodb_undo_log_truncate | OFF |18 +--------------------------------------+-------+
Controls the size of the maximum undo Tablespace file, and when Innodb_undo_log_truncate is activated, the undo Tablespace tries to truncate when it exceeds the innodb_max_undo_log_size threshold. The default size for this value is 1g,truncate, which defaults to 10M.
Set the number of undo stand-alone tables, the range is 0-128, the default is 0,0 to indicate that the standalone undo table space is not turned on, and the undo log is stored in the Ibdata file. This parameter can only be specified at the beginning of the initialization of the MySQL instance, if the instance has been created, this parameter cannot be changed if the number specified in the Innodb_undo_tablespaces in the database configuration file is greater than the number specified at the time the instance was created, the failure will start. Tip This parameter is set incorrectly. If this parameter is set to N (n>0), then n undo Files (undo001,undo002 ... undo N) are created in the Undo directory, and each file has a default size of 10M.
when do I need to set this parameter? When the db write pressure is large, you can set the independent undo table Space, remove the undo log from the Ibdata file, specify the Innodb_undo_directory directory storage, can be developed to high-speed disk, speed up the read and write performance of undo log.
InnoDB purge thread, space reclamation and undo file based on Innodb_undo_log_truncate setting on or off, innodb_max_undo_log_size parameter values, and truncate frequency Re-initialization.
This parameter takes effect if a separate tablespace is set and the number of independent table spaces is greater than or equal to 2. Purge thread in the process of truncate undo log file, you need to check if there are any active transactions on the file, and if not, you need to mark the undo log file as non-assignable, and at this time, undo log will be recorded on the other file. So at least 2 separate tablespace files are required for truncate operation, after labeling is not assignable, a separate file Undo_<space_id>_trunc.log is created, and the record is now truncate an undo log file. The Undo log file is then initialized to 10M, and the Undo_<space_id>_trunc.log file representing the truncate action is deleted after the operation is completed. This file ensures that even if the database service fails to restart during the truncate process, the service discovers the file and continues to complete the truncate operation after the deletion of the file, identifying the Undo log file to be allocated.
- Innodb_purge_rseg_truncate_frequency
Used to control the frequency of the purge rollback segment, which defaults to 128. Assuming that it is set to N, a history purge is triggered when the reconcile thread of the InnoDB purge operation purge the transaction 128 times, checking whether the current undo log tablespace state will trigger truncate.
1.3 Undo Space managementIf you need to set up a stand-alone table space, you need to specify the number of stand-alone tablespaces when initializing your DB instance. Undo internally consists of multiple rollback segments, namely Rollback segment, a total of 128, saved in the Ibdata system table space, from RESG SLOT0-RESG slot127, each RESG slot, that is, each rollback segment, The interior consists of 1024 undo segment. The rollback segment (rollback segment) is assigned as follows:
- Slot 0, reserved for system table space;
- Slot 1-32, reserved to the temporary table space, each time the database restarts, the temporary table space will be rebuilt;
- slot33-127, if there is a separate table space, it is reserved to the Undo stand-alone table space, if not, reserved for the system tablespace;
The rollback segment removes 32 of the remaining 128-32=96 rollback segments that are provided to the temporary table transaction, performs 96*1024 concurrent transaction operations, consumes an undo segment slot per transaction, and notes that if there is a temporary table transaction in the transaction, the undo in the temporary table space The segment slot occupies an undo segment slot, which occupies 2 undo segment slots. If the error log has:
Cannot find a free slot for an undo log。This means that there are too many concurrent transactions and you need to consider whether you want to divert the business. The rollback segment (rollback segment) is allocated using a polling schedule, and if a stand-alone table space is set, then the undo segment in the System tablespace rollback segment is not used, and the stand-alone tablespace is used, and is not assigned if the review segment is truncate operation. Top (go to top)
2 Redo
What's 2.1 redo ?When the database modifies the data, the data page needs to be read from disk to buffer pool and then modified in buffer pool, then the data page in buffer pool is inconsistent with the contents of the data page on disk, which is called buffer The data page of the pool is dirty page dirty data, if this time an abnormal DB service restart, then the data is not in memory, and is not synchronized to the disk file (note that the synchronization to the disk file is a random io), that is, data loss occurs, if this time, to be able to have a file, When the data page change in the buffer pool is finished, the corresponding modification record is recorded to this file (note that the log is sequential IO), then when the DB service is crash, when the DB is restored, it can be re-applied to the disk file according to the contents of the file. Data remains consistent. This file is redo log, used to record the data after the modified records, Sequential records. It can bring these benefits:
- When the dirty page in the buffer pool has not been flushed to the disk, crash occurs, after the service is started, the records that need to be refreshed to the disk file can be found by redo log;
- Data in buffer pool is directly flush to disk file, is a random io, inefficient, and the data in the buffer pool to redo log, is a sequential IO, can improve the speed of transaction submission;
Assuming that you modify the row data in the TBA table and change the name= ' B ' to name = ' B2 ', then the redo log will be used to store the name= ' B2 ' record, and if the modification occurs when flush to the disk file, you can use the Redo log to do the redo operation, id=2. Ensure the persistence of the transaction.
Note Here the difference between redo log and binary log, redo log is generated by the storage engine layer, and binary log is generated by the database layer. Suppose a large transaction, to the TBA do 100,000 lines of record insertion, in this process, has been continuously to the redo log sequence records, and binary log is not recorded, know the transaction commits, will be written to the binary log file one time. Binary log has 3 record formats: Row,statement and mixed, different format records.
2.2
Redo
Parameters
- Innodb_log_files_in_group
Redo log file number, naming methods such as: Ib_logfile0,iblogfile1 ... iblogfilen. Default of 2, maximum 100.
File size, the default value is 48M, the maximum value is 512G, note that the maximum value refers to the sum of the entire Redo log series file, i.e. (Innodb_log_files_in_group * innodb_log_file_size) cannot be greater than the maximum value of 512G.
- Innodb_log_group_home_dir
File storage Path
Redo Log buffer, default 8M, can be set 1-8m. Defer the transaction log to the disk, place the redo log in the buffer, and then flush the log from buffer to disk, based on the settings of the Innodb_flush_log_at_trx_commit parameter.
- Innodb_flush_log_at_trx_commit
- Innodb_flush_log_at_trx_commit=1, each commit writes redo log from redo log buffer to system, and Fsync flushes to the disk file.
- innodb_flush_log_at_trx_commit=2, MySQL writes logs from redo log buffer to system at each transaction commit, but writes to file system buffer only, Fsync to disk files from within the system. If the DB instance is crash, redo log will not be lost, but if the server crash, this part of the data will be lost because the file system buffer is too late to Fsync to the disk files.
- Innodb_flush_log_at_trx_commit=0, the transaction occurs, the log has been stimulated in the redo log buffer, as in the other settings, but when the transaction commits, does not produce redo write operations, but MySQL internal operations per second, From redo log buffer, write the data to the system. If crash occurs, the transaction modification operation within 1s is lost.
- Note: This "flush (brush to disk) operation per second" is not guaranteed to be 100% "per second" due to a process scheduling policy issue.
2.3
Redo
Space ManagementRedo log file to
ib_logfile[number]Named, the Redo log is written sequentially to the file file, and when it is full, it goes back to the first file, overwriting the write. (But in the case of Redo checkpoint, the first log file's header checkpoint tag is also updated, so it is strictly not a sequential write). In fact, redo log consists of two parts: redo log buffer and redo log file. The data modifications are recorded in buffer pool to redo log buffer, and then the redo log is brushed down to the redo log file as follows:
- Redo log buffer Not enough space
- Transaction commit (dependent on innodb_flush_log_at_trx_commit parameter setting)
- Background thread
- Do checkpoint
- Example shutdown
- Binlog switching
Top (go to top)
3 Undo and redo how to record transactionsThis part of the content recommended reading this series of blogs, written well: http://www.zhdba.com/mysqlops/2012/04/06/innodb-log1/The following sections from this blog, thanks to the author summary, in a very good understanding.
3.1 Undo + redo Transaction Simplification processSuppose there is a, b two data, the values are 1, 2, start a transaction, the operation of the transaction content is: 1 is modified to 3, 2 is modified to 4, then the actual record is as follows (Simplified): A. Transaction begins.
B. Record a=1 to undo log.
C. Modify the 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 redo log to disk. I. Transaction Submission
3.2 io ImpactThe design of Undo + redo is primarily concerned with improving IO performance and increasing database throughput. As can be seen, b d e G H, are new operations, but B d e G is buffered to the buffer zone, only G is increased IO operation, in order to ensure that Redo log can have better IO performance, InnoDB's Redo log design has the following features: A. Try to keep Redo Log is stored in a contiguous space. Therefore, the space of the log file is fully allocated when the system is first started. Record redo log in sequential append mode to improve performance through sequential IO. B. Bulk write logs. Instead of writing directly to the file, the log writes redo log buffer. When you need to flush logs to disk (such as transaction commits), write many logs together to disk. C. Concurrent transaction sharing redo log storage space, their redo log in the order of execution of the statement, sequentially alternating records together,
To reduce the space occupied by the log. For example, the record in Redo log might be something like this:
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 redo log to disk, the log of other uncommitted transactions is also written to disk. E. Redo logs are only sequentially appended, and when a transaction needs to be rolled back, its Redo log record is not removed from the Redo log.
3.3 RecoveryThe redo log is also logged when the uncommitted transactions and rolled-back transactions are noted earlier, so these transactions are handled in a special process when recovering. There are 2 different recovery strategies: a. When recovering, only the transactions that have already been committed are re-made.
B. When recovering, redo all transactions include uncommitted transactions and rolled back transactions. The uncommitted transactions are then rolled back through the undo log.
the MySQL database InnoDB storage engine uses the B policy,The recovery mechanism in the INNODB storage engine has several features: A. When you redo the redo log, and
do not care about transactional。 When recovering, there is no begin and no commit,rollback behavior. Nor do you care which transaction each log is. Although transaction-related content such as transaction ID is credited to redo Log, the content is only considered part of the data being manipulated. B. Using the B policy, you must persist the undo log, and you must write the corresponding undo log to disk before writing redo log. This association of undo and redo log makes persistence complicated. To reduce complexity, innodb the undo log as data, so the operation to record the Undo log is also recorded in the Redo log. This way, undo log can be cached like data without writing to the disk before redo log. The redo log that contains 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. Here, there is one more question that is not clear. Since redo is not transactional, would it not be re-executing the transaction that was rolled back?
That's true. At the same time, InnoDB also records the operation of the transaction when it is rolled back into the redo log. Rollback operations are essentially
The data is modified so that the operation of the data during rollback is also recorded in the Redo log.
A rollback of the transaction's redo Log, which 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 BTo old value> record 9: <trx1,
Delete a>A transaction that has been rolled back is redo and then undo, so it does not break the consistency of the data. Reference article: http://mysql.taobao.org/monthly/2016/07/01/https://yq.aliyun.com/articles/50747http://www.zhdba.com/ mysqlops/2012/04/06/innodb-log1/http://mysql.taobao.org/monthly/2015/04/01/
Tell me what redo log Undo log is doing in MySQL.
