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

If you are using phpmyadmin for mysql management or you frequently view the mysql server, you may find that your mysql server has a large number of sleep processes, today, I want to analyze the causes and solutions for the large sleep process in mysql.

I have encountered a similar problem before. I checked the cause from the Internet for several reasons:

What causes excessive sleep connections?

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.

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. You can only analyze the system's SQL query step by step, find the slow query SQL, and optimize the p

I used exclusion to locate the problem. For this reason, I analyzed the cause and found that it was not satisfied.

The mysql configuration problem is first ruled out here. The sleep close time is 8 hours, and the default value (show variables like 'wait _ timeout';), and the server configuration is maintained by O & M personnel, our O & M is still outstanding.

Rule 1:

In my business, php does not use persistent connection mysql_pconnect to connect to mysql. In the high-concurrency system framework, persistent connections are not used.

Exclude 3:

Are database queries 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) will be immediately closed as long as it is in the Framework. After the page is accessed, the mysql link will be automatically closed.

A buddy wrote one online,

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 $ 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:

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, the initial value of wait_timeout is 28800 by default.

Wait_timeout 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 ):

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 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:

The Code is as follows:	Copy code
Mysql> set global wait_timeout = 10; Mysql> show global variables like '% timeout '; + ---------------------------- + ------- + | Variable_name | Value | + ---------------------------- + ------- + | Wait_timeout | 10 | + ---------------------------- + ------- +