Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is checkpoint technology". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is checkpoint technology".

In MySQL, a DML statement, such as delete or update, changes the records in the page, so the page is dirty, that is, the version of the page in the buffer pool is newer than that of the disk, and the database needs to flush the data from the buffer pool to disk.

If a new version of the page is flushed to disk every time the page in the buffer pool changes, then the performance consumption must be high, and if downtime occurs during the flushing of dirty pages to disk, the data cannot be recovered. In order to avoid the problem of data loss, the transaction database system adopts the strategy of Write Ahead Log, that is, log first, redo log first, and then modify the page.

Suppose the following scenario, is it possible that the dirty pages of the buffer pool can not be brushed back to disk?

1. The buffer pool is large enough to buffer all data on the disk.

2. The redo log can be increased infinitely.

For the first scenario:

In a production environment, 3TB volume databases are rare or even larger, while 3TB memory servers are almost non-existent, so this scenario is almost impossible.

For the second scenario:

The unlimited growth of redo logs means that DBA or SA needs to monitor storage usage in real time, and the device can be expanded indefinitely, which is almost impossible to achieve.

Even if the above scenario is possible, once the MySQL server goes down, the redo log can be reapplied for a very long time, even for several years.

As a result, checkpoint technology has emerged, which mainly solves the following problems:

1. Shorten the recovery time of the database

2. Flush dirty pages to disk when the buffer pool is insufficient.

3. Refresh the dirty page when the redo log is not available

Within innodb, there are two types of checkpoint, namely:

Sharp Checkpoint

Fuzzy Checkpoint

Sharp checkpoint means that when the database is shut down, dirty pages are flushed to disk, which is the default working mode, namely innodb_fast_shutdown=1.

If the database runs with sharp checkpoint, the availability of the database will be greatly affected. When the database is running, fuzzy checkpoint is used to refresh dirty pages, that is, only some dirty pages are refreshed, not all dirty pages are to disk.

Sharp checkpoint that can occur in innodb in the following ways:

Master Thread Checkpoint

FLUSH_LRU_LIST Checkpoint

Asnc/Sync Flush Checkpoint

Dirty Page too much Checkpoint

Master Thread Checkpoint

The new version will clean page cleaner thread independently, flushing dirty pages to disk every second or every ten seconds.

FLUSH_LRU_LIST Checkpoint

LRU list does not have enough free pages when brushing dirty.

FLUSH_LRU_LIST Checkpoint

When the redo log is not available, brush it dirty.

Dirty Page too much Checkpoint

When the buffer pool dirty page is too often, it is automatically cleaned and controlled by the parameter innodb_max_dirty_pages_pct. A value of 75 means that when the buffer pool dirty page occupies 75%, checkpoint will be forced to refresh part of the dirty page to disk.

Thank you for your reading, the above is the content of "what is checkpoint technology", after the study of this article, I believe you have a deeper understanding of what is checkpoint technology, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.