Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand the Buffer Pool of MySQL". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

What is buffer pool?

When we use mysql, such as the very simple select * from table; statement, the specific query data is actually implemented in the storage engine. We all know that mysql data is actually placed on the disk. If each query is queried directly from the disk, it is bound to affect performance, so the data must be taken out of the disk first, and then put in memory, and the next query will be fetched directly from memory. But mysql is often not the only process running in a machine, many processes need to use memory, so there will be a special area in mysql to deal with this data, this area specially prepared for mysql is called buffer pool.

The workflow of buffer pool

Let's take the query statement as an example: when querying, we will first go to buffer pool (memory) to see if there is a corresponding data page, and if so, we will directly return 2: if there is no corresponding data page in buffer pool, we will look it up in the disk, and if the corresponding data is found in the disk Then a copy of the page's data will be directly copy to buffer pool and returned to the client 3: next time the same query will come in and directly search buffer pool to find the corresponding data and return it.

When you see here, I believe you should have a general understanding of buffer pool, and do you feel a little bit cached? of course, buffer pool is not as simple as caching, and the internal structure is still more complex, but it doesn't matter. Let's move on.

Buffer pool data management

The basic unit of data management

After all, buffer pool is a kind of memory management, of course, the data is not managed according to a sql statement, but according to the data page. The default data page of the innodb engine is 16kb, while when buffer pool starts, it is 128m by default, so there are 8192 data pages. The data management of the disk is also managed in terms of data pages, so every time you look up data, you will find the corresponding data page in the disk if it is not in buffer pool,buffer pool, and then copy it to buffer pool and return it to the client.

Free linked list

Normally, buffer pool must be filled back from the first data page, write back one by one, and append it directly each time. The following figure (the yellow part indicates that the data has been written)

However, in the actual production environment, this is not the case. We not only have query operations, but also delete, modify and other operations, and the data that has been written into buffer pool may not always be valuable. Some data is not needed, and the corresponding data pages need to be released, so it will cause the data of buffer pool to be discontinuous.

In this case, how to find effective free data page space to store data? The most intuitive way is to look back from the first page traversing one by one, to find free data pages, this method is feasible, but very affect efficiency, so mysql in dealing with this problem using the free linked list to manage idle data pages.

You can take a look at the structure of the free linked list.

The free linked list has a base node, which records the unique flag of the free linked list, the address of the tail node of the linked list, and the total length of the linked list.

There will be a lot of control blocks behind the base node, and the control block itself is very small, only storing pointers to free data pages, so buffer pool can find it directly with the free linked list when looking for free data pages.

As long as a page of data is free, append the address of the page directly to the free linked list.

Flush linked list

Of course, just using the free linked list will not solve all the problems, for example: we are executing update table test set field_a = 1 At that time, we first modify the corresponding data pages in buffer pool, and then update the corresponding data pages on disk. (of course, a data consistency problem is involved here. Mysql is solved by redo log, which is not within the scope of our article.) when we synchronously modify the corresponding modified data pages in buffer pool to disk, this process is called "brushing dirty", and there is a certain strategy for brushing dirty. Can be used

Select @ @ innodb_flush_log_at_trx_commit

To view the dirty brushing policy.

We generally do not set real-time write, which affects performance, so it is generally delayed, so it gives rise to a question: how does mysql find the modified dirty data in buffer pool? Here we use the flush linked list, which is actually similar to the free linked list

What is maintained on the flush linked list are pointers to dirty data pages. When brushing dirty, just go through the flush linked list to clean it.

Lru linked list

Buffer pool has a certain space limit, the default is 128m, there will always be a time when the space is full, so there is an elimination mechanism for data pages, and the elimination mechanism is lru (the least used recently).

The principle of lru is actually very simple. The used data page is moved directly to the head of the linked list, and then the data page at the end of the linked list is eliminated directly after the buffer pool is full.

Optimization of lru linked list

In fact, there are some problems with simple lru linked lists. For example, in the course of our work, we may use statements such as select * from test to do some requirements such as brushing data. If the test table is very large, it is very likely to fill up the buffer pool at once, eliminate all the previous data pages, and then when the rest of the data is online and the business is executed normally. Will come back to re-slowly eliminate the data pages occupied by select * from test, which will greatly affect online performance.

Therefore, in view of the above problems, mysql's buffer pool is optimized on the basis of lru.

Buffer pool's lru linked list divides the data into hot and cold blocks at a ratio of about 5:3, and each new page is written to the cold area.

However, if this is the case, there will never be any data in the hot data area, so the time of writing in the cold data area will be recorded separately when it is written in the cold data area. if the interval is greater than 1s the next time the data area is accessed, it will be put into the hot data area. in this way, a large amount of innocent data will not be eliminated. So when we execute a statement like select * from test to refresh the script, it only takes up space for cold data, not hot data.

This is the end of "how to understand the Buffer Pool of MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.