MySQL principle ~ checkpoint

A brief introduction: Let's talk about checkpoint today.

Two definitions:

Checkpoin is the redo log checkpoint that refreshes the data page to disk, saves the record with the LSN number, the function is when the outage and other crash situation, when the restart will be queried checkpoint point, after the checkpoint occurs after the changes to disk recovery.

Three functions:

1 shorten the recovery time of the database

The location of the checkpoint that was recorded in the database recovery time redo log, the page before the point was flushed back to the disk, and only the redo log after checkpoint was restored. This greatly shortens the recovery time.

2 buffer pool Not enough or trigger dirty page percent mechanism to flush dirty pages back to disk

Buffer pool is not enough, according to the LRU algorithm, overflow the least recently used pages, if the page is dirty page, force checkpoint, the page is flushed back to the disk, the dirty page is also triggered automatically.

3 redo logs are not available (cannot be reused, cannot be overwritten), refresh dirty pages

Redo log is not available, it means that this part of redo log can not be overwritten, why? Because: this part of the corresponding data has not been flushed to disk. The redo log is designed to be used cyclically. When the database is restored, it can be overwritten if it is not needed, and if necessary, the checkpoint must be forced to flush the pages in the buffer pool at least to the current redo log location

Four main work

Checkpoint: Brush the dirty pages in the buffer pool back to disk.

Five refresh Mode

Inside the InnoDB storage engine, two kinds of checkpoint, respectively:

• Sharp Checkpoint for consistent refresh at database shutdown (triggered on database shutdown)
• Fuzzy Checkpoint for partial-page refreshes at database runtime (database runtime periodic triggering)

Six database shutdown mode parameters explained

When closed, the parameter innodb_fast_shutdown affects the behavior of the table's storage engine as InnoDB. The value of this parameter is 0, 1, 2.

1. 0 means that when MySQL is off, InnoDB needs to complete all full purge and merge insert buffer operations, which can take some time, and sometimes even several hours to complete. If you are doing a InnoDB plugin upgrade, you will typically need to tune this parameter to 0 before closing the database.
2. 1 is the default value for this parameter, which means that you do not need to complete the above full purge and merge insert buffer operations, but some data dirty pages in the buffer pool will still be flushed to disk.
3. 2 means that the full purge and merge insert buffer operations are not completed, and the data dirty pages in the buffer pool are not written back to disk, but the logs are written to the log file. This will not cause any transactions to be lost, but the next time the MySQL database is started, a recovery operation will be performed (recovery)

It can be found that setting the shutdown level also affects Checkpoint operations, such as sharp Checkpoint does not trigger in the case of =2

MySQL principle ~ checkpoint