MySQL Flush-List and dirty page flushing mechanism

1. Review

The Buffer Pool will be initialized after MySQL is started. The Buffer Pool will also initialize N blank cache pages, and their description data will be organized into an LRU linked list and a FreeList doubly linked list.

At this time, when you read a data page from the disk, you will first find the description information of a free cache page from the Free List, and then load the data page you read into the cache page. At the same time, the description information of the cache page is removed from the Free List. In addition, the description information block will also be maintained in the LRU linked list.

After the data page is loaded into the Buffer Pool, you can perform changes on it.

2. Flush List

In order to speed up the response to the client, MySQL will modify the data in the Buffer Pool, but once you modify the cache page in the LRU linked list, the data in the page will be inconsistent with the data page information on the disk! People generally call this kind of data page a dirty page.

In order to ensure the eventual consistency of data, MySQL needs to flush dirty pages back to disk!

But the question is: which data pages need to be flushed back to disk?

This brings us to Flush List.

The Flush List is very similar to the Free List, both of which are bidirectional linked lists organized by the data description information in the Buffer Pool.

Once you make a modification to the buffer page in memory, the description information block corresponding to the buffer page will be added to the Flush List. In this way, when there are not enough data pages in the Buffer Pool, we can give priority to refreshing the dirty data pages in the Flush List to the disk.

If you have read the previous articles, you must know about LRUList, FreeList, FlushList, Buffer Pool, dirty pages, and dirty data.

Let’s pursue the victory! Let's take a look at the dirty page drop mechanism

3. What is a dirty page? What is dirty data?

What are dirty pages?

In the article introducing Flush List, I mentioned that dirty pages are cache pages that have been modified in the LRU linked list. They are inconsistent with the data pages on disk, and dirty pages need to be flushed back to disk.

What is dirty data?

This problem actually leads to the concept of dirty read. For example: Transaction A reads uncommitted data from Transaction B. We call this data dirty data.

4. When to flush dirty pages back to disk

When the Buffer Pool is not enough, according to the LRU mechanism, MySQL will move the cache pages of the Old SubList part out of the LRU linked list. If the description information of the removed cache page is in the Flush List, MySQL has to flush it back to disk.

There are many opportunities for the InnoDB storage engine to flush dirty pages back to disk. You can take a look at it as extended knowledge.

1. When the MySQL database is closed, all dirty data pages are flushed back to disk. This feature is controlled by the parameter: innodb_fast_shutdown=0. By default, InnoDB flushes dirty pages back to disk and cleans up the undo log before shutting down.

2. There is a background thread Master Thread that asynchronously refreshes a certain proportion of pages in the Buffer Pool back to disk every second or every ten seconds.

3. In MySQL 5.7, the Buffer Pool is refreshed by page cleaner threads.

We can control the number of page cleaner threads through the innodb_page_cleaners parameter, but when you adjust this value to be larger than the number of Buffer Pools, MySQL will automatically set the number of innodb_page_cleaners to the number of innodb_buffer_pool_instances.
Prior to Innodb1.1.x, it was necessary to ensure that there were at least 100 free pages available in the LRU list. Falling below this threshold triggers a flush of dirty pages.
Starting from MySQL 5.6, that is, innodb 1.2.X, the innodb_lru_scan_depth parameter specifies the downstream distance for each buffer pool instance that page cleaner threads scan the Buffer Pool to find dirty pages to be refreshed. The default value is 1024, and the background thread will be executed once per second.
4. When there are too many dirty data pages, it will trigger the dirty data pages to be refreshed back to disk. This mechanism can be controlled by the parameter innodb_nax_dirty_pages_pct. For example, if it is set to 75, it means that when the dirty data pages in the Buffer Pool reach 75% of the overall cache, a refresh action is triggered. The default value of this parameter is 0. This disables the early flushing behavior of the Buffer Pool.

5. When the redo log is unavailable, the dirty pages in the dirty page list will be forced to be refreshed back to disk. This mechanism is also completed by a background thread.

5. Other knowledge points about dirty page refresh

Refresh adjacent data pages: This means that when MySQL refreshes a dirty page back to disk, it also refreshes the dirty pages adjacent to the dirty page back to disk in the same manner.

This process can be controlled by the parameter innodb_flush_neighbors.

• When set to 0, the adjacency refresh function is disabled.
• When set to 1, flush adjacent dirty pages in the same manner.
• When set to 2, dirty pages are flushed with the same degree.

So how do you choose which state to set it to?

You can decide based on the storage type of the machine where the MySQL instance is located. If the storage is HDD, it is recommended to turn it on because the disk refresh rate of HDD is low. Turning on this parameter can effectively reduce IO operations. On the contrary, if SSD storage is used, it has the characteristic of high disk IO, so it is recommended to disable this parameter.

The above is the details of MySQL Flush-List and dirty page disk mechanism. For more information about MySQL Flush-List and dirty page disk mechanism, please pay attention to other related articles on 123WORDPRESS.COM!