Detailed description of InnoDB Transaction log (redo log and undo log)

Detailed description of InnoDB Transaction log (redo log and undo log)

To avoid performance problems caused by I/O bottlenecks during data writing to the maximum extent, MySQL adopts a cache mechanism: When the query modifies the data in the database, innoDB first reads the data from the disk to the memory, modifies the data copy in the memory, and persists the modification to the transaction log on the disk (first write the redo log buffer, instead of directly recording the modified data to the hard disk each time. After the transaction log persistence is complete, dirty data in the memory can be slowly flushed back to the disk, it is called Write-Ahead Logging. Transaction logs Use APPEND writing, which improves the performance of sequential io.

To avoid data loss caused by power loss or system faults when dirty data is flushed back to the disk, InnoDB uses the transaction log (redo log) to solve this problem.

[Redo log]

It is used to continue transactions that have been committed but data has not been fully written back to the disk during instance fault recovery.

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

Generally, two or more ib_logfiles are initialized to store redo logs. The number is determined by the innodb_log_files_in_group parameter. The names start from ib_logfile0, fill ib_logfile in sequence, and reuse (in a circular fashion ). If the last ib_logfile is full, and the transaction of all records in the first ib_logfile persists to the disk, the transaction will be cleared and reused.

When the write volume is high, the size of the redo log significantly affects the write performance.

Innodb_log_file_size is used to control the size of ib_logfile. Version 5.5 and earlier versions: The default value is 5 MB, and the maximum value is 4 GB.

The total transaction log size cannot exceed 4 GB. The transaction log is too large and the checkpoint will be reduced. While saving disk io, the large transaction log also means that the database recovers slowly after crash.

For more information about innodb_log_file_size, see show engine innodb status \ G.

Log sequence number-Last checkpoint <(innodb_log_files_in_group * innodb_log_file_size) * 0.75

[Undo log]

The image before data modification is recorded. Stored in ibdata. (The DDL operation modifies the data dictionary and stores the information in ibdata)

It is used to roll back a transaction that has not been committed to the status before the start of the transaction with the help of the undo log when the instance recovers from failure.

An undo log is a collection of undo log records associated with a single transaction. an undo log record contains information about how to undo the latest change by a transaction to a clustered index record. if another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from the undo log records. undo logs exist within undo log segments, which are contained within rollback segments. rollback segments are physically part of the system tablespace. for related information, see Section 14.6, "InnoDB Multi-Versioning ".

The InnoDB crash recovery process consists of several steps :( it refers to MySQL server crash, excluding hardware failure or server system error)

Redo log application
Redo log application is the first step and is already med during initialization, before accepting any connections. if all changes are flushed from the buffer pool to the tablespaces (ibdata * and *. ibd files) at the time of the shutdown or crash, redo log application is skipped. innoDB also skips redo log application if redo log files are missing at startup.

Removing redo logs to speed up recovery is not recommended, even if some data loss is acceptable. Removing redo logs shocould only be considered after a clean shutdown, with innodb_fast_shutdown set to 0 or 1.

Roll back of incomplete transactions
Incomplete transactions are any transactions that were active at the time of crash or fast shutdown. the time it takes to roll back an incomplete transaction can be three or four times the amount of time a transaction is active before it is interrupted, depending on server load.

You cannot cancel transactions that are being rolled back. in extreme cases, when rolling back transactions is expected to take an exceptionally long time, it may be faster to start InnoDB with an innodb_force_recovery setting of 3 or greater. see Section 14.23.2, "Forcing InnoDB Recovery ".

Change buffer merge
Applying changes from the change buffer (part of the system tablespace) to leaf pages of secondary indexes, as the index pages are read to the buffer pool.

Purge
Deleting delete-marked records that are no longer visible to active transactions.

The steps that follow redo log application do not depend on the redo log (other than for logging the writes) and are already med in parallel with normal processing. of these, only rollback of incomplete transactions is special to crash recovery. the insert buffer merge and the purge are running med during normal processing.

After redo log application, InnoDB attempts to accept connections as early as possible, to reduce downtime. as part of crash recovery, InnoDB rolls back transactions that were not committed or in xa prepare state when the server crashed. the rollback is already med by a background thread, executed in parallel with transactions from new connections. until the rollback operation is completed, new connections may encounter locking conflicts with recovered transactions.