preface
Here’s what happened. I saw this question in the answer to the invitation from Hu:
–
And without thinking about it, I snapped down this answer:
This is actually flushed by the MySQL background thread. All modified pages in the Buffer Pool are marked as dirty and placed in a Flush list.
MySQL then starts a background thread to Flush dirty pages from the Flush linked list to disk when the condition is met.
The conditions are as follows: The number of dirty pages reaches 10% of the number of pages in the Buffer Pool. Of course, the value of 10% is variable, which is specified by the innodb_max_dirty_pages_pcT_lwm configuration item. The default value is 10%. This value must also be less than the value (90%) of the other configuration innodb_max_DIRty_pages_pct.
The number of threads started is controlled by another variable, Innodb_page_CLEANERS, which defaults to 4. You don’t usually change that.
Something like that.
But a brother from behind said, “I don’t know what you are saying.”
When I looked back later, I found that what I wrote at that time was indeed a little too jumpy, but it was not so coherent when I looked back later, so I decided to explain this matter clearly again.
1. The table data
How often does the MySQL table flush?
- What to swipe to disk
- When does it flush to disk
Let’s discuss it separately.
2. What to swipe to disk
It looks like a bit of nonsense, but it must be flushing data to disk. So we need to talk more about how data is flushed to disk.
The answer is that page
For those of you who don’t know much about pages, check out the previous article: The Complete Guide to MySQL Pages – How to Shallow in and Deep out of Pages
In InnoDB, a page is the smallest unit of data to be managed. When MySQL runs with InnoDB as its storage engine, row by row of table data is organized into page by page and placed in Buffer Pool.
Learn more about InnoDB’s memory structure and how it works
Page after page of data is stored in the Buffer Pool. When a DML statement (or CRUD) makes a change to the table data, the page on which the data resides is marked as a dirty page.
InnoDB stores these dirty pages in a structure called Flush list. Pages that are put into this list are flushed to disk, but not immediately.
Like other InnoDB logs such as the Redo Log, these logs have their own flush policy. For example, the Redo Log can be represented as follows:
With the parameter 0, Redo logs are written and flushed to disk every second.
With the parameter 1, the Redo Log is flushed to disk after each transaction commit
Parameter 2. Every time a transaction commits, it is written to the OS cache and the OS cache is flushed to disk every second
The Flush list has its own strategy.
3. When will the disk be flushed
Following the section, the policy is to Flush dirty pages from the Flush list to disk if the number of dirty pages reaches 10% of the number of pages in the Buffer Pool. For example, if there are 100 pages in the Buffer Pool, the number of dirty pages reaches 10 will start a background thread, triggering a flush.
Of course, the [10%] value is configurable and can be adjusted through the MySQL configuration item innodb_max_dirty_pages_pcT_lwm, although the default value is 10%. However, we cannot adjust the value beyond a certain maximum value, which is specified by innodb_max_dirty_pages_pct. The default value is 90%.
In other words, the default flush threshold is 10%. If you need to customize, the maximum value cannot exceed 90%.
4. Who is responsible for brushing the plates
As mentioned in the previous section, there is no doubt that threads are started to do this. We need to focus on how many threads are started to do this.
The answer is four, which we can also change with the innodb_page_CLEANERS configuration item, but generally don’t change.
So much for that point.
Welcome to wechat search and follow [SH full stack Notes]. If you think this article is helpful to you, please click a like, close a note, share a share and leave a message.