In order to speed up data access, the most frequently accessed data is stored in the cache to avoid accessing the database every time.
The operating system will have a buffer pool mechanism to avoid accessing the disk every time, so as to speed up data access.
As a storage system, MySQL also has a buffer pool mechanism to avoid disk I/O every time data is queried.
Today, LET’s talk about InnoDB buffer pool.
What does InnoDB’s buffer pool cache? What’s the use?
Cache table data and index data, load data on disk to buffer pool, avoid disk I/O for each access, accelerate access.
Fast, so why not put all the data in the buffer pool?
There are two sides to every coin. Data fragility aside, fast access is the opposite of small storage capacity:
(1) Cache access is fast, but the capacity is small. The database stores 200G of data, and the cache capacity may only be 64G;
(2) Fast memory access, but small capacity, buy a notebook disk has 2T, memory may only 16G;
Therefore, only the “hottest” data can be placed in the “nearest” place to “minimize” disk access.
How do you manage and phase out buffer pools to maximize performance?
Before getting into the details, let me introduce the concept of “preread”.
What is preread?
Disk read and write is not read on demand, but read by page. At least one page of data (generally 4K) is read at a time. If the data to be read in the future is in the page, subsequent DISK I/O can be saved and efficiency can be improved.
Why does prefetch work?
Data access generally follows the principle of “centralized read and write”, where some data is used and more likely nearby data is used. This is known as the “locality principle”, which indicates that preloading is effective and can actually reduce disk I/O.
How does InnoDB buffer pool design relate to reading by page (4K)?
(1) Disk access reads per page can improve performance, so buffer pools generally cache data per page;
(2) The read-ahead mechanism informs us that some “likely to be accessed” pages can be added to the buffer pool in advance to avoid future disk I/O operations;
What algorithm does InnoDB use to manage these buffer pages?
LRU(Least recently used)
Voiceover: Memcache, OS will use LRU for page replacement management, but MySQL plays differently.
How does a traditional LRU manage buffer pages?
The most common way to play this is to put the pages that are entered into the buffer pool in the head of the LRU as the most recently accessed element, thus being the last to be eliminated. Here are two cases:
(1) The page is already in the buffer pool, so only the “move to” LRU header is performed, and no page is eliminated;
(2) the page is not in the buffer pool. In addition to the action of “putting” the LRU header, we should also do the action of “eliminating” the LRU tail page;
As shown above, suppose the LRU length of the management buffer pool is 10 and the buffered page numbers are 1,3,5… Pages 40 and 7.
Suppose that the next data to be accessed is in the page number 4:
(1) the page number 4 is already in the buffer pool;
(2) Put the page with the page number of 4 in the head of the LRU, and no page will be eliminated;
Voice-over: To reduce data movement, LRU is generally implemented with linked lists.
Suppose that the next data to be accessed is in the page number 50:
(1) the page with page number 50 is not in the buffer pool;
(2) Put the page with the page number of 50 to the head of LRU, and eliminate the page with the page number of 7 at the tail;
The traditional LRU buffer pool algorithm is very intuitive, OS, memcache and many other software are used, MySQL why so sentimental, can not directly use it?
There are two questions:
(1) Prefetch failure;
(2) Buffer pool pollution;
What is prefetch failure?
Because of read-ahead, the page is put into the buffer pool Ahead of time, but MySQL does not Read the data from the page. This is called read-ahead failure.
How to optimize prefetch failure?
To optimize the prefetch failure, the idea is:
(1) Make the page that fails to read stay in the buffer pool LRU as short as possible;
(2) Make the pages that are actually read move to the head of the buffer pool LRU;
To ensure that hot data that is actually read stays in the buffer pool as long as possible.
Specific methods are as follows:
(1) LRU is divided into two parts:
-
New Sublist
-
Old Sublist
(2) The Tail of the Cenozoic is connected to the head of the Old;
(3) When a new page (such as a preread page) is added to the buffer pool, only the old header is added:
-
If the data is actually read (preread successfully), it is added to the head of the new generation
-
If the data is not read, it will be flushed out of the buffer pool earlier than the “hot data pages” of the new generation
For example, the entire buffer pool LRU looks like the figure above:
(1) The whole LRU length is 10;
(2) The first 70% are Cenozoic;
(3) The latter 30% are old generation;
(4) The New Old Generation was placed end to end;
If a new page with page number 50 is preread to the buffer pool:
(1) 50 will only be inserted from the old generation header, the old generation tail (also the whole tail) will be eliminated;
(2) Assume that the page 50 will not actually be read, that is, the prefetch fails and it will be flushed out of the buffer pool before the new generation data;
If page 50 is immediately read, for example SQL accesses row data within the page:
(1) It will be immediately added to the head of the new generation;
(2) The pages of the new generation will be crowded into the old generation, and no pages will be really eliminated;
The improved buffer pool LRU can solve the problem of “prefetch failure”.
Voice-over: But don’t throw out the baby with the bathwater. Cancel the prefetch strategy for fear of prefetch failure. In most cases, the locality principle is valid and prefetch works.
The new and improved LRU still cannot solve the problem of buffer pool contamination.
What is MySQL buffer pool contamination?
When a SQL statement needs to scan a large amount of data in batches, it may cause all pages in the buffer pool to be replaced, resulting in a large amount of hot data to be replaced and MySQL performance to deteriorate dramatically. This situation is called buffer pool pollution.
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 in full, which requires accessing a large number of pages:
(1) Add page to buffer pool (insert old generation header);
(2) Read the relevant row from the page (insert the new generation head);
(3) Compare the name field in row with the string shenjian, and add it to the result set if it meets the conditions;
(4)… Until all rows in all pages have been scanned…
In this way, all data pages are loaded to the head of the new generation, but only accessed once, and the real hot data is swapped out in large numbers.
How does this sweep of large amounts of data lead to buffer pool contamination?
MySQL buffer pool added a “old generation dwell time window” mechanism:
(1) Suppose T = residence time window of old generation;
(2) Pages inserted into the head of the old generation will not be placed into the head of the new generation even if they are accessed immediately;
(3) Only when “visited” and “stay time in old generation” is greater than T, will be put into the head of the new generation;
For example, if a batch data scan is performed, five pages, 51,52,53,54,55, etc. will be visited in sequence.
Without the “old generation window” policy, these pages, which are visited in batches, will swap out a lot of hot data.
With the “old generation stay time window” policy, pages loaded in a short period of time will not be immediately inserted into the head of the new generation, but those pages visited only once in a short period of time will be preferentially eliminated.
And only after staying in the old generation long enough, staying longer than T, will it be inserted into the new generation head.
What parameters in InnoDB correspond to the above principles?
There are three important parameters.
Parameters: the innodb_buffer_pool_size
Configure the buffer pool size. If memory allows, dbAs often recommend increasing this parameter. The more data and indexes are placed in memory, the better the performance of the database.
Parameters: innodb_old_blocks_pct
Introduction: The ratio of the old generation to the length of the whole LRU chain is 37 by default, that is, the ratio of the meso-Cenozoic to the old generation in the whole LRU chain is 63:37.
Voiceover: If you set this parameter to 100, it degrades to normal LRU.
Parameters: innodb_old_blocks_time
Introduction: The residence time window of the old generation is millisecond, the default is 1000, that is to say, it will be inserted into the head of the new generation only when the two conditions of “being visited” and “staying in the old generation for more than 1 second” are met.
conclusion
(1) Buffer pools are a common mechanism for reducing disk access;
(2) Buffer pools usually cache data in the unit of page;
(3) The common management algorithm of buffer pool is LRU, memcache, OS, InnoDB all use this algorithm;
(4) InnoDB optimizes ordinary LRU:
-
The buffer pool is divided into old generation and new generation. Pages that enter the buffer pool enter the old generation first and are accessed before entering the new generation, so as to solve the problem of prefetch failure
-
Pages are accessed, and the residence time of the old generation exceeds the configured threshold, then enter the new generation to solve the problem of batch data access and large amount of hot data elimination
Ideas are more important than conclusions.
What problem is solved? More important than the solution.
The architect’s Path– Share technical ideas
Related recommendations:
How to Write a Cache
The article 6 of the shell tips, let the script more professional | 1 minute series”
The MyISAM and InnoDB index difference series | 1 minute
MySQL deadlock analysis with two tools
Research: What is the difference between a buffer pool and a cache?
Voiceover: Long article read and forward low, why?