Shulou(Shulou.com)06/01 Report--

Summary:

We know that InnoDB uses Write Ahead Log strategy to prevent downtime data loss, that is, when a transaction commits, the redo log is written first, and then the in-memory data page is modified, resulting in dirty pages. Since there is a redo log to ensure data persistence, and you can retrieve data directly from the buffer pool page when querying, why refresh the dirty page to disk? If the redo log can grow infinitely and the buffer pool is large enough to cache all data, there is no need to flush dirty pages from the buffer pool to disk. However, there are usually the following problems:

Limited server memory, insufficient buffer pool, unable to cache all data redo logs infinitely increase cost is too high if redo full log recovery time is too long in case of downtime, in fact, when the database is down, the database does not need to redo all the logs, only need to execute the logs after the last point brushing. This point is called Checkpoint, and it solves the above problem: when the database recovery time buffer pool is insufficient, the dirty page is flushed to the disk redo log when the disk redo log is not available.

The redo log is designed to be recyclable, and when the log file is full, the unneeded part of the redo log where the corresponding data has been flushed to disk can be overwritten and reused.

The InnoDB engine marks the version through LSN (Log Sequence Number), and LSN is the end point of each log in the log space, expressed as a byte offset. Every page has LSN,redo log, LSN,Checkpoint and LSN. You can observe this by using the command show engine innodb status:

-

LOG

-

Log sequence number 1039878815567

Log flushed up to 1039878815567

Pages flushed up to 1039878814486

Last checkpoint at 1039878814486

0 pending log writes, 0 pending chkp writes

5469310 log i/o's done, how many pages are refreshed each time by the 1.00 log i/o's/secondCheckpoint mechanism, where are the dirty pages taken, and when is the refresh triggered? These are all very complicated. There are two types of Checkpoint, namely:

Sharp CheckpointFuzzy CheckpointSharp Checkpoint occurs when the database is shut down, brushing all dirty pages back to disk. Use Fuzzy Checkpoint to refresh some dirty pages at run time. Some dirty pages are refreshed in the following ways:

Master Thread CheckpointFLUSH_LRU_LIST CheckpointAsync/Sync Flush CheckpointDirty Page too much CheckpointMaster Thread CheckpointMaster Thread refreshes a certain percentage of pages back to disk from the list of dirty pages in the buffer pool at a rate of every second or every ten seconds. This process is asynchronous and does not block the query thread.

Flush LRU List Checkpoint

InnoDB should make sure that there are about 100 free pages available in the LRU list. Before the InnoDB1.1.X version, check whether there are enough pages in LRU for the user query operation thread. If not, the page at the end of the LRU list will be eliminated. If there are dirty pages in the eliminated page, Checkpoint will be forced to brush back the dirty page data to disk, which obviously blocks the user query thread. Starting with the InnoDB1.2.X version, this check is done in a separate Page Cleaner Thread, and the user can control the number of pages available in the LRU list through innodb_lru_scan_depth, with a default value of 1024.

Async/Sync Flush Checkpoint

Refers to the need to force some pages in the dirty page list to be flushed back to disk when the redo log file is not available. This ensures that the redo log files can be recycled. Prior to the InnoDB1.2.X version, Async Flush Checkpoint blocked the user query thread that found the problem, and Sync Flush Checkpoint blocked all query threads. InnoDB1.2.X is then put into a separate Page Cleaner Thread.

Dirty Page Too Much Checkpoint

When there are too many dirty pages, the InnoDB engine forces Checkpoint. The goal is also to ensure that there are enough free pages available in the buffer pool. It can be set by the parameter innodb_max_dirty_pages_pct, which defaults to 75%.

(root@localhost) [(none)] > show variables like 'innodb_max_dirty_pages_pct'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_max_dirty_pages_pct | 75 | |

+-+ +

