How to use the max_connections configuration parameter to perform performance optimization on Mysql

MySQL’s max_connections parameter is used to set the maximum number of connections (users). Each user connecting to MySQL counts as one connection, and the default value of max_connections is 100. This article will explain the detailed role and performance impact of this parameter.

Features related to max_connections

MySQL will retain a connection for administrator (SUPER) login no matter what, for the administrator to connect to the database. Maintenance operation, even if the current number of connections has reached max_connections. Therefore, the actual maximum number of connections that can be made in MySQL is max_connections+1;
The actual maximum value of this parameter (the actual maximum number of connections) is 16384, that is, the maximum value of this parameter cannot exceed 16384. Even if it exceeds, 16384 will prevail. ;
Increase the value of the max_connections parameter and will not occupy too many system resources. The occupancy of system resources (CPU, memory) mainly depends on the density and efficiency of the query;
The most obvious symptom of setting this parameter too small is the occurrence of "Too many connections" errors;

We Let’s first look at how to check the current value of mysql’s max_connections:

The following sql

show variables like "max_connections";

displays the results in the following format

+------- ----------+-------+
| Variable_name | Value |
+-----------------+- ------+
| max_connections | 100 |
+-----------------+-------+

The value of max_connections can be set to 200 through the following sql statement. Of course, the premise is that the currently logged in user has sufficient permissions:

set global max_connections = 200;

This setting will take effect immediately , but this setting will become invalid when mysql restarts. A better way is to modify mysql's ini configuration file my.ini

Find the mysqld block, modify or add the following settings:

max_connections= 200

After this modification, even if mysql is restarted, this configuration will be loaded by default

However, for the sake of safety, it is recommended that you modify it directly in my.ini. If there is anything you can add.

Adjust the value of the max_connections parameter

There are several ways to adjust this parameter, which can be set during compilation or in the MySQL configuration file my.cnf Settings can also be adjusted directly using commands and take effect immediately.

1. Set the default maximum number of connections during compilation

Open the source code of MySQL, enter the sql directory, and modify the mysqld.cc file:

{"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. Modify it to the desired value, save and exit. Then execute

./configure;make;make install

to recompile and install MySQL; note that since the MySQL source code is compiled, installed and modified, this operation is best performed before installing MySQL;

2. In the configuration file my Set the value of max_connections in .cnf

Open the MySQL configuration file my.cnf

[root@www ~]# vi /etc/my.cnf

Find the max_connections line and modify it to (if not, add it yourself),

max_connections = 1000

The 1000 above is the value of this parameter.

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

First log in to mysql and execute the following command:

[root@www ~]# mysql -uroot -p

Then enter the MySQL Root password .

View the current Max_connections parameter value:

mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';

Set the value of this parameter:

mysql> set GLOBAL max_connections=1000;

(note the case of the above command)

The modification will take effect in real time without restarting MySQL.

Generally speaking, this parameter should be set as large as possible when the server resources are sufficient to meet the needs of multiple clients connecting at the same time. Otherwise, an error similar to "Too many connections" will occur.
Generally, a more comprehensive number is set based on the number of people online at the same time. We set it to 10000.

The above is the detailed content of How to use the max_connections configuration parameter to perform performance optimization on Mysql. For more information, please follow other related articles on the PHP Chinese website!