The hierarchical architecture of the application system, in order to speed up data access, will be the most frequently accessed data, in the cache (cache), to avoid every time to access the database.
The operating system (OS) has a buffer pool mechanism to avoid accessing disks every time and accelerate data access.
MySQL, as a storage system, also has a buffer pool mechanism to avoid disk I/O every time you query data.
Today, I’m going to talk about InnoDB’s buffer pool.
What does InnoDB’s buffer pool cache? What’s the use?
Cache table data and index data and load data on disk to buffer pool, avoiding disk I/O for each access and accelerating access.
Fast. So why not put all the data in the buffer pool?
There are always two sides to everything. Aside from data volatility, the opposite of fast access is low storage capacity:
(1) The cache access is fast, but the capacity is small, the database stores 200G data, the cache capacity may only be 64G;
(2) Memory access is fast, but the capacity is small, buy a laptop disk has 2T, memory may only be 16G;
Therefore, only the “hottest” data can be placed in the “nearest” place to “minimize” disk access.
How can buffer pools be managed and eliminated to maximize performance?
Before I get into the details, let me introduce the concept of “preread.”
What is prereading?
Disk reads and writes do not read on demand, but on a page by page. At least one page of data (usually 4K) is read at a time. If the data to be read in the future is in the page, it can save subsequent disk IO and improve efficiency.
Why does prereading work?
Data access, which generally follows the principle of “centralizing read and write”, uses some data and more often uses nearby data. This is known as the “locality principle”, which shows that preloading is effective and does reduce disk IO.
How does reading by page (4K) relate to InnoDB’s buffer pool design?
(1) Disk access improves performance by reading by page, so buffer pools generally cache data by page;
(2) The prefetch mechanism allows us to add some “might want to access” pages to the buffer pool in advance to avoid future disk IO operations;
What algorithm does InnoDB use to manage these buffer pages?
The most easy to think of is LRU(Least recently used).
Voice-over: Memcache, OS both use LRU for page replacement management, but MySQL does not play the same way.
How does traditional LRU manage buffer pages?
The most common way to play this is to place the cached page at the head of the LRU as the most recently accessed element and thus the last to be eliminated. There are two cases:
(1) If the page is already in the buffer pool, just “move” to the LRU head, and no page is eliminated;
(2) Page is not in the buffer pool, in addition to do “put” LRU head action, but also do “eliminate” LRU tail page action;
As shown in the figure above, suppose the LRU that manages the buffer pool is 10 in length, buffered with page numbers 1,3,5… , 40,7 pages.
Suppose the next data to be accessed is on a page with page number 4:
(1) Page 4 is already in the buffer pool;
(2) put the page number 4 in the LRU header, no page is eliminated;
Voiceover: In order to reduce data movement, LRU is generally implemented with linked lists.
Suppose, then, that the data to be accessed is on a page with page number 50:
(1) Page 50 is not in the buffer pool;
(2) Put the page number 50 in the LRU header, and eliminate the page number 7;
Traditional LRU buffer pool algorithm is very intuitive, OS, Memcache and many other software are used, MySQL why so saccharine, can not be directly used?
There are two problems:
(1) Prefetch failure;
(2) Buffer pool pollution;
What is prefetch failure?
Because of read-ahead, the page is put into the buffer pool in advance, but MySQL does not Read the data from the page, which is called prefetch failure.
How do I optimize the prefetch failure?
To optimize prefetch failure, the idea is as follows:
(1) Make the pre-read failed page stay in the buffer pool LRU as short as possible;
(2) let the page that is actually read move to the buffer pool LRU header;
To ensure that the hot data that is actually read remains in the buffer pool for as long as possible.
The specific methods are:
(1) LRU is divided into two parts:
- New Sublist
- The old Sublist
(2) The tail of the Cenozoic is connected with the head of the old generation.
(3) When new pages (such as preread pages) are added to the buffer pool, they are added only to the old generation headers:
- If the data is actually read (preread successfully), it will be added to the head of the new generation
- If the data is not read, it will be eliminated from the buffer pool before the new generation of “hot data pages”
For example, the entire buffer pool LRU looks like this:
(1) The entire length of LRU is 10;
(2) The first 70% are the Cenozoic;
(3) The last 30% are the old generation;
(4) The new and old generations are connected end to end;
Suppose a new page with page number 50 is preread and added to the buffer pool:
(1) 50 will only be inserted from the old generation head, the old generation tail (also the whole tail) page will be eliminated;
(2) Assuming that page 50 will not actually be read, that is, a prefetch failure, it will be eliminated from the buffer pool before the next generation of data;
If page 50 is read immediately, for example SQL accesses row data in the page:
(1) It will be immediately added to the head of the new generation;
(2) The new generation of pages will be squeezed into the old generation, there will be no pages are really eliminated;
The improved buffer pool LRU is a good solution to the “prefetch failure” problem.
Voice-over: But don’t just throw out the baby for fear of failure and cancel the pre-read strategy. In most cases, the principle of locality is valid and the pre-read is effective. *
The new and old improved LRU still does not solve the problem of buffer pool contamination.
What is MySQL buffer pool contamination?
When a large amount of data is scanned in a batch by a SQL statement, all pages in the buffer pool may be replaced, resulting in a large amount of hot data being swapped out, and MySQL performance deteriorates sharply. This situation is called buffer pool contamination.
For example, if you have a user table with a large amount of data, execute:
select * from user where name like “%shenjian%”;
Although the result set may have only a small amount of data, this type of like cannot hit the index and must be scanned fully, which requires a large number of pages to be accessed:
(1) Add page to buffer pool (insert old generation header);
(2) Read the relevant row from the page (insert Cenozoic head);
Select * from ‘shenjian’ where ‘name’ = ‘name’ and ‘shenjian’ = ‘shenjian’;
(4)… Until all rows in all pages have been scanned…
In this way, all the data pages are loaded into the head of the new generation, but only accessed once, and the real hot data is swapped out in large numbers.
How can this kind of scanning of large amounts of data cause the problem of buffer pool contamination?
MySQL buffer pool added a “old generation residence time window” mechanism:
(1) Suppose T= residence time window of old generations;
(2) Pages inserted into the head of the old generation, even if accessed immediately, will not be immediately inserted into the head of the new generation;
(3) Only when the “access” is satisfied and the “stay time in the old generation” is greater than T, will be put into the new generation head;
To continue with the example, suppose the batch data scan, there are 51,52,53,54,55 and so on five pages will be visited in sequence.
If there is no “old generation residence time window” policy, these pages are visited in batches, will swap out a large number of hot data.
After adding the “old generation stay time window” strategy, pages that are loaded in a short period of time will not be inserted into the new generation head immediately, but those pages that are visited only once in a short period of time will be eliminated first.
Only when the old generation stays long enough and stays longer than T, will it be inserted into the head of the new generation.
What parameters in InnoDB correspond to the above principles?
There are three important parameters.
Parameters: the innodb_buffer_pool_size
If memory allows, dbAs often recommend that the buffer pool size be increased. The more data and indexes that can be stored in memory, the better the database performance will be.
Parameters: innodb_old_blocks_pct
Introduction: The ratio of the old age to the length of the whole LRU chain is 37 by default, that is, the length ratio of the whole LRU between the Cenozoic and the old age is 63:37.
Voiceover: If this parameter is set to 100, it degenerates to normal LRU.
Parameters: innodb_old_blocks_time
Introduction: The residence time window of the old generation is in milliseconds. The default value is 1000. It is inserted into the head of the new generation only when the two conditions of “being accessed” and “staying in the old generation for more than 1 second” are met.
conclusion
(1) Buffer pools are a common mechanism to reduce disk access;
(2) Buffer pools usually cache data in pages;
(3) Common buffer pool management algorithms are LRU, Memcache, OS, InnoDB all use this algorithm;
(4) InnoDB optimizes ordinary LRU:
- The buffer pool is divided into the old generation and the new generation. Pages in the buffer pool enter the old generation first. Pages are accessed before entering the new generation to solve the prefetch failure problem
- If pages are accessed and the residence time in the old generation exceeds the configured threshold, the pages enter the new generation to solve the problems of batch data access and large amounts of hot data elimination
Thinking is more important than conclusion.
The problem solved is more important than the solution.
