Solutions to excessive Sleep connections in MySQL

Sometimes you run show processlist in mysql, and then you will find that there are many such processes in the database:

There are three reasons for 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;

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 pconnect.

2. After the program is executed, you should explicitly call mysql_close