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

This article mainly explains the principle of "InnoDB dirty page refresh mechanism". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the principle of InnoDB dirty page refresh mechanism".

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.

This creates a dirty page. Since there is a redo log to ensure data persistence, and the data can be fetched directly from the buffer pool page when querying, why refresh the dirty page?

To the disk? If the redo log can grow infinitely, and the buffer pool is large enough to cache all data, then there is no need to refresh the dirty pages in the buffer pool

To the disk. However, there are usually the following problems:

The server memory is limited and the buffer pool is insufficient to cache all data.

The cost requirement of unlimited increase in redo log is too high.

If you redo all logs in case of downtime, the recovery time is too long.

In fact, when the database goes down, the database does not need to redo all the logs, only the logs after the last point brushing. This point is called Checkpoint.

It solves the above problems:

Shorten database recovery time

Flush dirty pages to disk when the buffer pool is insufficient

Refresh dirty pages when the redo log is not available

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

Coverage reuse.

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 11102619599

Log flushed up to 11102618636

Last checkpoint at 11102606319

0 pending log writes, 0 pending chkp writes

15416290 log i/o's done, 12.32 log i/o's/second

How many pages are refreshed each time by the Checkpoint 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 Checkpoint

Fuzzy Checkpoint

Sharp 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 Checkpoint

FLUSH_LRU_LIST Checkpoint

Async/Sync Flush Checkpoint

Dirty Page too much Checkpoint

Master Thread Checkpoint

Master 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 that there are enough pages in LRU for user queries

The operation thread, if not, will eliminate the page at the end of the LRU list. If there is a dirty page in the eliminated page, it will force Checkpoint to swipe back the dirty page data to

Disk, which obviously blocks the user query thread. Starting with the InnoDB1.2.X version, this check is placed in a separate Page Cleaner Thread

And users 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.

Before 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 passed through

Parameter innodb_max_dirty_pages_pct to set:

Mysql > show variables like 'innodb_max_dirty_pages_pct'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_max_dirty_pages_pct | 60 | |

+-+ +

Thank you for your reading, the above is the content of "the principle of InnoDB dirty page refresh mechanism". After the study of this article, I believe you have a deeper understanding of the principle of InnoDB dirty page refresh mechanism, 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.