Let's talk about whether MySQL will cause OOM if there are too many data queries

This article brings you relevant knowledge about whether OOM will occur if there are too many MySQL data queries. I hope it will be helpful to everyone.

The host memory only has 100G. Now we need to scan the entire table of a 200G large table. Will the memory of the DB host be used up?

When doing a logical backup, isn't it just a scan of the entire database? If this would eat up all the memory, wouldn't the logical backup fail long ago?

So it seems that there should be no problem with a full table scan of the large table. Why is this?

The impact of full table scan on the server layer

Assume that we now want to perform a full table scan on a 200G InnoDB table db1.t Table scan. Of course, if you want to save the scan results on the client, you will use a command similar to this:

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

InnoDB data is saved on the primary key index, so the full table scan actually directly scans the primary key index of table t. Since this query statement has no other judgment conditions, each row found can be directly placed in the result set and then returned to the client.

So, where does this "result set" exist?

The server does not need to save a complete result set. The process of getting and sending data is as follows:

Get a row and write it to **"net_buffer". The size of this memory is defined by the parameter "net_buffer_length". The default is 16k. If the sending is successful, clear **"net_buffer", and then continue to fetch the next line and write it into "net_buffer"**

If the sending function returns **"EAGAIN" or "WSAEWOULDBLOCK"**, it means local The network stack (socket send buffer) is full and is waiting. Until the network stack is writable again, continue to send

Query result sending process

Visible:

One During the query sending process, the maximum internal memory occupied by MySQL is **"net_buffer_length"**, which will not reach 200G

• socket send buffer and cannot reach 200G (default Define /proc/sys/net/core/wmem_default), if the socket send buffer is full, the process of reading data will be suspended

• So MySQL actually "sends while reading" . This means that if the client receives slowly, the MySQL server will not be able to send the results, and the transaction execution time will become longer.

For example, the following state is the result seen on the server show processlist when the client does not read the content of "socket receive buffer".

Server-side sending blocking

#If you see that the State is always "Sending to client", it means that the server-side network stack is full.

If the client uses the –quick parameter, the mysql_use_result method will be used: read one line and process one line. Assume that the logic of a certain business is relatively complex, and if the logic to be processed after each row of data is read is very slow, it will cause the client to take a long time to fetch the next row of data, and the above result may appear.

Therefore, for normal online business, if a query returns few results, it is recommended to use the **"mysql_store_result"** interface to directly save the query results to local memory.

Of course, the premise is that the query returns not many results. If there are too many, the client will occupy nearly 20G of memory because a large query is executed. In this case, you need to use the "mysql_use_result" interface instead.

If you see many threads in "Sending to client" in the MySQL you are responsible for maintaining, it means that you want your business development students to optimize the query results and evaluate whether so many returned results are reasonable.

To quickly reduce the number of threads in this state, you can set **"net_buffer_length"** to be larger.

Sometimes, the status of many query statements on the instance is "Sending data", but there is no problem when checking the network. Why does Sending data take so long?

The status change of a query statement is as follows:

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

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

• Then continue the process of executing the statement

• After execution is completed, set the status to an empty string.

• That is, "Sending data" does not necessarily mean "sending data", but may be at any stage in the executor process. For example, you can construct a lock waiting scenario and see the Sending data status.

Reading the entire table is locked:

Sending data status

It can be seen that session2 is waiting Lock, the status is displayed as Sending data.

• Only when a thread is in the state of "waiting for the client to receive results" will "Sending to client" be displayed

• If it is displayed as "Sending data ", it just means "executing"

Therefore, the results of the query are sent to the client in segments, so the entire table is scanned and the query returns a large amount of data, and the memory is not Explode.

The above is the processing logic of the server layer. How is it processed in the InnoDB engine?

The impact of full table scan on InnoDB

One of the functions of InnoDB memory is to save the updated results and cooperate with redo log to avoid randomness Write disk.

The data pages of memory are managed in Buffer Pool (referred to as BP), and BP plays a role in accelerating updates in WAL.

BP can also speed up queries.

Due to WAL, when the transaction is committed, the data page on the disk is old. If there is a query to read the data page immediately, should the redo log be applied to the data page immediately?

unnecessary. Because at this time, the result of the memory data page is the latest, just read the memory page directly. At this time, the query does not need to read the disk, and the results are fetched directly from the memory, which is very fast. Therefore, Buffer Pool can speed up queries.

The acceleration effect of BP on queries depends on an important indicator, namely: memory hit rate.

