Mysql connection number, max concurrency setting

The project may encounter Mysql:error 1040:too many connections "exception, one of the reasons for this situation is that the traffic is too high, the MySQL server can not withstand, this time to consider increasing the spread of read pressure from the server Another reason is that the max_connections value in the MySQL configuration file is too small.
First, let's look at the maximum number of MySQL connections:

 like ' %max_connections% ';

If the server has a large number of concurrent connection requests, it is recommended that this value be raised to increase the amount of concurrent connections, although this is based on the fact that the machine can support it, because if the number of connections is greater, the connection buffer is provided for each connection.

It will cost more memory, so adjust the value appropriately, you cannot blindly increase the value of the set.

The value is too small will often appear error 1040:too many connections errors, can be over

 like ' max_used_connections ';

Wildcards view the number of connections in the current state to decide the size of the value.

To see that the maximum number of connections for the server response is 3, which is much lower than the maximum allowable connection value for the MySQL server.

For MySQL server maximum connection value of the set range is ideal: The server response to the maximum connection value of the server is higher than the number of connection values above 10%, if under 10%, the MySQL server maximum connection limit value is set too high.

Max_used_connections/max_connections * 100% = 3/512 *100%≈0.0058%

We can see that the ratio is much lower than 10% (because this is a local monitoring test server, the result value is not much reference meaning, you can set the maximum number of connections according to the actual situation).

If the max_used_connections is the same as max_connections, then it is max_connections set too low or exceed the server load limit, less than 10% is set too large.

The mysql max_connections parameter is used to set the maximum number of connections (users). Each user connected to MySQL counts as a connection.

MySQL will keep a connection for administrator (SUPER) login Anyway, for the administrator to connect to the database for maintenance operations, even if the current number of connections has reached max_connections. So the actual maximum number of connections MySQL is max_connections+1;
The maximum value of this parameter actually works (the actual maximum number of connections) is 16384, that is, the maximum value of the parameter cannot exceed 16384, even if it is more than 16384;
Increase the value of the Max_connections parameter without consuming too much system resources. The utilization of system resources (CPU, memory) mainly depends on the density and efficiency of the query;
The most obvious feature that the parameter is set too small is the "Too many connections" error;

Set this maximum connection value

Method 1:

 
set GLOBAL max_connections=1024;
show variables like ‘%max_connections%‘;

This method fails after MySQL restarts.

Method 2:

Modify MySQL configuration file my.cnf, and add or modify the value of max'u connections in the [mysqld] section:
max_connections=512 

Restart the MySQL service.

Overall, this parameter should be set as large as possible when the server resources are sufficient to meet the needs of multiple client simultaneous connections. Otherwise, there will be an error like "Too many connections".

Mysql connection number, max concurrency setting