Analysis of max_connections configuration parameters for MySQL Performance Optimization

MySQL Performance Optimization-max_connections configuration parameters analysis MySQL max_connections parameters are used to set the maximum number of connections (users. Each user connected to MySQL is counted as a connection. the default value of max_connections is 100. This article describes the detailed functions and performance impact of this parameter.

Features related to max_connections

MySQL retains a connection used to log on to the administrator (SUPER), which is used to connect the administrator to the database for maintenance, even if the current number of connections has reached max_connections. Therefore, the actual maximum number of connections allowed for MySQL is max_connections + 1;
The actual maximum value (actual maximum number of connections) for this parameter is 16384, that is, the maximum value of this parameter cannot exceed 16384, even if it exceeds 16384, it shall prevail;
Adding the value of max_connections does not occupy too many system resources. The usage of system resources (CPU and memory) depends on the query density and efficiency;
The most obvious feature of Setting this parameter Too small is the "Too connections" error;

Adjust the value of max_connections

There are several methods to adjust this parameter, which can be set during compilation, in the MySQL configuration file my. cnf, or directly use the command to adjust and take effect immediately.

1. set the default maximum number of connections during compilation

Open the MySQL source code, enter the SQL directory, and modify the mysqld. cc file:
The code is as follows:
{"Max_connections", OPT_MAX_CONNECTIONS,
"The number of simultaneous clients allowed.", (gptr *) & max_connections,
(Gptr *) & max_connections, 0, GET_ULONG, REQUIRED_ARG, 100, 1, 16384, 0, 1,
0 },
The red "100" is the default value of this parameter. change it to the expected value and save the disk and exit. Then execute
The code is as follows:
./Configure; make install
Recompile and install MySQL. Note that this operation is best performed before MySQL installation because MySQL source code has been compiled and installed and modified;

2. set the max_connections value in the configuration file my. cnf.

Open MySQL configuration file my. cnf
The code is as follows:
[Root @ www ~] # Vi/etc/my. cnf
Locate max_connections and change it to (if not, add it by yourself ),
The code is as follows:
Max_connections = 1000
The above 1000 is the value of this parameter.

3. modify the value of this parameter in real time (temporary ).

First, log on to mysql and run the following command:
The code is as follows:
[Root @ www ~] # Mysql-uroot-p
Enter the MySQL Root password.

View the current value of Max_connections:
The code is as follows:
Mysql> SELECT @ MAX_CONNECTIONS AS 'Max Connections ';
Set the value of this parameter:
The code is as follows:
Mysql> set GLOBAL max_connections = 1000;
(Note the case sensitivity of the above Command)

The modification takes effect immediately without restarting MySQL.

In general, this parameter should be set as large as possible when the server resources are sufficient to meet the simultaneous connection needs of multiple clients. Otherwise, an error similar to "Too connector connections" will occur.