The MySQL thread runs out, the new connection cannot connect the question note!

Problem:

The maximum number of connections supported by the MySQL server is capped, and each connection consumes a certain amount of memory resources, so when the client accesses the MySQL server to process the appropriate operation, it should disconnect the memory resource by disconnecting the connection.

If the server has a large number of idle connections, so that will be wasted memory, and if it has been accumulating and constantly open, will reach the connection limit, reported "Too many connections" error. Can be viewed by the command "Show Process List", if you find a large number of sleep threads in the background, this time you need to adjust the above parameters.

Some parameters are explained:

Show variables like '%max_connections% '; --Querying the current connection

The maximum number of connections to the database can be set in/ETC/MY.CNF

[Mysqld]

max_connections = 1000

Mysql> Show status like ' threads% ';

+-------------------+-------+

| variable_name | Value |

+-------------------+-------+

| threads_cached | 58 |

| threads_connected |   57 | # # #这个数值指的是打开的连接数

| threads_created | 3676 |

| threads_running |   4 | # # #这个数值指的是激活的连接数, this value is generally much lower than the connected value

+-------------------+-------+

The threads_connected is the same as the show processlist result, indicating the current number of connections. To be exact, threads_running represents the current number of concurrent numbers.

References: http://blog.csdn.net/imxiangzi/article/details/46502423

SHOW full processlist;

Kill Kill + ID;

Describe the meaning and purpose of each column,

ID column: An identity that is useful when you want to kill a statement.

User column: Displays the current user, if not root, this command displays only the SQL statements that are within the scope of your permission.

Host column: Shows which IP port this statement was issued from. Users that can be used to track the problem statement.

DB column: Shows which database this process is currently connected to.

Command column: Displays commands for the execution of the current connection, typically hibernation (sleep), query, connection (connect).

Time column: The duration of this state, in seconds.

State column: Displays the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is only one of the states in the statement execution, an SQL statement that has been queried as an example, may need to go through copying to TMP table, Sorting result,sending data and other states can be completed.

Info column: This SQL statement is displayed because the length is limited, so long SQL statements are incomplete, but an important basis for judging the problem statement.

Commonly used parameters that need to be modified:

1, Interactive_timeout

2, Wait_timeout

1.interactive_timeout

The number of seconds that the server waits for activity before closing the interactive connection. The interactive client refers to the client that uses the Client_interactive option in Mysql_real_connect ().

Parameter default value: 28,800 seconds (8 hours)

2.wait_timeout

The number of seconds that the server waits for activity on a connection when a non-interactive connection is closed.

Parameter default value: 28,800 seconds (8 hours)

Problem:

Does not take effect after individual modification of wait_timeout,mysqld in my.cnf; (Widnows My.ini)

Also modify the interactive_timeout and Wait_timeout parameters and take effect after restarting the server.

Note: Due to MySQL version problem, sometimes only need to modify wait_timeout this value. The specific parameters need to be viewed according to the version manual.

This article from the "[email protected]" blog, declined to reprint!

The MySQL thread runs out, the new connection cannot connect the question note!