MySQL Sleep Process

Query the current number of connections in MySQL:

Mysql> Show status like '%threads_connected% '; +-------------------+-------+| Variable_name     | Value |+-------------------+-------+| threads_connected |    +-------------------+-------+1 row in Set (0.00 sec)

Query maximum number of connections:

Show variables like '%max_connections% '; set GLOBAL Max_connections=800;flush privileges can also modify/etc/my.cnf in Max_ connections:max_connections = 1000

Executing show Processlist under the MySQL client can see a lot of sleep processes, which are often said to be dead connections, and they will keep sleep until the wait_ inside MY.CNF is set. Timeout This parameter value time is up, MySQL will kill it by itself. In killing it, MySQL will also record in Error-log inside a aborted connection XXX to db: ' xxx ' User: ' xxx ' The log, with Google Translate, will get a pretty strong explanation " Fetal death in the belly of the connection! "

You can view the time-out period for system settings by using the following command:

Show global variables like '%timeout '; set global wait_timeout = 10;

So there are three reasons for sleep, the following is the explanation given by the MySQL manual:

• The client program did not call Mysql_close () before exiting. (Failure to write a program, or DB Class library of the database does not automatically close each connection)
• The client sleep time does not make any requests to the server in Wait_timeout or interactive_timeout for the seconds specified. (similar to a regular connection, similar to an incomplete TCP IP protocol construct, the server has always assumed that the client is still present (it is possible that the client has been disconnected))
• The client program sends a request to the server before it ends and the result is not returned. (see: Three-time Handshake for TCP IP protocol)

Configure the parameters in MySQL. Time-out setting.

• Max_connections:

The number of simultaneous customers allowed. When the load is too large, you will often see too many connections error. The maximum number of links has been reached, so this can happen.

• Wait_timeout

The number of seconds the server waits for an action on a connection before closing the connection, the default value is 28800, which means that if nothing happens, the server closes the connection after 8 hours. Prevents excessive sleep and causes too many connections to appear.

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 request to establish a connection to any request or down directly, except the root user. So, this problem is quite serious under the condition of heavy load. If you find that your MySQL has a lot of dead connections, 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.
There is another way, you can add wait_timeout and interactive_timeout in the MY.CNF, set their values 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. or 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.

Wait_timeout has a lot of drawbacks, its embodiment is that a large number of sleep in MySQL can not be released in a timely manner, drag down the system performance, but also can not set this finger too small, or you may encounter "MySQL has gone away" and the like problems, usually, I think it's a good idea to set Wait_timeout to 10, but in some cases there may be problems, 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 (of course, this is not an unresolved issue. You can mysql_ping in the program every now and then so that the server knows you're alive and recalculates the wait_timeout time.

See also: http://www.blogjava.net/xiaomage234/archive/2010/04/12/318046.html

http://blog.csdn.net/starnight_cbj/article/details/4492555

MySQL Sleep Process