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

How to solve too many MySQL connections? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

The default maximum number of connections for MySQL database is: 100

For a single project developed by many people, although we will not use more than 10 connections at the same time, in theory, 100 is more than enough, but in addition to the connections we are using, there are a large number of Sleep connections, which is the real culprit.

After analyzing the root cause of the problem, we need to prescribe the right remedy to the case and solve the problem one by one:

Modify the maximum number of MySQL connections

First, check what is the current maximum number of Mysql connections:

Show variables like'% max_connections%'

I have modified it here, so it is 1000. The children's shoes that have not been changed should still be 100.

Then check the maximum number of parallel connections at the same time since the mysql service was started:

Show status like 'Max_used_connections'

For the setting of the maximum number of connections for MySQL, set a larger value when it is configured for the first time. Later, in the process of use, the periodic query Max_used_connections then determines the maximum number of connections that is most suitable for the current project according to its value and the performance of the server.

There are two ways to modify the maximum number of connections

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Use the sql statement (effective immediately, but not valid after the server is restarted):

Set global max_connections = 1000

1 modify / etc/my.cnf. Adding max_connections = 1000 is permanent. Take effect after restart

But changing the maximum number of connections can only ostensibly solve the problem. As we have more developers, there will be more Sleep connections. If we reach the upper limit of 1000, do we have to change it to 10000? This is obviously very undesirable. So we should not only cure the symptoms, but also cure the root of the disease. Killing redundant Sleep connections is a permanent cure.

Kill the Sleep connection

We can view all current connection status through the show_processlist command

It can be found that Sleep connections account for the vast majority.

MySQL database has an attribute wait_timeout that is the maximum survival time of sleep connections. The default is 28800 s, which translates to 8 hours in hours. Oh, my God! This is too long! Seriously affect performance. This means that all connections that have been established but not closed since work today will not be cleaned.

Execute the command:

Show global variables like'% wait_timeout'

We changed it to an appropriate value, and here I changed it to 250s. Of course, you can also modify it in the configuration file to add wait_timeout = 250. This value can be modified according to the needs of the project, in s. I configured 240s here in combination with navicat's timeout request mechanism.

Execute the command:

Set global wait_timeout=250; 's answer on how to solve the problem with too many MySQL connections is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.