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

This article mainly explains "what is the use of the buffer pool of MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the use of the buffer pool of MySQL?"

Detailed explanation of MySQL buffer pool

Application system hierarchical architecture, in order to speed up data access, the most frequently accessed data will be placed in the cache to avoid accessing the database every time. Operating system, there will be a buffer pool (buffer pool) mechanism to avoid each access to the disk, in order to speed up data access. As a storage system, MySQL also has a buffer pool (buffer pool) mechanism to avoid disk IO every time the data is queried. Today, I'll talk to you about InnoDB's buffer pool.

What is the buffer pool cache for InnoDB? What's the use?

Cache table data and index data, load the data on the disk into the buffer pool, avoid disk IO every access, and play the role of accelerating access. Fast, so why not put all the data in the buffer pool?

Everything has two sides. Despite the volatility of data, the opposite of fast access is small storage capacity:

(1) the cache access is fast, but the capacity is small. The database stores 200 GB of data, and the cache capacity may be only 64 GB.

(2) memory access is fast, but the capacity is small. If you buy a notebook, the disk has 2T, and the memory may only be 16GB.

Therefore, you can only put the "hottest" data in the "nearest" place to minimize disk access.

How to manage and phase out buffer pools to maximize performance? Before introducing the details, let's introduce the concept of "pre-reading".

What is pre-reading?

Disk read and write is not read on demand, but read by page, reading at least one page of data at a time (usually 4K). If the data to be read in the future is in the page, you can save the subsequent disk IO and improve efficiency.

Why is pre-reading valid?

Data access usually follows the principle of "centralized read and write". If you use some data, you will most likely use nearby data, which is the so-called "locality principle". It shows that loading in advance is effective and can indeed reduce disk IO.

What does reading by page (4K) have to do with the buffer pool design of InnoDB?

(1) disk access page reading can improve performance, so buffer pools generally cache data by page.

(2) the pre-reading mechanism enlightens us that some pages that may be accessed can be added to the buffer pool in advance to avoid future disk IO operations.

What algorithm does InnoDB use to manage these buffer pages?

The easiest thing to think of is LRU (Least recently used). Voiceover: memcache,OS will use LRU for page replacement management, but the way MySQL is played is different.

How does traditional LRU manage buffer pages?

The most common game is to put the page in the buffer pool into the header of the LRU as the most recently accessed element, thus being eliminated at the latest. There are two more situations here:

(1) if the page is already in the buffer pool, only move to the LRU header is done, and no page is eliminated

(2) if the page is not in the buffer pool, you should not only "put" the LRU header, but also "eliminate" the LRU trailing page.

As shown in the figure above, if the LRU length of the management buffer pool is 10, the page number of the buffer pool is 1pm 3pm 5. , 40pl 7 page. Suppose the next data to be accessed is in the page with page number 4:

(1) the page with page number 50 is not in the buffer pool.

(2) put the page with page number 50 in the header of LRU, and eliminate the page with page number 7 at the end.

The traditional LRU buffer pool algorithm is very intuitive, OS,memcache and many other software are in use, why MySQL is so hypocritical, can not be used directly?

Here are two questions:

(1) invalid pre-reading

(2) buffer pool pollution

What is pre-reading invalidation?

Due to Read-Ahead, the page is put into the buffer pool in advance, but in the end, MySQL does not read data from the page, which is called read-ahead invalidation.

How to optimize the pre-reading failure?

To optimize the failure of pre-reading, the idea is:

(1) allow pages that fail to preread to stay in the buffer pool LRU as short as possible

(2) move the pages that are actually read to the header of the buffer pool LRU

To ensure that the thermal data that is actually read stays in the buffer pool as long as possible.

The specific methods are:

(1) divide the LRU into two parts:

New generation (new sublist)

Old age (old sublist)

(2) the end of the new and old generation is connected, that is, the tail of the new generation (tail) is connected to the head of the old generation (head).

(3) when a new page (such as a pre-read page) is added to the buffer pool, only the old generation header is added:

If the data is really read (pre-read successfully), it will be added to the head of the new generation.

If the data is not read, it will be eliminated from the buffer pool earlier than the "hot data pages" in the new generation.

If a new page with page number 50 is pre-read into the buffer pool:

(1) 50 will only be inserted from the head of the old generation, and the page at the end of the old generation (also the whole tail) will be eliminated.

(2) assuming that the page 50 will not be really read, that is, pre-reading fails, it will be eliminated from the buffer pool earlier than the new generation of data.

For example, if the batch data is scanned, five pages such as 51Jing 52jue 53jue 54je 55 and so on will be accessed in turn.

After adding the "old generation residence window" strategy, pages that are heavily loaded in a short period of time will not be immediately inserted into the new generation header, but will give priority to those pages that have been visited only once in a short period of time.

Parameter: innodb_buffer_pool_size

Description: configure the size of the buffer pool. When memory allows, DBA often recommends enlarging this parameter. The more data and indexes are put in memory, the better the performance of the database.

Parameter: innodb_old_blocks_pct

