Easy to troubleshoot MySQL database connection too many errors

1, the database system allows the maximum number of connections max_connections. This parameter can be set. If not set, the default is 100. The maximum is 16384.

2, the database current number of connection threads threads_connected. This is a dynamic change.

See Max_connections, max_connections the way to see later.

if threads_connected = = max_connections, the database system will not be able to provide more connections, at this time, if the program also want to create a new connection thread, the database system will refuse, if the program did not do too much error handling, there will be similar to the strong altar of an error message.

Because the connection to the database is created and destroyed, the resources of the system are consumed. And to avoid having too many connection threads open at the same time, programming now generally uses the so-called database connection pooling technology.

However, database connection pooling technology does not prevent program errors from draining the connection resources.

This situation usually occurs when the program fails to release the database connection resource in a timely manner or otherwise causes the database connection resource to not be released, but the strong altar system estimates that this low-level programming error will not occur.

An easy way to check this error is to constantly monitor threads_connected changes as you refresh the strong altar page. If the max_connections is large enough and the threads_connected value is increasing to max_connections, then the program should be checked. Of course, if you use database connection pooling technology, the threads_connected grows to the maximum number of connection threads in the database connection pool, no longer grows.

From the case of a strong altar error, the larger possibility is that the database system is not properly configured. A few suggestions are made below. For reference

Let your engineers tune the maximum allowable number of connections for MySQL from the default of 100 to 32000. This will not be the old connection too much problem.

View Max_connections

Go to MySQL, with the command:

Show variables

To view the variable value of the maximum number of connections for a database:

Max_connections

View threads_connected

Go to MySQL, with the command:

Show status

To view the value of the currently active connection thread variable:

threads_connected

Set Max_connections

The Setup method is to add the following last red line in the My.cnf file:

[Mysqld]
port=3306
#socket =mysql
Skip-locking
Set-variable = key_buffer=16k
Set-variable = max_allowed_packet=1m
Set-variable = thread_stack=64k
Set-variable = table_cache=4
Set-variable = sort_buffer=64k
Set-variable = net_buffer_length=2k
Set-variable = max_connections=32000

Once the modifications are complete, restart MySQL. Of course, to make sure the settings are correct, you should look at max_connections.

Attention:

1, although the 32000 written here. But the actual MySQL server allowed the maximum number of connections 16384;

2, in addition to Max_connections, the above other configuration should be based on your system needs to be configured, do not have to rigidly;

3, add the maximum allowable number of connections, the system consumption increase is not small.

4, if your MySQL is using My.ini as the configuration file, settings similar, but the format of the settings to be slightly flexible

Transfer from: http://www.aspnetjia.com

Easy to troubleshoot MySQL database connection too many errors