The checkpoint in Inondb

Checkpoint is mainly to solve the problem:

1. Shorten the recovery time of the database

2. When the buffer pool is not enough, flush the dirty pages to disk

3. Refresh dirty pages When redo logs are not available

The InnoDB engine uses the LSN (log sequence number) to mark the log version. LSN is a 8-byte number. Each page has LSN, redo log, LSN, checkpoint, and LSN.

There are two types of checkpoint inside the InnoDB storage engine:

1.sharp checkpoint

Occurs when the database shuts down. Flushes all dirty pages to disk. This is the default way, that is, innodb_fast_shutdown=1

Mysql> Show variables like ' innodb_fast_shutdown '; +----------------------+-------+| Variable_name        | Value |+----------------------+-------+| Innodb_fast_shutdown | 1     

2.fuzzy checkpoint
When the database is running, using sharp checkpoint can affect the availability of the database.
So at run time, use fuzzy checkpoint to refresh the page (only part of the dirty page is refreshed, not all dirty pages).

There are several cases of fuzzy checkpoint in InnoDB:
-master Thread Checkpoint:

(Almost every second or every 10 seconds) asynchronously refreshes a percentage of the page-to-disk from the list of dirty pages in the buffer pool

-flush_lru_list Checkpoint:

Remove the page from the end of the LRU list, InnoDB to ensure that there are enough free pages available in the LRU list. The number of available pages in the LRU list is controlled by the parameter Innodb_lru_depth in 5.6, which defaults to 1024

Mysql> Show variables like ' innodb_lru_scan_depth '; +-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_lru_scan_depth | 1024  

-async/sync Flush Checkpoint

When redo logs are not available, you need to force some pages to be flushed to disk.

-dirty page too much checkpoint

Too many dirty pages, forcing checkpoints to occur

Mysql> Show variables like ' innodb_max_dirty_pages_pct '; +----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_max_dirty_pages_pct | 75    

Forced refresh when dirty pages account for 75%.

The checkpoint in Inondb