Introduction: the proportion of the length of the whole LRU chain in the old age is 37 by default, that is, the ratio of the length of the whole LRU between the Cenozoic and the old age is 63:37.

Voiceover: if you set this parameter to 100, it will degenerate to a normal LRU.

Parameter: innodb_old_blocks_time

Introduction: the residence time window of the old generation, the unit is millisecond, and the default is 1000, that is, the two conditions of "being visited" and "staying in the old age for more than 1 second" are met before they are inserted into the head of the new generation.

Summary

(1) buffer pool (buffer pool) is a common mechanism for reducing disk access.

(2) buffer pools usually cache data in pages (page)

(3) the common buffer pool management algorithm is LRU,memcache,OS,InnoDB uses this algorithm.

(4) InnoDB optimizes the ordinary LRU: the buffer pool is divided into the old generation and the new generation. The page that enters the buffer pool enters the old generation first, and the page is accessed before entering the new generation, in order to solve the problem of pre-reading invalidation. The page is accessed, and if the stay time in the old generation exceeds the configuration threshold, it will enter the new generation to solve the problem of batch data access and mass hot data elimination.

Ideas are more important than conclusions. What problem has been solved is more important than the plan.

Will the database memory be blown out?

Question: my host has only 100 gigabytes of memory. Now I have to scan a large table of 200 gigabytes. Will I run out of memory in the database host?

This problem is really worth worrying, the system OOM (out of memory) is no joke. However, when you think about it on the other hand, when you make a logical backup, don't you just scan the whole database? If this will eat up all the memory, wouldn't the logical backup be dead long ago?

Therefore, it seems to be no problem to do a full table scan of a large table. But what exactly is the process?

The influence of full table scan on server layer

Suppose that we are now going to db1 a 200G InnoDB table. T, perform a full table scan. Of course, if you want to save the scan results on the client side, you will use a command like this:

Mysql-h$host-P$port-u$user-p$pwd-e "select * from db1.t" > $target_file

As you already know, InnoDB's data is stored on the primary key index, so a full table scan is actually a direct scan of the primary key index of table t. Since there are no other judgment conditions for this query, each row found can be directly put into the result set and then returned to the client.

So where does this "result set" exist?

In fact, the server does not need to save a complete result set. The process of fetching and sending data is as follows:

Get a line and write it to net_buffer. The size of this block of memory is defined by the parameter net_buffer_length, which defaults to 16k.

Get the line repeatedly until the net_buffer is full and send it out by calling the network interface.

If the send is successful, clear the net_buffer, then move on to the next line and write to the net_buffer.

If the sending function returns EAGAIN or WSAEWOULDBLOCK, it means that the local network stack (socket send buffer) is full and goes into waiting. Do not continue sending until the network stack is writable again.

The flow chart for this process is shown below.

Figure 1 query result sending process

From this process, you can see:

In the process of sending a query, the maximum amount of memory used in MySQL is as large as net_buffer_length, and it will not reach 200GB.

It is also impossible for the socket send buffer to reach 200G (the default definition / proc/sys/net/core/wmem_default), and if the socket send buffer is full, the process of reading the data will be suspended.

In other words, the MySQL is sent while reading, and if the client receives it slowly, it will cause the MySQL server to execute the transaction longer because the result cannot be sent.

For example, the following state is the result that I deliberately asked the client not to read the contents of the socket receive buffer and then saw it on the server side show processlist.

Figure 2 Server sending blocking

If you see that the value of State has been at "Sending to client", it means that the network stack on the server side is full.

If the client uses the-quick parameter, the mysql_use_result method is used. This method is to read one line and process one line. You can imagine that the logic of a business is complex, and if the logic to be processed after each row of data is read is very slow, it will take a long time for the client to fetch the next row of data, as shown in figure 2.

Therefore, for normal online business, if the return results of a query will not be many, I recommend that you use the mysql_store_result interface to save the query results directly to local memory.

Of course, the premise is that the query does not return many results. Some students said that the client took up nearly 20 gigabytes of memory because they executed a large query, so they need to use the mysql_use_result interface instead.

On the other hand, if you see many threads in the "Sending to client" state in the MySQL you are responsible for maintaining, it means that you have to ask the business developer to optimize the query results and evaluate whether so many returned results are reasonable.

Setting the net_buffer_length parameter to a larger value is an option if you want to quickly reduce the number of threads in this state.

A state that is very similar to "Sending to client" is "Sending data", which is a question that is often misunderstood. A classmate asked me that I saw that the status of many query statements on the instances I maintained was "Sending data", but there was nothing wrong with looking at the network. Why did it take so long to Sending data?

In fact, the state change of a query statement looks like this (note: here, I omitted other extraneous states):

After the MySQL query statement enters the execution phase, first set the status to "Sending data"

Then, send the information related to the column of the execution result (meta data) to the client

Then continue the process of executing the statement

When the execution is complete, set the status to an empty string.

In other words, "Sending data" does not necessarily mean "data is being sent", but may be at any stage in the executor process. For example, you can construct a lock waiting scenario to see the Sending data status.