You can check the current BP hit rate of a system in the show engine innodb status results. Generally speaking, for an online system with stable service to ensure that the response time meets the requirements, the memory hit rate must be above 99%.

Execute show engine innodb status, you can see the words "Buffer pool hit rate", which displays the current hit rate. For example, the hit rate in the picture below is 100%.

#If all the data pages required for the query can be obtained directly from the memory, that is the best, and the corresponding hit rate is 100%.

The size of the InnoDB Buffer Pool is determined by the parameter **"innodb_buffer_pool_size"**. It is generally recommended to set it to 60%~80% of the available physical memory.

About ten years ago, the data volume of a single machine was hundreds of G, and the physical memory was several G; now, although many servers can have 128G or higher memory, the data volume of a single machine is Reached T level.

So, it is common for **"innodb_buffer_pool_size"** to be smaller than the disk data amount. If a Buffer Pool is full and a data page needs to be read from the disk, an old data page must be eliminated.

InnoDB memory management

The least recently used (Least Recently Used, LRU) algorithm is used to eliminate the data that has not been used for the longest time.

Basic LRU algorithm

The LRU algorithm used by InnoDB to manage BP is implemented using a linked list:

• state1, the head of the linked list is P1, indicating P1 It is the data page that has been accessed recently

• At this time, a read request accesses P3, so it becomes state 2, and P3 is moved to the front

• State 3 indicates that the data page accessed this time does not exist in the linked list, so a new data page Px needs to be applied for in BP and added to the head of the linked list. But because the memory is full, new memory cannot be requested. So the Pm data page memory at the end of the linked list is cleared, the content of Px is stored, and placed at the head of the linked list

In the end, the data page Pm that has not been accessed for the longest time is eliminated.

What will happen if we want to do a full table scan at this time? You want to scan a 200G table, which is a historical data table and no business usually accesses it.

Then, scanning according to this algorithm will eliminate all the data in the current BP and store the content of the data page accessed during the scanning process. In other words, BP mainly stores data from this historical data table.

For a library that is doing business services, this is not acceptable. You will see that the BP memory hit rate drops sharply, the disk pressure increases, and the SQL statement response slows down.

So, InnoDB cannot use the original LRU directly. InnoDB optimizes it.

Improved LRU algorithm

InnoDB divides the linked list into New area and Old area according to the ratio of 5:3. In the figure, LRU_old points to the first position of the old area, which is 5/8 of the entire linked list. That is, 5/8 near the head of the linked list is the New area, and 3/8 near the end of the linked list is the old area.

Improved LRU algorithm execution process:

State 1, you need to access P3. Since P3 is in the New area, it is the same as LRU before optimization, so move it to the head of the linked list => State 2

After that, you need to access a new data page that does not exist in the current linked list. At this time, the data page Pm is still eliminated, but the newly inserted data page Px is placed at **"LRU_old"**

The data page in the old area must make the following judgment every time it is accessed:

If the data page exists in the LRU linked list for more than 1s, move it to Linked list header

If the data page exists in the LRU linked list for less than 1s, the position remains unchanged. 1s is controlled by the parameter **"innodb_old_blocks_time"**, the default value is 1000, unit ms.

This strategy is tailored to handle operations similar to full table scans. Or scan the 200G historical data table:

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

5. There are multiple records in one data page, this The data page will be accessed multiple times, but due to sequential scanning, the time interval between the first access and the last access of this data page will not exceed 1 second, so it will still be retained in the old area

6. If we continue to scan subsequent data, the previous data page will not be accessed again, so there will never be a chance to move to the head of the linked list (New area), and it will be eliminated soon.

It can be seen that the biggest benefit of this strategy is that in the process of scanning this large table, although BP is also used, it has no impact on the young area at all, thus ensuring that the Buffer Pool responds to normal business queries. Hit rate.

Summary

MySQL adopts the logic of calculating and issuing at the same time. Therefore, for query results with a large amount of data, the complete data will not be saved on the server side. Result set. Therefore, if the client does not read the results in time, it will block the MySQL query process, but it will not burst the memory.

As for the InnoDB engine, due to the elimination strategy, large queries will not cause memory to skyrocket. Moreover, because InnoDB has improved the LRU algorithm, the impact of full table scan of cold data on the Buffer Pool can also be controlled.

Full table scan still consumes IO resources, so it is still not possible to directly perform full table scan on the main database online during peak business periods.

Recommended learning: mysql video tutorial

The above is the detailed content of Let's talk about whether MySQL will cause OOM if there are too many data queries. For more information, please follow other related articles on the PHP Chinese website!