mysql how to optimize the number of connections to prevent excessive traffic

Many developers will encounter the abnormal situation of "MySQL: ERROR 1040: Too many connections". One of the reasons for this situation is that the amount of traffic is too high, and the MySQL server is unable to withstand it. Another reason is that the value of max_connections in the MySQL configuration file is too small.

First, let's look at the maximum number of connections in mysql:

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

Second, look at the maximum number of connections the server responds to:

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

You can see that the maximum number of connections the server responds to is 2, far below the maximum number of connections allowed by the mysql server.

The ideal setting range for the mysql server Max connection value is that the maximum connection value that the server responds to accounts for more than 10% of the server Max connection value. If it is less than 10%, the mysql server Max connection value is set too high.

Max_used_connections / max_connections * 100% = 2/151 *100%  material  1%

We can see that the percentage is much lower than 10% (because this is a local test server, the result value is not very useful, you can set the upper limit of the number of connections according to the actual situation).

Take another look at the result value of linode VPS:

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 151  |
+-----------------+-------+
1 row in set (0.19 sec) 
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| Max_used_connections | 44  |
+----------------------+-------+
1 row in set (0.17 sec)

The maximum number of connections here is about 30% of the maximum number of connections.

Now that we know how to look at the maximum connection value for the mysql server and how to determine if it is reasonable, let's look at how to set this maximum connection value.

Method 1:

mysql> set GLOBAL max_connections=256; 
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 256  |
+-----------------+-------+
1 row in set (0.00 sec)

Method 2:

Modify the mysql configuration file ES45en.cnf to add or modify the max_connections value in paragraph [mysqld] :

max_connections=128
Restart the mysql service.