Turn MySQL connection timeout

On a heavily loaded MySQL server, sometimes you may occasionally see some connection timeout errors, such as: Can ' t connect to MySQL server on ' MyDB ' (110). If you have more than one connection request at that time, you will find that the other connection is not a problem. This kind of problem is very inconspicuous at first, and can be almost negligible for a long time (note: Not many), similar to the incidence of one out of 10,000, but when the server load continues to increase, the frequency will be increased.

If you are timing the connection you will find that the connection is generally nearly 3-9 seconds. This time is sometimes very strange, I have met many years ago, when the database request connection was reset, the SYN packet has been discarded. In 3 seconds, a SYN packet is discarded, and 9 seconds of two are discarded. If you encounter a similar situation, there may be a problem with your network or your database server requests a listen queue overflow, you can check by running the netstat-s command, you may return a socket similar to: Listen queue 38,409 times overflow, 38,409 SYN packets are discarded, which means that the kernel cache that listens for the socket overflows, and the SYN packet will have to be discarded--MySQL does not accept the connection as soon as it is needed.

If this happens, there are 2 tuning places you can consider.
1. Linux kernel: net.ipv4.tcp_max_syn_backlog, this parameter is used to set all the socket kernel cache size. My system defaults to 2048, other versions may vary, and if your connection is large, you may need to increase this value to around 8192. The exact match situation I will introduce below.

2.
MySQL parameter: back_log, the default value is 50. You may want to set this value to 1000 or higher. At the same time, you may raise the Net.core.somaxconn kernel parameter value, which is used to set the maximum depth of the listening queue. In my own kernel, this parameter is set to 128, which in many cases is low.

Now let's delve into this problem. First look at how MySQL is accepting connections. There is a main thread that will accept all requests to listen for a socket connection. When a new connection arrives, the main thread creates a new socket for the new connection, creates a new child thread, or takes a sub-thread from the cache to handle the connection. Standing in MySQL network communication base Multithreading this point of view, multi-core is advantageous, but for the main thread said, multicore and no use. Usually the main thread accepts the connection quickly, but if the main thread waits for a mutex or to start a new thread, the listening queue may overflow. Let's see if a database typically accepts 1000 connections per second, which is a high value, but you may encounter higher. Because of the random arrival characteristics of the connection request, sometimes you may see 3,000 connections concurrently. In this case, the default Back_log (50) can only support 17 milliseconds, and the main thread is processed in some places for more than 17 milliseconds, some SYN packets will be discarded.

I recommend that you adjust the value of Tcp_max_syn_backlog and Back_log to a connection request that supports 2 seconds (note: The connection request has not timed out for 2 seconds). For example, normally there are 100 connections per second, assuming a peak of 3 times times normal, then 300 connections/sec, which means that the previously mentioned parameters must be set to at least 600. (Note: 300 Connections/sec * 2 sec = 600 Connections/sec)

Setting a parameter to a request that supports more than 2 seconds does not make much sense, because the client will issue a new connection request after 3 seconds of not receiving an answer.

In addition, if you create 1000 MySQL connections per second, you may be a bit too much, after all, creating and destroying 1000 connections requires a lot of resources. Consider using a long connection or a connection pool, at least the case where most of the connections are created by the app.

Turn MySQL connection timeout