Shulou(Shulou.com)05/31 Report--

This article mainly introduces the mysql database connection too many errors how to deal with, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Mysql database connection too many errors, possible cause analysis and solutions (transfer) [@ more@] the system cannot connect to the database, the key depends on two data:

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

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

See later on how to check max_connections and max_connections.

If threads_connected = = max_connections, the database system will not be able to provide more connections. At this time, if the program still wants to create a new connection thread, the database system will reject it. If the program does not do too much error handling, an error message similar to Qiangtan will appear.

Because creating and destroying database connections will consume the resources of the system. And in order to avoid opening too many connection threads at the same time, programming now generally uses the so-called database connection pooling technology.

However, database connection pool technology can not avoid program errors leading to the depletion of connection resources.

This usually occurs when the program fails to release database connection resources in time or for other reasons, but Qiangtan system estimates that such low-level programming errors will not occur.

An easy way to check for this error is to constantly monitor threads_connected changes while refreshing the Qantan page. If the max_connections is large enough and the threads_connected value continues to increase to reach max_connections, then it's time to check the program. Of course, if you use database connection pooling technology, threads_connected will no longer grow when it reaches the maximum number of connection threads in the database connection pool.

Judging from the mistakes made by Qiangtan, it is more likely that the database system has not been configured properly. Here are some suggestions. For reference

Ask your engineer to change the maximum number of allowed connections for MySQL from the default 100 to 32000. This will not always have the problem of too many connections.

View max_connections

Enter MySQL and use the command: show variables

View the variable value of the maximum number of connections to the database: max_connections

View threads_connected

Enter MySQL and use the command: show status

View the value of the currently active connection thread variable: threads_connected

Set up max_connections

The setting 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

After the modification, restart MySQL. Of course, to make sure the settings are correct, you should take a look at max_connections.

Note:

1. Although 32000 is written here. But the maximum number of connections allowed by the actual MySQL server is 16384

2. In addition to max_connections, the other configurations mentioned above should be configured according to the needs of your system.

3. The maximum number of allowable connections is added, which does not increase the consumption of the system.

4. If your mysql uses my.ini as a configuration file, the setting is similar, but the format should be slightly modified.

Thank you for reading this article carefully. I hope the article "how to deal with too many mysql database connections" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.