MySQL InnoDB log mechanism in-depth analysis

MySQL InnoDB log mechanism in-depth analysis

http://blog.csdn.net/yunhua_lee/article/details/6567869

1.1. Log & Checkpoint

InnoDB transaction log refers to redo log, 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 64-bit integer.

1.1.1. Write mechanism 1.1.1.1. Log Write

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

LSN=1G, the log file size of a total of 600M, this write 512 bytes, the actual write operation is:

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

L Write log: Find the offset 400M, write 512 bytes of log content, the next transaction LSN is 1000000512;

1.1.1.2. Checkpoint Write

InnoDB implements the mechanism of fuzzy checkpoint, each fetch to the oldest dirty page, and then make sure 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 as a checkpoint point to log file, meaning " The log and data for the LSN preceding 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 for InnoDB 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): Current system LSN maximum, the new transaction log LSN will be generated on this basis (lsn1+ the size of the new log);

L Log flushed up to (LSN2): LSN that is currently written to the log file;

L Oldest modified data log (LSN3): The LSN of the current oldest dirty page data, which writes the LSN directly to the log file when the checkpoint is written;

L last checkpoint at (LSN4): LSN that 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 Downtime causes log loss InnoDB has a log brush disk mechanism, can be controlled by innodb_flush_log_at_trx_commit parameters;

Log overwrite causes log loss

InnoDB log file size is fixed, write the time by taking the remainder to calculate the offset, so that there are two LSN write to the same location, the next write to the front is covered, in the "Write mechanism" section of the sample as an example, The offsets for the lsn=100000000 and lsn=1600000000 two logs are the same. In this case, in order to ensure data consistency, must require that the lsn=1000000000 corresponding dirty page data has been brushed to disk, that is, the last checkpoint corresponding LSN must be greater than 1000000000, or overwrite Dogo Katsuragi zhi also no, data also no brush disk , the data is lost once the outage is made.

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 (log cap, approximately equal to the total size of the log file *0.8,0.8 is a factor of safety), CKP age and Buf age are 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	There is no log range for checkpoint, if CKP age exceeds log space, the log and data for the overwritten log (lsn1-lsn4-log cap) are "probably" not being 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, the corresponding data for the overwritten log (lsn1-lsn3-log cap) is "definitely" not being 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 there is no direct impact on the execution speed of the transaction (with indirect effects such as CPU and disk being busier, and the execution speed of the transaction may be affected)
Buf Sync	Log space Size * 15/16	Forcing a dirty page brush on the Buf age-buf async, where the transaction stops executing, so for sync, there is a lot of dirty page brush disk, so the time to block is longer than CKP sync.
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 due to write checkpoint operation is relatively simple, even if blocked, the time is very short

When a transaction executes faster than the dirty page brush speed, CKP age and buf age grow, and when the async point is reached, a dirty page brush or write checkpoint is enforced, and if this does not catch up with the speed of the transaction execution, in order to avoid data loss, when the sync point is reached, 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, it will eventually trigger the log protection mechanism, and then block the transaction, causing the MySQL operation to hang.

Since writing checkpoint itself is simpler than writing dirty pages, consuming much less time, and CKP sync points after the buf sync Point, most of the blocking is blocked at the buf Sync Point, which is why the IO is high when the transaction is blocked, Because this time in 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.