The sleep process of MySQL

PHP's garbage collection mechanism, in fact, is only for PHP itself.

For mysql,php, there is no right to automatically release its things.

If you do not call Mysql_close () before the page finishes executing, the connection will not be closed by MySQL.

If you are using the Pconnect method, even if you call Mysql_close () before the page finishes executing, you will not be able to close the connection with MySQL.

Using the Pconnect method in PHP to establish a connection, if your load to a certain extent, you can see a lot of sleep process, these processes are so-called dead connection, they will keep sleep until my.cnf inside the Wait_ set Timeout This parameter value time is up, MySQL will kill it by itself.

While killing it, MySQL will also record a aborted connection XXX to db in error-log: ' xxx ' User: ' xxx ' host: ' xxx ' log.

Causes of sleep:

1 The client program has not been a call mysql_close () before exiting.

2 The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the S Erver.

3 The client program ended abruptly in the middle of a data transfer

If you have too many sleep processes at the same time, plus other state connections, the total exceeds the value of max_connection, then MySQL will no longer be able to process any requests that cannot be connected to any request or hang directly except the root user.

How to resolve:

1. First check whether your program is using the Pconnect method, and secondly, check whether the Mysql_close () is called in time before the page is finished executing. Try not to use the Pconnect method, that is, to use mysql_connect. When the program finishes executing, you should explicitly call Mysql_close

2. Add Wait_timeout and Interactive_timeout to the MY.CNF, set the value smaller, by default the value of Wait_timeout is 8 hours, you can change to 1 hours, or half an hour. This way, MySQL will kill the connection faster. Prevents the total number of connections from exceeding max_connection values.

Wait_timeout too big, will cause a lot of sleep process in MySQL can not be released in time, drag system performance, but too small, may encounter "MySQL has gone away" and other issues

Set the value of the max_connection larger, but this is obviously inappropriate, the more connections, the greater the pressure on your server. Actually those connections are redundant, and killing them as soon as possible is the best policy.

3. Analyze the SQL query of the system, find the SQL with slow query, optimize the

The sleep process of MySQL