Today the website is stuck again, and a large number of sleep is found
Causes of sleep:
1 The client program did not 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 server.
3 The client program ended abruptly in the middle of a data transfer
If the number of sleep processes in the same period is too high, and the total number of connections in other states exceeds the max_connection value, then MySQL can no longer process any requests except the root user, cannot establish a connection with any request, or hangs directly
Solution:
1. First, check whether your program uses the pconnect method, and secondly, check whether the mysql_close() is called in time before the page is executed. Try not to use the pconnect method, i.e. use mysql_connect. When the program is executed, the mysql_close should be explicitly invoked
2. Add wait_timeout and interactive_timeout to my.cnf, set the value smaller, by default the wait_timeout value is 8 hours, you can change it to 1 hour, or half an hour. This way mysql will kill dead connections faster. Prevent the total number of connections from exceeding the max_connection value.
wait_timeout too large will cause a large number of SLEEP processes in MySQL to be released in time, dragging down system performance, but if the settings are too small, you may encounter problems such as "MySQL has gone away"
Set the max_connection value to a higher value, but this is obviously not appropriate, the more connections you have, the more stress it puts on your server. In fact, those connections are redundant, and killing them as soon as possible is the best policy.
3. Analyze the system's SQL query step by step, find the SQL that is too slow, and optimize it
1)interactive_timeout:
Parameter meaning: The number of seconds the server waits for an activity before turning off the interactive connection. An interactive client is defined as a client that uses CLIENT_INTERACTIVE option in mysql_real_connect().
Parameter default: 28800 seconds (8 hours)
(2)wait_timeout:
Parameter meaning: The number of seconds that the server waits for activity before closing the non-interactive connection.
When the thread starts, the session wait_timeout value is initialized based on the global wait_timeout or global interactive_timeout value, depending on the client type (defined CLIENT_INTERACTIVE the connection options of mysql_real_connect()).
Parameter default: 28800 seconds (8 hours)
There is an upper limit to the maximum number of connections that MySQL Server can support, as each connection is established and consumes memory, so we expect clients to disconnect and free up the occupied memory after connecting to MySQL Server to handle the corresponding operation. If your MySQL Server has a large number of idle connections, not only will they consume memory in vain, but if the connections keep accumulating and opening, they will eventually reach the maximum number of connections in MySQL Server, which will report the error 'too many connections'. The value setting of the wait_timeout should be judged according to the operation of the system. After the system has been running for a period of time, you can check the connection status of the current system through the show processlist command, if you find a large number of connected processes in the sleep state, it means that the parameter setting is too large, and you can make appropriate adjustments to make smaller adjustments.
sql command:
This modification method is inAfter restarting the mysql service, it will be invalid, so it's best to configure these two properties in the mysql configuration file.
Modify the my.cnf file under Linux:
|
Posted on 9/5/2017 8:23:59 PM
|
|
|
|
Landlord|
Posted on 9/6/2017 9:32:56 AM
|
Posted on 9/6/2017 11:15:15 AM
|
