This is the second day of my participation in the November Gwen Challenge. Check out the details: the last Gwen Challenge 2021
How to load disk data into mysql
Generally, when updating a piece of data, the data is stored in disk at the beginning and then loaded into mysql when it is used. The stored data is called a table in logical concept, and the data is stored in disk in the form of data pages in physical level, so the data loaded into mysql is called cache pages.
Each cache page has a corresponding description, which stores some information related to the metadata of the cache page. The description information can be used to quickly locate the cache page. Of course, the cache page pointed to by the initial description is free and has no data.
When an UPDATE statement is executed, the database name and table name in the SQL statement can be resolved to know which tablespace the data page to load. The consistency algorithm can also be used to obtain the data page number based on the SQL statement itself. You can get the corresponding page address from the data page cache (essentially a hash table), which can be directly used to locate the page in the InnoDB buffer pool. If the data page has not been loaded, the cache page address does not exist, and the data page needs to be loaded from disk into mysql.
1.2. How do I know which cached pages are free? The free linked list data structure is introduced, which combines the description information of the cache pages that have not been used with a bidirectional circular linked list. When needed, a node is unloaded to store the data page information.
Data is loaded into the cache pages right now, already has data, cache pages related to the change of information are sure to write description information, and now have data for page caching, can no longer stay in the free list, you need to cache pages corresponding to the description information node from the free list to remove, transfer to the lru list
2. Perform update in InnoDB
The data has been loaded from disk into the buffer pool. To perform the update operation, lock the row to be updated, write a copy of the original data to undo log for possible rollback, and perform the update operation to update the cached page data. In this case, the data on the cached page is not consistent with the data on the disk. Such cached pages are called dirty pages
How do I know which cached pages in the buffer pool are dirty? We designed a flush list, which is a list of cached pages whose data has been updated in the buffer pool. The descriptions of these cached pages are added to the Flush list. Flush list (lRU)
3. Dirty pages are flushed when the cache pool memory is insufficient
InnoDB buffer pool memory is about to run out. If InnoDB buffer pool memory is about to run out, InnoDB buffer pool memory is about to run out. If InnoDB buffer pool memory is about to run out, InnoDB buffer pool memory is about to run out. If the cached pages are in both the lRU bottom node and the flush list, the dirty pages need to be flushed first, and then the cached pages need to be freed to ensure that the data changed by the transaction can be stored. If the cached page is not flush, free the cached page and add the description to the free list.
4. Problems caused by mysql prefetch mechanism and optimized LRU linked list
Mysql to proofread mechanism, when a data page is loaded into the buffer pool, probably by the other irrelevant data pages are loaded into the buffer, which incidentally loaded into memory data pages, they are visit frequency is very low, but because of the characteristic of the lru list, new always preferred by the lru head, As a result, the cache pages with low access frequency are placed at the top of the list. As a result, when the free list is not enough, the LRU list instead flusher the cache pages that were originally accessed more frequently but were pushed to the end of the LRU list.
The optimized LRU linked list mainly introduces the idea of separating hot and cold data to solve the problems caused by mysql prefetch mechanism. The LRU linked list is divided into hot data area and cold data area. The hot data area mainly stores the cached pages with high access frequency, and the cold data area stores the cached pages with low access frequency. When loading the data from disk to lru list, the first page will be loaded into the cache data directly into the first cold chain header, if 1000 ms (by default, configurable) after cold data cached page is accessed again, where you think these 1000 ms after accessed cache pages, in the near future may also be accessed, can think they are hot data. The cache pages are moved from the cold list to the hot list head, separating the hot data from the cold heap.
At this time, if you want to load other data pages and find that the buffer pool memory is not enough, in fact, there will always be a scheduled task started by a thread in the background, constantly from the end of the LRU linked list to the cache page to the disk and release the cache page, lRU linked list cold and hot data separation design, This ensures that the cached pages retrieved by the scheduled task from the end of the LRU list are infrequently accessed data, minimizing the impact on performance.
Five, think
5.1 What are data pages and cache pages? How do I know which cache pages are free and which can be purged?
- On the logical level, data is usually presented in the form of tables, but on the physical level of storage, data stored on disk is stored in the form of data pages, one page at a time, each data page has multiple rows of data
- InnoDB storage engine, through the free linked list can be viewed in the current memory, where the cache page is free, can be directly used to store new data; If the free list runs out, the Flush list records which caches are dirty and can be flushed.
5.2 What is the mysql prefetch mechanism? Under what circumstances will it be triggered? What kind of scenarios does mysql design the prefetch mechanism for?
The mysql prefetch mechanism is designed to improve performance. After a data page is loaded into the memory, other data pages are also loaded into the memory. This is because other data pages may be used in subsequent operations.
The trigger time of the prefetch mechanism depends on the parameter configuration:
- Innodb_read_ahead_threshold: 56(default)
- Innodb_random_read_ahead: OFF(default)
The first parameter: the current page load data in the data area of page (data area to store multiple data) in order already visited 56 data page, mysql will automatically at this time to pay a visit to the next data analysis may be about to the data, this will trigger the current data in a data area all loaded into memory;
The second parameter: the current load in the data page of data area, if the order and visited 13 consecutive data page, the mysql will automatically analysis may be in order to load all the data from the current data area into memory, in the current data area of the mechanism of trigger to proofread all data pages loaded into memory at a time.
The prefetch mechanism is generally designed to cope with full table scan and paging query operations, which have a large number of data pages loaded into the cache. In order to infrequently load data from disk, the data on disk is preloaded into memory to improve performance.
5.3. Similar to redIS, where hot and cold data are shared in memory, how to optimize the design of REDIS cache by using lRU linked list to solve the prefetch mechanism?
Scene: In high concurrency scenarios, redis cache data due to some force majeure factors, large area outage cannot recover completely, the new system online midway down for the first time, in these cases if restart redis even if successful, the redis memory is also no data, in face of the upcoming subsequently levels must even million call flow, Redis will be overwhelmed and the incoming traffic will definitely hit mysql and cause mysql to hang.
Optimization: During the normal operation of the daily system, it is possible to calculate which data has the highest number of visits and store it. For example, a copy of each visit data is reported to MQ, and then the hot data information of the top number of visits can be calculated by some real-time computing framework such as Storm. Their primary key information is stored in such as Hbase\Zookeeper, and then every time the system starts, the primary key of hot data information stored in Hbase\Zookeeper is first found, and then the data is queried in the database and written in Redis as cache preheating. Loading the most frequently accessed data into redis memory is basically resistant to high concurrent access.
5.4 When the memory is extremely insufficient, it may occur that every time a data page is loaded, a cache page will be flushed to disk, resulting in double IO performance problem. How to consider optimizing mysql memory parameters to avoid performance loss in this situation?
The main reason is that the memory size and data Settings of the buffer pool are inappropriate, which do not meet the concurrent pressure of the current service system. The buffer pool is set to a small size, resulting in insufficient free cache pages and frequent flush flush. In addition, the buffer pool must be locked for each thread to operate. Although the buffer pool execution is efficient, the actual concurrent serial execution of multiple threads still affects the performance.
Innndb_buffer_pool_size can be configured to increase innoDB memory, and innodb_buffer_pool_instances can be configured to configure several buffer pool instances to load balance the multi-thread concurrency pressure. Improved overall performance of innoDB storage engine.