In-depth analysis of MySQL Innodb Log Mechanism

1.1.Log & Checkpoint

InnodbThe transaction log of isRedo log, AbbreviationLog,Save in Log FileIb_logfile *. Innodb also has another log Undo log, but the Undo log is stored in the shared tablespace (ibdata * file ).

 

BecauseLogAndCheckpointTherefore, these two parts are combined for analysis.

Glossary:LSN, Log serial number,InnodbThe log serial number of is64Integer.

 

1.1.1. Write Mechanism 1.1.1.1. LogWrite 

LSNIn fact, the offset corresponding to the log file, the newLSN= OldLSN +The size of the log to be written. Example:

LSN=1G, The total log file size is600 M, This write512Bytes, the actual write operation is:

LOffset: BecauseLSNThe value is much larger than the log file size. Therefore, the offset is400 M;

LWrite log: locate the offset400 MLocation, write512Byte log content, the content of the next transactionLSNYes1000000512;

 

1.1.1.2. CheckpointWrite 

InnodbImplementedFuzzy CheckpointEvery time you get the oldest dirty page, make sure that the dirty page correspondsLSNPreviousLSNHave been written to the log file, and thenLSNAsCheckpointPoint to log file, which means"ThisLSNPreviousLSNThe corresponding log and data have been written to the disk file.". When restoring data files,InnodbScan log files.LSNLessCheckpointCorrespondingLSNAnd the recovery is completed.

CheckpointThe write location is at a fixed offset at the beginning of the log file, that is, each writeCheckpointOverwrite the previousCheckpointInformation.

 

 

1.1.2. Management Mechanism 

BecauseCheckpointIt is closely related to logs.CheckpointThe detailed implementation mechanism is as follows:

 

 

As shown in,InnodbA transaction log4Stages:

LCreation phase: the transaction creates a log;

LLog fl: the log files written into the disk;

LData disk flushing: the dirty pages of logs written to the data files on the disk;

LWriteCKP: Logs are treatedCheckpointWrite log files;

 

 

Corresponding to this4Phase, the system records4Log-related information. www.bkjia.com is used for other processing:

LLog sequence number(LSN1): Current SystemLSNMaximum value, new transaction logLSNThe (LSN1 +New log size );

LLog flushed up(LSN2):LSN;

LOldest modified data log(LSN3): Corresponding to the currently oldest dirty page dataLSN, WriteCheckpointDirectlyLSNWrite to log files;

LLast checkpoint(LSN4): The data has been written.CheckpointOfLSN;

 

 

For the system4ItemsLSNIs decreasing, namely:LSN1> = LSN2> = LSN3> = LSN4.

 

For exampleShow innodb status/GCommand to view,Oldest modified data logNot Displayed ):

1 2 Next Page