Figure 4 Sending data status

As you can see, session B is obviously waiting for a lock, and the status is displayed as Sending data.

That is, "Sending to client" is displayed only when a thread is in the state of "waiting for the client to receive the results"; if it is displayed as "Sending data", it simply means "executing".

Now you know that the results of the query are sent to the client in segments, so scanning the whole table and returning a large amount of data from the query will not burst the memory.

We all know the processing logic in the server layer, but how do we deal with it in the InnoDB engine? Will scanning the full table affect the engine system?

The influence of full table scan on InnoDB

Data pages in memory are managed in buffer pool, and Buffer Pool plays a role in accelerating updates in WAL. In fact, Buffer Pool has a more important role, which is to speed up queries.

Due to the WAL mechanism, when the transaction commits, the data page on the disk is old, so if there is a query to read the data page immediately, should redo log be applied to the data page immediately?

The answer is no. Because the result of the memory data page is up-to-date at this time, just read the memory page directly. You see, at this time, the query does not need to read the disk at all, and it is very fast to get the results directly from memory. Therefore, Buffer Pool also has the function of speeding up queries.

The acceleration effect of Buffer Pool on query depends on an important indicator, that is, memory hit rate.

You can check the current BP hit rate of a system in the show engine innodb status results. In general, a stable service online system, to ensure that the response time meets the requirements, the memory hit rate should be more than 99%.

When you execute show engine innodb status, you can see the word "Buffer pool hit rate", which shows the current hit rate. For example, the hit rate in figure 5 is 99.0%.

Fig. 6 basic LRU algorithm

InnoDB manages the LRU algorithm of Buffer Pool, which is realized by linked list.

In state 1 of figure 6, the header of the linked list is P1, indicating that P1 is the recently accessed data page; assume that there are only so many data pages in memory.

At this time, there is a read request to access P3, so it becomes state 2 and P3 is moved to the front.

Status 3 indicates that the data page visited this time does not exist in the linked list, so you need to apply for a new data page Px in Buffer Pool and add it to the header of the linked list. However, because the memory is full, new memory cannot be requested. Therefore, the memory of the data page Pm at the end of the linked list is emptied, the contents of the Px are stored, and then placed in the header of the linked list.

In effect, the data page Pm, which has not been accessed for the longest time, has been eliminated.

This algorithm looks fine at first glance, but will it be a problem if you consider doing a full table scan?

Suppose that according to this algorithm, we want to scan a 200G table, and this table is a historical data table, which is usually accessed by no business.

Then, if scanned according to this algorithm, all the data in the current Buffer Pool will be eliminated and stored in the contents of the data page accessed during the scan. In other words, Buffer Pool mainly contains the data of this historical data table.

For a library that is doing business services, this is not good. You will see that Buffer Pool's memory hit ratio drops sharply, disk pressure increases, and SQL statement response slows down.

Therefore, InnoDB cannot use this LRU algorithm directly. In fact, InnoDB has improved the LRU algorithm.

Fig. 7 improved LRU algorithm

In the implementation of InnoDB, the whole LRU linked list is divided into young area and old region according to the proportion of 5:3. What LRU_old points to in the figure is the first location of the old area, which is the 5 Universe 8 of the entire linked list. In other words, the 5ax 8 near the head of the linked list is the young region, and the 3ax 8 near the end of the linked list is the old region.

The execution process of the improved LRU algorithm is as follows.

In figure 7, state 1, to access the data page P3, because P3 is in the young area, move it to the head of the linked list and become state 2, just like the LRU algorithm before optimization.

After that, you need to access a new data page that does not exist in the current linked list, which still eliminates the data page Pm, but the newly inserted data page Px is placed at LRU_old.

Data pages in the old area should make the following judgment every time they are accessed:

If the data page has been in the LRU linked list for more than 1 second, move it to the header of the linked list

If the data page exists in the LRU linked list for less than 1 second, the position remains the same. The time of 1 second is controlled by the parameter innodb_old_blocks_time. The default value is 1000, in milliseconds.

This strategy is tailored to handle operations similar to full table scans. Taking the scan 200G historical data table just as an example, let's take a look at the operation logic of the improved LRU algorithm:

During the scanning process, data pages that need to be newly inserted are placed in the old area

There are multiple records in a data page, and the data page will be accessed many times, but because it is scanned sequentially, the interval between the first access and the last access of the data page will not exceed 1 second, so it will still be retained in the old area.

If you continue to scan the subsequent data, the previous data page will no longer be accessed, so there is no chance to move to the head of the linked list (that is, the young area) and will soon be eliminated.

As you can see, the biggest benefit of this strategy is that although Buffer Pool is also used in the process of scanning this large table, it has no impact on the young area at all, thus ensuring the query hit rate of Buffer Pool response to normal business.

Thank you for your reading, the above is the content of "what is the use of MySQL buffer pool?" after the study of this article, I believe you have a deeper understanding of the use of MySQL buffer pool, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.