Causes and solutions for a large number of sleep processes in mysql

Mysql has a large number of sleep processes and solutions. mysql server has a large number of sleep processes. This article analyzes the causes and solutions for the large sleep process in mysql. Possible cause: why are there too many sleep connections? 1. too many persistent connections are used (in my opinion, persistent connections are not suitable for high-concurrency systems. the mysql connection is not closed in time. the database query is not optimized enough to consume too much time. 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 3. I can only analyze the system's SQL query step by step and find the slow SQL query. I used the exclusion method to locate the problem. For this reason, I analyzed 1 and 3, the mysql configuration is not met. The sleep is closed for 8 hours and the default value is show variables like 'wait _ timeout ';), in addition, the server configuration is maintained by O & M personnel, and our O & M is still excellent. 1. For my business, the persistent link mysql_pconnect is not used in the php link to mysql. In the high-concurrency system framework, does not use persistent links? 3. Is database query not optimized? You cannot write it yourself. If there are SQL statements that are not optimized enough, you can enable mysql slow query log viewing and optimize it. Another point is that my business can view the database access status yesterday and today by checking the database access status, there are not many reads and writes, there are only more than 2 million data records in the table, and it has been running online for a long time. If there are not enough optimization queries, they will be suspended for a long time. The problem may be that the mysql connection is not closed in time in the 2 Program, which may cause many reasons and is difficult to analyze. Generally, the mysql Link (mysql_close) is immediately closed as long as it is in the Framework. After the page is accessed, the mysql link will be automatically closed. Example:

[php] <?php  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);  }  } //by www.jbxue.com  mysql_close($connect);  ?>

Change $ password to your actual Database password. You can modify the sleep connection time and add it to the scheduled task. For example, use the crontab-e command to add:

[html] */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, the initial value of wait_timeout is 28800 wait_timeout by default, which has many drawbacks, it is reflected that a large number of SLEEP processes in MySQL cannot be released in time, dragging down system performance, but this value cannot be set too small, otherwise, you may encounter problems such as "MySQL has gone away". Generally, it is a good choice to set wait_timeout to 10, but in some cases, there may also be problems. For example, if there is a CRON script, and the interval between two SQL queries is greater than 10 seconds, then this setting will be problematic (of course, this is not an unsolved problem. You can ping mysql_ping from time to time in the program so that the server knows that you are still alive and recalculates the wait_timeout Time ):

[html] # vi /etc/my.cnf  [mysqld]  wait_timeout=10  # /etc/init.d/mysql restart  

However, this method is too stiff, so we should try to avoid restarting the online service no matter what, to see how to SET it in the MySQL command line:

[html] mysql> set global wait_timeout=10;  mysql> show global variables like '%timeout';  +----------------------------+-------+  | Variable_name | Value |  +----------------------------+-------+  | wait_timeout | 10 |  +----------------------------+-------+