InnoDB Architecture (iii) CHECKPOINT technology

Checkpoint Technology

Previous InnoDB Architecture (ii) memory from buffer pool , buffer pool management , redo log Buffer , additional memory buffer These four points describe the memory structure of the InnoDB storage engine . In the process of flushing the buffer pool's data to disk, checkpoint technology is used, and this article focuses on the checkpoint in memory to the application.

First, checkpoint use the background

Because of the daily DML statements, such as: Theupdate/delete Operation First operates the buffer pool data and does not immediately write to the disk , which may cause the in -memory data to be inconsistent with the data on the disk The situation. The pages of the buffer pool that are inconsistent with the disk data are the dirty pages we often say. And checkpoint's job is to put dirty pages in memory and flush the dirty pages to disk under certain conditions.

If the outage occurs during the flushing of the page data from the buffer pool to disk, the data cannot be recovered; to avoid this, the write Ahead log policy is used, that is, when the transaction commits, the redo log is written, the page is modified, In this case, the downtime can also be recovered by redo logs .

Ii. purpose of Checkpoint　　

1. If the redo log is too large, the recovery time is too long when the database is started;

2. If the buffer pool is not enough , the dirty page data should be flushed to disk first;

3. redo log is not available , refresh dirty page to disk;

Third, the operation mechanism of checkpoint

Before you understand the operational mechanism, consider these questions: When Checkpoint happened? What is the condition? The selection of dirty pages (objects)?

Within the InnoDB storage engine, checkpoint is divided into two types:

　　(a) Sharp Checkpoint

Sharp Checkpoint occurs when the database shuts down, flushing all dirty pages back to disk , which is the default mode of operation, parameter: Innodb_fast_shutdown=1.

　　(ii) Fuzzy Checkpoint

When the InnoDB storage engine runs, it uses the fuzzy checkpoint to refresh the page, refreshing only a portion of the dirty pages . In the following four cases the fuzzy Checkpoint will be set up:

　　　　1. Master Thread Checkpoint

For master thread, the dirty page list of the buffer pool refreshes a certain percentage of the page back disk at a rate of every second or every n seconds, and the process is asynchronous , and the user query thread does not block.

　　　　2. Flush_lru_list Checkpoint

To ensure that there are 100 or so free pages available in the LRU list , the user query thread checks to see if the LRU list has enough space to work before the InnoDB 1.1.x version. If not, the page that overflows the end of the LRU list, according to the LRU algorithm, needs to be checkpoint if the pages have dirty pages. Therefore called: Flush_lru_list Checkpoint.

3. Async/sync Flush Checkpoint

Async/sync Flush checkpoint occurs when the Redo log is unavailable (full), flushes a portion of the dirty page in buffer pool to disk, and after the Dirty page is written to disk, the redo log of the thing can be freed up. The size of the Redo_log file can be configured by Innodb_log_file_size.

4. Dirty Page Too much Checkpoint

That is, too many dirty pages, forcing checkpoint to ensure that the buffer pool has enough pages available. Parameter setting: innodb_max_dirty_pages_pct = 75 indicates that checkpoint is enforced when the number of dirty pages in the buffer pool is 75%. 1.0.x after default 75

　　　　　　

　　

InnoDB Architecture (iii) CHECKPOINT technology