Easily solve the problem of too many MySQL database connections

Easily solve the problem of too many MySQL database connections

1. Maximum number of connections allowed by the MySQL database system max_connections. This parameter can be set. If this parameter is not set, the default value is 100. The maximum value is 16384.

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

For more information about how to view max_connections and max_connections, see.

If threads_connected = max_connections, the database system cannot provide more connections. In this case, if the program wants to create a new connection thread, the database system will reject the connection, if the program does not handle too many errors, an error message similar to the strong altar will appear.

Because the creation and destruction of database connections consume system resources. To avoid opening too many connection threads at the same time, the so-called database connection pool technology is usually used for programming.

However, the database connection pool technology does not prevent connection resources from being exhausted due to program errors.

This usually happens when the program fails to release the database connection resources in time or other reasons cause the database connection resources to be unable to be released. However, it is estimated that this low-level programming error will not occur in the strong altar system.

The easy way to check this error is to constantly monitor the changes of threads_connected when refreshing the strong altar page. If max_connections is large enough, and the value of threads_connected is constantly increasing to reach max_connections, check the program. Of course, if you use the database connection pool technology, threads_connected will not grow to the maximum number of connection threads in the database connection pool.

From the perspective of a strong altar error, it is more likely that the database system fails to be properly configured. The following are some suggestions. For reference

Ask your engineers to change the maximum number of connections allowed for MySQL from 100 to 32000 by default. This will not cause too many connections.

View max_connections

Go to MySQL and run the following command:

Show variables

View the variable value of the maximum number of connections in the database:

Max_connections

View threads_connected

Go to MySQL and run the following command:

Show status

View the value of the currently active connection thread variable:

Threads_connected

Set max_connections

The setting method is to add the last red line below in the my. cnf file:

[Mysqld]
Port = 3306
# Socket = MySQL
Skip-locking
Set-variable = key_buffer = 16 K
Set-variable = max_allowed_packet = 1 M
Set-variable = thread_stack = 64 K
Set-variable = table_cache = 4
Set-variable = sort_buffer = 64 K
Set-variable = net_buffer_length = 2 K
Set-variable = max_connections = 32000

After modification, restart MySQL. Of course, to ensure correct settings, check max_connections.

Note:

1. Although 32000 is written here. However, the actual MySQL server allows a maximum of 16384 connections;

2. In addition to max_connections, the above configurations should be configured according to your system's own needs;

3. added the maximum number of allowed connections, which does not increase the system consumption much.

4. If your mysql instance uses my. ini as the configuration file, the settings are similar, but the settings must be slightly changed.