Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to solve the problem when the MySQL cache page is full". In the daily operation, I believe many people have doubts about how to solve the problem when the MySQL cache page is full. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubt of "how to solve the problem when the MySQL cache page is full." Next, please follow the editor to study!

1. What if the cache page is full?

However, there is always a time when the free linked list is free, that is, when the cache pages are full, there is no free cache to load data pages on disk?

At this point, we thought, what should we do? Do you want to eliminate some of the cache pages, that is, empty them, and then load the new data pages into the cache pages?

How to eliminate it?

1.1.The LRU elimination algorithm 1.1.1, strategy:

MySQL uses the LRU elimination algorithm, LRU, or least recently use, which is the least recently used.

The strategy is to add the cache page used to the header of the LRU linked list, as long as it is modified or queried, it will be moved to the header of the linked list, and finally eliminate the tail of the LRU.

1.1.2. Existing problems:

1. MySQL has a pre-read mechanism: when a data page is loaded from disk, it is possible to load adjacent data pages into the cache page as well.

What's the problem with this?

If you look at the picture above, for example, the space size is 4. Originally, ABC is often accessed, but now if you want to add the two dotted data pages, you must eliminate one, then you will definitely eliminate C, and the frequently visited ones will be eliminated, leaving the adjacent data pages without access.

Then it is necessary to understand the pre-reading mechanism of MySQL.

①, controlled by parameter innodb_read_ahead_threshold, the default is 56. This parameter indicates that if multiple data pages in an area are accessed sequentially, which is 56, the read-ahead mechanism will be triggered to load all the data pages in the next area into the cache page.

②, which is controlled by the parameter innodb_random_read_ahead. The default is off. This parameter indicates that if 13 consecutive data pages of an area are cached, the read-ahead mechanism will be triggered to load all the pages in that area into the cache page.

2. Full table scan

If it is a full table scan, the whole table will be loaded into the buffer pool, it is possible to squeeze the frequently accessed LRU list to the back, it is possible to be eliminated.

How to optimize it?

Since there are frequently accessed data and infrequently accessed data, can you partition them in the LRU linked list and manage these two pieces of data separately?

1.2.Design LRU linked list based on the idea of hot and cold data separation.

All the LRU linked list is divided into two parts, the hot and cold ratio is controlled by the innodb_old_blocks_pct parameter, the default is 37, that is to say, the cold data accounts for 37%.

1.2.1, strategy

The data page loaded for the first time is placed directly in the head of the cold data area.

Controlled by parameter innodb_old_block_time, default is 1000 milliseconds.

1.2.2, performance optimization

If the data located in the hot data area is accessed, should the header of the hot data area be loaded immediately?

No matter the side of the chain is inconvenient, frequent movement is certainly undesirable. According to the MySQL, only the data in the last three parts of the hot data is accessed will it be moved to the head of the linked list.

1.3. Brushing mechanism of cold data

The previous discussion is that the cache is full before eliminating the tail data to be flushed into the disk, in fact, it is not necessary to wait for it to be full. There is a thread in the background that runs a scheduled task that flushes to the disk at regular intervals and then empties the cache pages and adds them to the free linked list.

Don't forget that it's not only cold data areas that are flushed into disk, but also flush linked lists, which are executed when MySQL is not very busy, and then removed from flush and LRU linked lists.

At this point, the study on "how to solve the problem when the MySQL cache page is full" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.