Shulou(Shulou.com)06/01 Report--

The following is to understand the reasons for the emergence of a large number of sleep processes in mysql and how to solve them. I believe you will benefit a lot after reading it. There are not many words in the text. I hope that the reasons for a large number of sleep processes in mysql and how to solve this short article is what you want.

Possible cause: the cause of too many sleep connections?

1. Too many persistent connections are used (in my opinion, in high concurrency [system] (in http://www.2cto.com/os/), persistent connections are not suitable)

2. The mysql connection is not closed in time in the program.

3. The database query is not optimized enough and takes too much time.

Of course, a more fundamental way is to investigate from the above three points:

In the program, do not use persistent links, even if you use mysql_connect instead of pconnect.

After the program has been executed, mysql_close should be called explicitly

3. We can only analyze the SQL query of the system step by step and find the SQL that is too slow. I use the elimination method to locate the problem. For this reason, 1 and 3 through the analysis, we find that it is not satisfied that the mysql configuration problem is excluded here. The sleep shutdown time is 8 hours, the default value (show variables like 'wait_timeout'). ), and the configuration of the CVM is maintained by OPS personnel, so our OPS is still very good at excluding 1: in my business, php link mysql does not use persistent link mysql_pconnect, and persistent link exclusion 3 is not used in high concurrency system framework: database query is not optimized? I wrote it myself. I can't. If there is really not enough sql that is not optimized enough, you can open mysql slow query log view and optimize it; another point is that by looking at the access of the database yesterday and today, there are not many reads and writes, the data volume of the table is only more than 2 million, and it has been in normal operation online for a long time. If there is a query that is not optimized enough, it will be dead long ago. The problem can only be that 2 programs did not close the mysql connection in time. There are many reasons for this problem and it is difficult to analyze. Generally speaking, as long as it is in the frame, the mysql link will be closed immediately (mysql_close). After the page is visited, the mysql link will be closed automatically. Example:

[php]

Change the $password to your actual database password, change the sleep connection time, and then add the scheduled task. For example, use the crontab-e command to add:

12 [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 configuration of MySQL, by default, the initial value of wait_timeout is 28800

Too large wait_timeout has disadvantages, which reflects that a large number of SLEEP processes in MySQL can not be released in time, which is a drag on system performance, but you can't set this value too low, otherwise you may encounter problems such as "MySQL has gone away". Generally speaking, I think setting wait_timeout to 10 is a good choice, but there may also be problems in some cases, such as a CRON script. If the interval between two SQL queries is more than 10 seconds, there will be a problem with this setting (of course, this is not an insurmountable problem. You can mysql_ping it from time to time in the program so that the CVM knows you are alive and recalculates the wait_timeout time):

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

However, this method is too blunt, and online service restart should be avoided as much as possible anyway. See how to set it through SET on the MySQL command line:

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

After reading the reasons for a large number of sleep processes in mysql and how to solve this article, many readers will certainly want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.