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

This article mainly introduces how to deal with abnormal client connections in MySQL. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

Background 1:

The program reported that there was an error in max_connection in the database, resulting in client being unable to connect to db.

According to the program, the use of a large number of connections is a historical problem, so the parameters of max_connection are raised.

Background 2:

Another business program found some long-running sleep connections in the database, but the application layer has restarted all processes.

For example, screenshot:

More than 10 seconds are new connections after restart, and about 3000 are abnormal links.

Scene:

The reproduction scene is not built.

Analysis:

The first thing to consider is that there are missing processes on the application server that have not been closed, so these connections are maintained all the time.

After checking and repeatedly ensuring that there is no process connecting to the database, the program begins to analyze along the network link.

First of all, the program stops all the processes in the application layer, and then it is found that only sleep connections that last for thousands of seconds are left on the MySQL.

EST's TCP/IP connection can also be seen on DB's server, confirming that the connection still exists.

Then I checked the haproxy server at the front end of MySQL, compared the ports, and found the connection on haproxy.

Check the connection information of haproxy, and find the IP and corresponding port of the application server, such as screenshot:

Strangely enough, there is no such information about port 38710 on the application server, and all the newly created ports have gone after 48000.

So what exactly is the principle of these connections to non-existent ports on haproxy?

After explaining the situation to my colleagues in the network section, I finally gave an explanation:

When connecting to DB, client will forward to haproxy via LVS, where the timeout setting of LVS is shorter than that of haproxy.

So when client accesses LVS after remaining idle for a period of time, LVS will tell client to re-initiate a new connection to haproxy

The old connection on your haproxy has not been disconnected because it has not timed out to haproxy, so this kind of link without source will appear when querying.

Handling method:

On the haproxy instance where the problem was found, the timeout of haproxy was adjusted to be slightly higher than that of LVS, and the connection no longer occurred within a few days.

And when haproxy is disconnected, there are some NOTE reminders in MySQL's error log that a connection has been disconnected.

In background 2, in fact, for the same reason, a large number of sleep connections are accumulated on the MySQL end, resulting in crowding out the available connection, resulting in client being unable to connect to DB.

After uniformly modifying the timeout of haproxy, this phenomenon no longer occurs, and the problem is solved.

The above is all the contents of the article "how to deal with abnormal client connections in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.