Solve the problem of too many Sleep connections in MySQL

Run show processlist in mysql. There are many such processes in the database:



In fact, there are too many Sleep connections in MySQL, so there are three reasons for the sleep:

1. Before the client program exits, it does not call mysql_close (). [write program negligence, or the database class library does not automatically close each connection...]
2. the client sleep does not send any request to the server within the seconds specified by wait_timeout or interactive_timeout. [similar to a common connection, similar to an incomplete tcp IP protocol structure, the server always thinks that the client still exists (it is possible that the client has been disconnected)]
3. Before the client end, the client end up sending a request to the server without returning the result. [refer to the three-way handshake of tcp IP protocol]

The solution is also simple.

Add

[Mysqld]

Wait_timeout = 10

Or

Mysql> set global wait_timeout = 10;


The perfect solution for MySQL sleep connection too many

If there are too many sleep connections, what will happen to the mysql server?
It seriously consumes mysql server resources (mainly cpu and memory) and may cause mysql to crash.
What causes excessive sleep connections? Www.2cto.com
1. Too many persistent connections are used (in my opinion, persistent connections are not suitable for high-concurrency systems)
2. The mysql connection is not closed in time in the program.
3. The database query is not optimized enough to consume too much time.
 
If you want to fundamentally solve the problem of too many sleep connections, you have to repeat the above three points, but the effect is not quick.
Some people share it on the Internet, using shell scripts with cron to regularly kill connections that have been sleeping for too long, but this method is very undesirable. A typical violent violence may cause data crash, and, you also need to write the corresponding shell and set cron, which is cumbersome to implement and is not recommended.
The better way is to let mysql decide the fate of these sleep connections, which is simpler and more effective.
Mysql configuration file contains the following items:
Wait_timeout, you can set the sleep connection timeout seconds. If a connection times out, it will be naturally terminated by mysql!
For example, set:
Wait_timeout = 100 # set the mysql connection sleep time to 100 seconds. If the sleep connection sleep time exceeds 100 seconds, it will be naturally terminated by the mysql service, which is easier than writing shell scripts.
So what should I do if the service cannot be stopped on a running production server? Log on to mysql as the root user and run the following command:
Set global wait_timeout = 100
You can.
In my production environment, this method has achieved quite good results.
Of course, the more fundamental method is to troubleshoot from the above three points:
1. In the program, do not use persistent links, that is, use mysql_connect instead of mysql_pconnect.
2. After the program is executed, you should explicitly call mysql_close
3. You can analyze the system SQL query step by step, find and optimize the slow query SQL statements.