How to achieve the maximum number of connections in mysql

What is the reason for the sudden increase in the number of database connections?

It may be that the database performance suddenly slows down, and it takes a long time for connected clients to get a response. Clients think that they have not been confirmed, so they keep connecting. This increases the number of sessions, making the database busier and eventually crashing.

1. Introduction

In the project, you may encounter the abnormal situation of "MySQL: ERROR 1040: Too many connections". Why: One reason for this situation is that the traffic is too high and the MySQL server cannot handle it. At this time, you should consider increasing the read pressure from the server to disperse it. Another reason is that the max_connections value in the MySQL configuration file is too small.

2. Knowledge Points

The max_connections parameter of Mysql is used to set the maximum number of connections (users). Each user connected to MYSQL is counted as one connection. The default value of max_connections varies slightly in different versions.

Mysql5.5 mysql5.6 mysql5.7: The default maximum number of connections is 151, and the upper limit is: 100000

The default maximum number of connections and the upper limit of the number of connections that can be modified are different for Mysql5.1 depending on its minor version.

MySQL 5.0 version: The default maximum number of connections is 100, and the upper limit is 16384

I want to complain about the naming of MySQL versions, which jumped from 5.7 to 8.0. Mysql5.5 mysql5.6 mysql5.7: The default maximum number of connections is 151. This value is far from enough for database applications with many concurrent connections. When the number of connection requests exceeds the default number of connections, an error will occur that indicates that the database cannot be connected, so we need to increase it appropriately. When using MySQL database, you often encounter a problem, which is "Can not connect to MySQL server. Too many connections" -mysql 1040 error. This is because the number of connections accessing MySQL and not yet released has reached the upper limit of MySQL. MySQL will always retain a connection for the administrator (SUPER) login, which is used by the administrator to connect to the database for maintenance operations, even if the current number of connections has reached max_connections. Therefore, the actual maximum number of connections that can be made to MySQL is max_connections+1; increasing the value of the max_connections parameter will not take up too many system resources. The usage of system resources (CPU, memory) mainly depends on the density and efficiency of queries. The most obvious symptom of setting this parameter too small is the "Too many connections" error.

3. Practice

1. Check the maximum number of connections

Mysql5.5 mysql5.6 mysql5.7: The default maximum number of connections is 151, and the upper limit is: 100000

    mysql> show variables like "%max_connections%";
    ±----------------±------+
    | Variable_name | Value|
    ±----------------±------+
    | max_connections| 151 |
    ±----------------±------+
    1 row in set (0.00 sec)

2. Check the maximum number of connections responded by the server

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 11 |
+----------------------+-------+
1 row in set (0.00 sec)

You can see that the maximum number of connections the server responds to is 11, which is far lower than the maximum number of connections allowed by the MySQL server. The ideal setting range for the maximum connection value of the MySQL server is: the ratio of the maximum connection value of the server response to the upper limit connection value of the server is above 10%. If it is below 10%, it means that the maximum connection limit value of the MySQL server is set too high.

3. Modify the maximum number of connections

The two common ways to modify the maximum number of connections are as follows:
The first method: Modify the maximum number of connections (max_connections) in the command line and set the maximum number of connections to 1000.

mysql> set global max_connections = 1000;

There is a problem with this method, that is, the set maximum number of connections is only valid for the current MySQL service process. Once MySQL is restarted, it will return to the initial state. Because the initialization work after MySQL is started reads data from its configuration file, and this method does not make changes to its configuration file.

The second method: Modify the maximum number of MySQL connections (max_connections) by modifying the configuration file.
Enter the MySQL installation directory, open the MySQL configuration file my.ini or my.cnf, search for max_connections=100, change it to max_connections=1000, and restart the MySQL service.

IV. References

1. https://www.yisu.com/zixun/38410.html

2. https://bbs.huaweicloud.com/blogs/147608

This is the end of this article about how to achieve the maximum number of connections in MySQL. For more information about the maximum number of connections in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!