1 row in set (above 0.00 sec are several trigger mechanisms for dirty page refresh. Next, let's talk about the logging mechanism and the third point of Async/Sync flush checkpoint principle.

Brief introduction of Log and Checkpoint

The transaction log of Innodb refers to Redo log, or Log for short, which is saved in the log file ib_logfile*. Innodb also has another log Undo log, but the Undo log is stored in a shared tablespace (ibdata* file).

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

Noun explanation: LSN, log sequence number, Innodb log sequence number is a 64-bit integer.

Log write

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

LSN=1G. The total log file size is 600m. If you write 512 bytes this time, the actual write operation is:

| |-find the offset: since the LSN value is much larger than the log file size, the offset is 400 MB by taking the remainder. |

| |-write to the log: find the location with an offset of 400m and write the content of the 512-character festival log. The LSN of the next transaction is 1000000512. |

Checkpoint write

Innodb implements the mechanism of Fuzzy Checkpoint, which fetches the oldest dirty page every time, and then ensures that the LSN before the LSN corresponding to the dirty page has been written to the log file, and then records the LSN of the dirty page to the log file as a Checkpoint point, which means "the log and data corresponding to the LSN before this LSN have been written to the disk file". When restoring the data file, Innodb scans the log file, and when it is found that the LSN is less than the corresponding LSN of Checkpoint, the recovery is considered to be complete.

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

Introduction to the process and principle of Flush Refresh

Since Checkpoint and log are closely related, log and Checkpoint are described together. The detailed implementation mechanism is as follows:

As shown in the figure above, a transaction log for Innodb goes through four phases:

1) creation phase: the transaction creates a log

2) Log flushing: log is written to the log file on disk

3) data flushing: the dirty page data corresponding to the log is written to the data file on the disk

4) write CKP: the log is written to the log file as Checkpoint

Corresponding to these four stages, the system records four log-related information for other processing purposes:

Log sequence number (LSN1): the current system LSN maximum, based on which the new transaction log LSN will be generated (the size of the LSN1+ new log)

Log flushed up to (LSN2): LSN that has been written to the log file

Pages flushed up to (LSN3): the LSN corresponding to the oldest dirty page data. When writing Checkpoint, write this LSN directly to the log file.

Last checkpoint at (LSN4): LSN that has been written to Checkpoint

For the system, the above four LSN are decreasing, namely: LSN1 > = LSN2 > = LSN3 > = LSN4.

The specific examples are as follows (viewed using the show engine innodb status\ G command)

Conceptual calculation means that Ckp ageLSN1- LSN4 has not yet done the log scope of Checkpoint. If the Ckp age exceeds the log space, it means that the log and data corresponding to the covered log (LSN1-LSN4-Log cap) may not have been brushed to disk. Buf ageLSN1- LSN3 has not washed the dirty pages to the scope of the log, if the Buf age exceeds the log space. Indicates that the data corresponding to the overwritten log (LSN1-LSN3-Log cap) has not been "definitely" brushed to the disk yet. * 7 LSN1-LSN3-Log cap 8 forces the dirty pages of Buf age-Buf async to be brushed, so the transaction can continue to execute, so it is async, which has no direct effect on the execution speed of the transaction (there are indirect effects, such as CPU and disk are busier. The execution speed of the transaction may be affected) the Buf sync log space size * 15 Buf age-Buf async 16 forces the dirty pages of 2 * (Buf age-Buf async) to be flushed, and the transaction stops execution, so it is sync. Because there are a large number of dirty pages flushing, the blocking time is longer than Ckp sync. The Ckp async log space size * 31 Checkpoint 32 forces Checkpoint to be written, and the transaction can continue to execute at this time, so it is async, which has no effect on the execution speed of the transaction (indirect effect is also small, because the operation of writing Checkpoint is relatively simple) Ckp sync log space size * 64 Checkpoint is forced to write at this time, so the transaction stops execution, so it is sync, but because the operation of writing Checkpoint is relatively simple, even if blocking, the time is very short.

When the transaction execution speed is greater than the dirty page flushing speed, Ckp age and Buf age will gradually increase. When the async point is reached, dirty page flushing or writing Checkpoint will be forced. If this still cannot catch up with the transaction execution speed, then in order to avoid data loss, when it reaches the sync point, it will block all other transactions and specialize in dirty page flushing or writing Checkpoint.

Therefore, in theory, as long as the transaction execution speed is faster than the dirty page flushing speed, the log protection mechanism will eventually be triggered, which will block the transaction and cause the MySQL operation to suspend.

Because the operation of writing Checkpoint itself is simpler and takes much less time than writing dirty pages, and the Ckp sync point is after the Buf sync point, most of the blocking is blocked at the Buf sync point, which is why the IO is very high when the transaction is blocked, because the dirty page data is constantly brushed to disk at this time. For example, the following screenshot log shows that many transactions are blocked at the Buf sync point:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.