MySQL connection timeout

On a MySQL server with heavy load, you may occasionally see connection timeout errors, such as: Can't connect to MySQL server on 'mydb' (110 ). If you have multiple connection requests at the time, you will find other connections are okay. This type of problem is very conspicuous at the beginning, and can be ignored for a long time (Note: The number of times is not large), similar to the occurrence rate of one in a thousand, but when the server load is constantly increasing, the frequency may increase.

If you time the connection, you will find that the connections are generally close to 3-9 seconds. This time is sometimes quite strange. I met it once many years ago. At that time, the database Request connection was reset and the SYN packet was discarded. One SYN packet is discarded in 3 seconds, and two are discarded in 9 seconds. If you have encountered a similar situation, it may be that your network is faulty or your database server request listening queue overflows. You can run the netstat-s command to check the situation, the system may return a message similar to the following: the Socket of the listening queue experienced 38409 overflows, And the 38409 SYN packets were discarded. This means that the kernel cache of the listening Socket overflows, the SYN packet will have to be discarded-MySQL does not accept the connection as soon as needed.

If this happens, you can consider two tuning points.
1. Linux kernel: net. ipv4.tcp _ max_syn_backlog. this parameter is used to set the cache size of All Socket kernels. The default value of my system is 2048. Other versions may be different. If your connection concurrency is large, you may need to increase this value to about 8192. I will introduce the matching details below.

2.
MySQL parameter: back_log. The default value is 50. You may need to set this value to 1000 or higher. At the same time, you may increase the value of the net. core. somaxconn Kernel Parameter, which is used to set the maximum depth of the listening queue. In my own kernel, this parameter is set to 128, which is usually low.

Now let's take a deeper look at this issue. First, let's see how MySQL accepts connections. A main thread will accept all requests to listen to Socket connections. When a new connection comes, the main thread will create a new Socket for the new connection, and create a new sub-thread or take a sub-thread from the cache to process the connection. From the perspective of multi-threaded MySQL Network Communication Base, multi-core is advantageous, but it is useless for the main thread. Generally, the main thread receives the connection quickly. However, if the main thread waits for mutual exclusion or to start a new thread, the listening queue may overflow. Let's take a look. If a database can normally accept 1000 connections per second, this value is very high, but you may encounter a higher value. Because of the random arrival of connection requests, you may see 3000 concurrent connections. In this case, the default back_log (50) can only support 17 ms. If the processing of the main thread is stuck for more than 17 ms in some places, some SYN packets will be discarded.

I recommend that you adjust the values of tcp_max_syn_backlog and back_log to Support 2 seconds of connection requests (Note: connection requests do not time out in 2 seconds ). For example, there are 100 connections per second under normal circumstances. If the peak value is 3 times normal, it is 300 connections/second, which means the parameter mentioned above must be set to at least 600. (Note: 300 connections/S * 2 seconds = 600 connections/s)

Setting the parameter to support requests of more than 2 seconds is of little significance, because the client will send a new connection request after not receiving a response within 3 seconds.

In addition, if you have created 1000 MySQL connections per second, you may be a bit too late. After all, creating and destroying 1000 connections requires a lot of resources. Consider using persistent connections or connection pools. At least that is the case where most connections are created by applications.

 

This article is from "IT milk"