The reason and solution of a large number of sleep processes in MySQL

There have been similar problems before, the cause of this problem from the Internet to check, there are several reasons:

Cause of excessive sleep connections?

1. Too many persistent connections used (personally, it is not appropriate to use a persistent connection in a high concurrency system)

2. In the program, did not close the MySQL connection in time

3. Database query is not optimized, excessive time consuming.

Of course, a more fundamental approach, or from the above three points to the investigation:

1. In the program, do not use a persistent link, that is, use mysql_connect instead of pconnect.

2. Program execution completed, should explicitly call Mysql_close

3. Only stepwise analysis of the system's SQL query, find the query too slow SQL, optimization P

I was using the elimination method to locate the problem, for this reason, 1 and 3 through analysis, found not satisfied

Here to eliminate the MySQL configuration is the problem, sleep closed time is 8 hours, the default value (show variables like ' wait_timeout ';), and the server configuration are operational maintenance personnel, our operational dimension is still very good

Exclusion 1:

My business, PHP link MySQL does not use persistent link mysql_pconnect, the high concurrency system framework, will not use the persistent link

Exclusion 3:

Database query not optimized? Written by yourself, not enough. If there's really not enough SQL for optimization, can turn on MySQL slow query log to view, and optimize; and one thing is my business. By looking at the database yesterday, today's two-day visit, reading and writing are not many, table data volume only Grand, and has been online normal operation for a long time, If there are not enough to optimize the query, long ago hung up.

The problem is only possible in the 2 program, did not close the MySQL connection in time, causing a lot of reasons for this problem, it is difficult to analyze. Generally as long as the frame, will immediately close the MySQL link (mysql_close), page access, the MySQL link is bound to automatically shut down

There's a guy on the internet who wrote a

The code is as follows

Copy Code

Define (' Max_sleep_time ', 120); $hostname = "localhost"; $username = "root"; $password = "password"; $connect = mysql_connect ($hostname, $username, $password); $result = mysql_query ("Show Processlist", $connect); while ($proc = Mysql_fetch_assoc ($result)) { if ($proc [Command] = = Sleep && $proc [' time '] > Max_sleep_time) { @mysql_query ("KILL"). $proc ["Id"], $connect); } } Mysql_close ($connect); ?>

Change the $password to your actual database password, and the sleep connection can be modified, and then join the scheduled task. For example, use the CRONTAB-E command to join:

The code is as follows	Copy Code
*/2 * * * * php/usr/local/sbin/kill-mysql-sleep-proc.php

You can check and clear the Sleep connection in the database every 2 minutes.

If you have not modified the MySQL configuration, by default, the Wait_timeout initial value is 28800

Wait_timeout too much abuse, its embodiment is a large number of the sleep process in MySQL can not be released in time, drag system performance, but can not set this value too small, otherwise you may encounter "MySQL has gone away" problems, usually, I think setting the Wait_timeout to 10 is a good choice, but in some cases it may also be problematic, such as having a cron script where two times the interval between SQL queries is greater than 10 seconds, then there is a problem with this setting (which, of course, is not an unresolved issue. You can mysql_ping in the program every now and then so that the server knows you're still alive and recalculates the wait_timeout time:

The code is as follows	Copy Code
# VI/ETC/MY.CNF [Mysqld] wait_timeout=10 #/etc/init.d/mysql Restart

However, this method is too blunt, the online service restart should be avoided in any case, see how to set in the MySQL command line by setting:

code is as follows	copy code
mysql> Set global wait_timeout=10; Mysql> show global variables like '%timeout '; +----------------------------+-------+ | variable_name | Value | +----------------------------+-------+ | wait_timeout | +----------------------------+-------+