Why do we need a Buffer Pool?
Although MySQL's data is stored on disk, you can't read data from disk every time, which would be extremely poor performance.
To improve query performance, then add a cache. So, when the data is taken out of disk, cache it in memory and the next time you query for the same data, read it directly from memory.
For this reason, the Innodb storage engine is designed with a Buffer Pool to improve the read and write performance of the database.
- When reading data, if the data exists in the Buffer Pool, the client reads the data in the Buffer Pool directly, otherwise it goes to the disk.
- When modifying data, the first step is to modify the page in the Buffer Pool where the data is located, then set its page as a dirty page, and finally the background thread writes the dirty page to disk.
What's in the Buffer Pool?
InnoDB divides the stored data into a number of pages, with the page as the basic unit of interaction between disk and memory, and the default size of a page is 16 KB. Therefore, the Buffer Pool also needs to be divided by page.
Buffer Pool contains many cache pages, at the same time each cache page has a description data, which can be called as control data, or description data, or metadata of the cache page. Control block data, control data includes "cache page tablespace, page number, cache page address, chain table node" and so on, the control block data is for better management of cache pages in Buffer Pool.
The control block also occupies memory space, it is placed at the top of the Buffer Pool, followed by the cache page, as shown below:
In addition to caching "index pages" and "data pages", the Buffer Pool also includes undo pages, insertion caches, adaptive hash indexes, lock information, and so on.
How the Buffer Pool is initialized when the database starts up
As soon as the database starts up, it goes to the operating system and requests an area of memory for the Buffer Pool according to the set Buffer Pool size, slightly larger.
Once the memory area is claimed, the database will divide the cache pages and their corresponding description data into Buffer Pools according to the default cache page size of 16KB and the corresponding description data size of 800 bytes or so.
Only this time, one by one in the Buffer PoolCached pages are emptyThe page corresponding to the data is read from the disk file and put into the cached page in the Buffer Pool only after the database is running and when the operation of adding, deleting, changing, or checking the data is performed.
Managing Buffer Pools
Managing Free Pages-Free Chained Tables
How to know which cached pages are empty
When the database is running up, the system will certainly keep performing the operation of adding, deleting, changing and checking, at this time you need to keep reading from the disk one by one data page into the Buffer Pool in the corresponding cache page to go, to cache the data, then you can later add, delete, change and check the data in the memory to perform.
But at this point in reading data pages from disk into the Buffer Pool in the cache page, necessarily involves a problem, that is, which cache page is free?
Because by default, data pages and cache pages on disk correspond to each other, both are 16KB, and one data page corresponds to one cache page. Data pages can only be loaded into the free cache pages, so MySql must know which cache pages are free in the Buffer Pool?
The MySQL database will design a Buffer Pool for afree linked listIt's abidirectional linked listdata structure, this free chain table, each node is a free cache page of the description of the data block address, that is, as long as you a cache page is free, then its description of the data block will be put into this free chain table.
At the beginning when the database is started, all the cache pages are free, because at this time it may be an empty database, not a single piece of data, so at this time all the description data block of the cache page, will be put into this free chain table.
Inside this free chain table is the control block of each cache page, as long as the cache page is free, then their corresponding control block will be added to the free chain table, each node will be bidirectional links to their own before and after the node, forming a bidirectional chain table.
In addition to that, this free chain table has a base node that references the head and tail nodes of the chain table, and it also stores how many nodes are currently in the chain table, that is, theHow many nodes of the control block are in the chain table, that is, how many free cached pages there are.
How are pages on disk read into Buffer Pool's cached pages?
- First, you need to get a control block from the free chain table, and then you can get the free cache page corresponding to this control block;
- Then you can read the data page on disk into the corresponding cache page, and at the same time write some relevant data into the control block, such as the data page belongs to the tablespace and so on.
- Finally just remove that control block from the free chain table.
How does MySQL know that a data page has been cached?
- In the execution of add, delete, change and check, it is certainly the first to see if this data page has been cached, if it is not cached go to the above logic, from the free chain table to find a free cache page, from the disk to read the data page to write to the cache page, write the control data, from the free chain table to remove this control block.
- But if the data pageAlready cachedSo actually the database will have a hash table data structure. So in fact, the database will have a hash table data structure, he will use the tablespace number + data page number, as a key, and then the address of the cached page as the value. when you want to use a data page, through the "tablespace number + data page number" as the key to the hash table to find out, if not on the If there is no data page, then read the data page, if there is already a data page, it means the data page has been cached.
MySQL introduces a data page cache hash table structure, that is, every time you read a data page into the cache, you will write a key-value pair in the hash table, the key is the tablespace number + data page number, and the value is the address of the cached page, so the next time you use this data page, you can read it directly from the hash table that it has been put into a cached page.
Managing Dirty Pages - Flush Link List
Why are there dirty pages?
If you want to update the data page will be in the Buffer Pool's cache page for you to perform the operation of additions, deletions and modifications directly in memory. mysql at this time, once the data in the cache page is updated, then the data in the cache page and the data in the data page on the disk, it is inconsistent, then it is said that this cache page is a dirty page.
How to Flush Dirty Pages Back to Disk
In order to quickly know which cache pages are dirty, the Flush link table is designed, which is similar to the Free link table, the nodes of the link table are also control blocks, the difference is that the elements of the Flush link table are all dirty pages.
With a Flush linked table, a background thread can traverse the Flush linked table and write dirty pages to disk.
Improving Cache Hit Ratio - LRU Chained Tables
The size of the Buffer Pool is limited, for some frequently accessed data want to stay in the Buffer Pool, and some rarely accessed data want to be eliminated at some point, so as to ensure that the Buffer Pool will not be full and can not be cached new data, but also to ensure that the commonly used data to stay in the Buffer Pool. The
What are cache hits?
Suppose now that there are two cache pages, and the data in one cache page, is often modified and queried, for example, out of 100 requests, 30 of them are querying and modifying the data in this cache page. So at this point we can say that in this case, the cache hit rate is high, why? Because in 100 requests, 30 times can operate the cache, do not need to load data from disk, the cache hit rate is relatively high.
The other data in the cached page is the data that has been modified and queried 1 time just after it has been loaded from disk to the cached page, and then none of the 100 requests after that have modified and queried the data in this cached page, then at this point we say that the cache hit rate is a bit low, because most of the requests may still need to go to disk to query for the data, and the data that they are going to be manipulating isn't in the cache.
So for both the above cached pages, when both the cached pages are full, the first cached page has a high hit rate, so surely the choice is to flush the second cached page to disk, thus freeing the cached page.
Therefore LRU chain table is introduced to determine which cached pages are not commonly used.Least Recently Used. The overall idea is that the nodes at the head of the chain table are the most recently used, while the nodes at the end of the chain table are the ones that have not been used for the longest time. Then, when there is not enough space, the nodes that have not been used for the longest time are eliminated, thus freeing up space.
A simple version of the LRU chain table
- When the accessed page is in the Buffer Pool, the LRU link table node corresponding to that page is directly moved to the head of the link table.
- When the accessed page is not in the Buffer Pool, in addition to putting the page into the head of the LRU link table, the node at the end of the LRU link table is also eliminated.
For example, in the following figure, suppose the length of the LRU link table is 5, and the LRU link table has pages 1, 2, 3, 4, and 5 from left to right.
If page 3 is accessed, move page 3 to the header because it is in the Buffer Pool.
If page 8 is accessed next, since page 8 is not in the Buffer Pool, you need to eliminate page 5 at the end and then add page 8 to the head.
There are two problems with the simple version of the LRU chain table
- prereading failure
- Buffer Pool contamination;
What is pre-reading failure?
MySQL's read-ahead mechanism: Programs are spatially localized, and data close to the currently accessed data has a high probability of being accessed in the future. Therefore, when MySQL loads a data page, it loads its neighboring data pages together in advance, in order to reduce disk IO.
However, these data pages that were loaded in advance may not be accessed, which is equivalent to this pre-reading is done in vain, this is the pre-reading failure.
If you use the simple LRU algorithm, you put the pre-read pages at the head of the LRU linked table, and you need to eliminate the pages at the end when the Buffer Pool runs out of space.
If these pre-read pages if they will not be accessed all the time, there will be a very strange problem, will not be accessed pre-read pages but occupy the LRU chain table in the front row of the position, and the end of the elimination of the page, may be frequently accessed pages, so that greatly reduces the cache hit rate.
How to solve
First of all, you can't remove the pre-reading mechanism for fear of pre-reading failure; the principle of spatial locality holds and is valid in most scenarios
And the best way to avoid the effects of pre-reading failures is toKeep the pre-read pages in the Buffer Pool as short as possible, and let the pages that are actually accessed move to the head of the LRU table, so that the hot data that is actually read stays in the Buffer Pool for as long as possible.。
Mysql divides the LRU chain table into two regions: the old region and the young region.
The young area is in the first half of the LRU table and the old area is in the second half.
With the division of these two districts, thePre-read pages just need to be added to the header of the old sectionThe page is inserted into the head of the young region only when the page is actually accessed. If the pre-read page is never accessed, it is removed from the old region so that theThe hotspot data in the young region will not be affected。
What is Buffer Pool Pollution?
This problem exists even with the above chained table dividing the OLD area of the YOUNG area.
When a certain SQL statement scans a large amount of data, because it is read, all this data will be placed in the head of the young area, then due to the limited space of the Buffer Pool, it is possible to replace all the pages in the Buffer Pool, resulting in a large amount of hot data being eliminated from the young area of the LRU, and when this hot data is accessed again, the When this hot data is accessed again, a large amount of disk IO will be generated due to cache misses, and MySQL performance will drop drastically, a process known as Buffer Pool Pollution.
How to solve
MySQL has added a new entry condition to the young zoneDetermination of the time to stay in the old area。
The first time Mysql accesses a cached page in the old area, it records the access time in its corresponding control block:
- If the time of a subsequent access is within a certain time interval from the time of the first access, then the cached page is not moved from the old area to the head of the young area;
- If the time of a subsequent access is not within a certain time interval from the time of the first access, then the cached page is moved to the head of the young area;
Can MYsql Cache Replace Redis?
- Redis caching supports many more scenarios.
- The actual work of the cached results are not only the results returned by the Mysql Select statement, it may be based on the results of the processing; and Mysql cache is the result of the Select statement
- Redis can provide access to richer data types such as List, Set, Map, ZSet
- The Redis cache hit rate is much higher than the Mysql cache.
- The way Mysql selects statements to be cached is not based on the frequency of access, but mainly based on whether the select statement contains dynamically changing values, there is no dynamic change in the value of the cache, such as the use of the now function will not be cached.Redis is the client's autonomy according to the frequency of access to the high cache.
- The rich data structure of Redis makes the cache reuse rate higher, for example, the cache is a List, you can access some of the elements of the List at will, such as paging requirements
- Mysql cache invalidation granularity is very coarse, as long as there is an update to the table, all caches involving the table (regardless of whether the update affects the cache or not) are invalidated, which makes the utilization of the cache will be very low, and only applies to the table with very few updates
- When there is a master-slave node and data is read from multiple nodes, the caches of each node will not be synchronized.3. Performance: The query performance of Redis is much higher than that of the Mysql cache, the main reason is that Redis is all in memory, but because of the hit rate problem of the mysql cache makes it impossible to put all of the mysql into memory.4. There are also a number of other reasons why Redis is so good. reasons
- Redis's storage structure facilitates read and write performance Redis is IO multiplexed to support greater throughput, Mysql's data characteristics make it pointless to make IO multiplexed in the vast majority of cases
- Data update will invalidate all caches of the table at the same time, which will make the data update slower.
Interview questions column
Java interview questions columnIt's online, so feel free to visit.
- If you don't know how to write a resume, resume projects don't know how to package them;
- If there's something on your resume that you're not sure if you should put on it or not;
- If there are some comprehensive questions you don't know how to answer;
Then feel free to private message me and I will help you in any way I can.