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

Too many connections

Literally, there are too many mysql connections. This error occurs when there are too many connections and there is no time to release.

View mysql's current connection information, paying special attention to sleep hibernating connections.

Show full processlist

Command status:

SLEEP

The thread is waiting for the client to send a new request. QUERY

The thread is executing a query or sending the results to the client. LOCKED

At the MYSQL services layer, the thread is waiting for a table lock. Locks implemented at the storage engine level, such as INNODB's row locks, are not reflected in thread state. This is a typical state for MYISAM. But it often occurs in other engines that do not have row locks. The ANALYZING AND STATISTICS thread is collecting statistics for the storage engine and generating an execution plan for the query. COPYING TO TMP TABLE (ON DISK)

The thread is executing the query and copying its result set to a temporary file, which is usually either a GROUP BY operation, a file sorting operation, or a UNION operation. If this state is followed by an ON DISK flag, it means that MYSQL is putting a temporary memory table on disk. SORTING RESULT

The thread is sorting the result set. SENDING DATA

A thread may transfer data between multiple states, or generate a result set, or return data to the client.

View the maximum number of connections set by mysql

Show global variables like 'max_connections'

Maximum number of connections responded by the server

Show global variables like 'max_user_connections'

It is recommended that the maximum connection value responded by the server accounts for more than 10% of the server upper limit connection value. If it is less than 10%, the maximum connection limit value of the mysql server is set too high.

Max_user_connections/max_connections 10% > = 10%

If the maximum number of connections is fine and there are too many connections in sleep hibernation, you should pay attention to two aspects:

one. Wait_timeout idle wait timeout 2. Interactive_timeout interaction timeout (connecting to the database through a mysql client is an interactive connection)

Need theme: too much wait_timeout setting has drawbacks, which reflects that a large number of SLEEP processes in MySQL can not be released in time, which is a drag on system performance. If the setting is too small, you may encounter problems such as "MySQL has gone away".

In order to fundamentally solve the problem of too much sleep, we need to check from the following three points:

one. The program connects to mysql without using persistent links. Even use mysqli_connect instead of pconnect. two. At the end of program execution, mysqli_close is actively and explicitly called to release connection resources. three. Resolve the SQL slow query statement in the program.

Finally, the process of rapid resolution

Set GLOBAL max_connections = 500 * set GLOBAL wait_timeout = 300 * set GLOBAL interactive_timeout = 600

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.