MySQL InnoDB log mechanism in-depth analysis

1.1.Log & Checkpoint

InnoDB transaction log refers to Redolog, which is called log , stored in the log file ib_logfile* inside. InnoDB There is another log of undo logs, but the undo log is stored in the shared tablespace (ibdata* file).

Because Log and Checkpoint are closely related, these two parts are analyzed together.

Noun Explanation:LSN, log sequence number,Innodb 's log sequence number is a full-bit integer.

1.1.1.Write mechanism1.1.1.1.Log Write

The LSN actually corresponds to the offset of the log file, and the new lsn= old LSN + log size written. Examples are as follows:

lsn=1G, the log file size of a total of 600M, This write bytes, the actual write operation is:

L offset: Because the LSN value is much larger than the log file size, so the offset is obtained by the method of 400M;

L Write log: Locate offset 400M , write the contents of the byte log, the LSN of the next transaction is 1000000512;

1.1.1.2.Checkpoint Write

InnoDB implements the mechanism of the Fuzzy Checkpoint , each fetch to the oldest dirty page, and then ensure that the LSN of the corresponding LSN of this dirty page has been written to the log file, and then the LSN of this dirty page logging to the log file as a Checkpoint point means " the log and data for the LSN before this LSN have been written to the disk file ". When recovering a data file,Innodb scans the log file, and when the LSN is found to be less than Checkpoint , the recovery is considered complete.

The location where the Checkpoint is written is at a fixed offset at the beginning of the log file, that is, each write Checkpoint overwrites the previous Checkpoint information.

1.1.2.Management mechanism

Because Checkpoint and logs are closely related, the log and Checkpoint are described together, the detailed implementation mechanism is as follows:

As shown, a transaction log forInnodb has undergone a total of 4 stages:

L Create phase: Transaction creates a log;

L Log Brush Disk: Log files written to disk;

L Data Brush Disk: The log corresponding to the dirty page data written to the data file on disk;

L Write CKP: Log is written as Checkpoint log file;

For these 4 stages, the system records 4 log-related information for various other processing uses:

L Log Sequence Number (LSN1): The current system LSN Maximum, and the new transaction log LSN will be generated on this basis (lsn1+ The size of the new log);

L log flushed up to (LSN2): The LSNthat is currently written to the log file;

L oldest modified data log (LSN3): The LSNof the current oldest dirty page data, which is directly used when writing Checkpoint Write to the log file;

L Last checkpoint at (LSN4): LSNthat is currently written to checkpoint ;

For the system, the above 4 LSN is decremented, namely: lsn1>=lsn2>=lsn3>=lsn4.

The specific example is as follows (using the show InnoDB status/g command to view,oldest modified data log is not displayed):

1.1.3.Protection Mechanisms

Innodb data is not real-time write disk, in order to avoid downtime data loss, to ensure the ACID properties of the data,Innodb at least to ensure that the data corresponding to the log can not be lost. For different situations,Innodb take different countermeasures:

L log loss due to downtime
InnoDB has a log brush disk mechanism, can be controlled by innodb_flush_log_at_trx_commit parameters;

L Log overwrite causes log loss

InnoDB log file size is fixed, write the time by taking the remainder to calculate the offset, so there are two LSN write to the same location, the next write to the front is covered, in the "Write mechanism" section of the example,LSN= 100000000 and LSN=1600000000 Two log offsets are the same. In this case, in order to ensure data consistency, it must be required that the lsn=1000000000 corresponding dirty page data has been brushed to disk, that is, the last checkpoint corresponding LSN is required Must be greater than 1000000000, otherwise covered dogo Katsuragi Zhi also no, data also no brush disk, once down, the data is lost.

In order to solve the problem of data loss in the second situation,Innodb implements a set of log protection mechanisms, which are implemented in detail as follows:

, the line represents the log space (logcap, approximately equal to the total size of the log file *0.8,0.8 is a factor of safety ),CKP age and Buf age is two floating points,Buf async,Buf sync,Ckp async,Ckp sync are several fixed points. The meanings of each concept are as follows:

Concept	Calculation	Meaning
CKP Age	Lsn1-lsn4	has not done the Checkpoint log range, if CKP age exceeds the log space, description of the overwritten log (LSN1-LSN4-log Cap The corresponding log and data "may" not have been brushed to disk
Buf Age	Lsn1-lsn3	There is no scope for the log of the dirty page brush, if Buf age exceeds the log space, indicating that the overwritten log (LSN1-LSN3-log cap) corresponds to the data "yes" Not yet brushed to disk
Buf Async	Log space Size * 7/8	Forcing the Buf to age-buf the dirty page brush of async, at which point the transaction can continue to execute, so for asyncThere is no direct impact on the execution speed of the transaction (with indirect effects such as CPU and disk being busier, The execution speed of the transaction may be affected)
Buf Sync	Log space Size * 15/16	Force the Dirty page of Buf age-buf async to brush the disk, at which time the transaction stops executing , so for sync, because there are a large number of dirty page brush disk, so the blocking times than CKP sync to be long.
CKP Async	Log space Size * 31/32	Force write Checkpoint, at which time the transaction can continue to execute, so for async, there is no impact on the execution speed of the transaction (the indirect effect is not significant, because the operation of writing Checkpoint is relatively simple)
CKP Sync	Log space Size * 64/64	Force write Checkpoint, at this time the transaction stop execution , so for sync, but because write Checkpoint operation is relatively simple, Even if it's blocked, the time is short.

When the transaction executes faster than the dirty page brush speed,CKP age and Buf will grow incrementally, forcing a dirty page brush or writing Checkpointwhen the async point is reached. If this does not keep up with the speed of transaction execution, in order to avoid data loss, when the sync point is reached, it will block all other transactions, specifically for dirty page brush or write Checkpoint.

so theoretically , as long as the transaction executes faster than the dirty page brush speed, the log protection mechanism will eventually be triggered, thus blocking the transaction , causing the MySQL operation to hang.

Since writing Checkpoint itself is simpler than writing dirty pages, it takes much less time and CKP sync points after the Buf sync Point, so most of the congestion is blocked Buf Sync Point, this is also when the transaction is blocked, theIO is very high, because this time in the constantly brush dirty page data to disk. For example, the following log shows that many transactions are blocked at the Buf sync point:

Note: InnoDB's log protection mechanism implementation can refer to the log0log.c file's void log_check_margins (void) function.