Brief analysis of max_connections configuration parameters for MySQL performance Optimization

The max_connections parameter of MySQL is used to set the maximum number of connections (users). Each user connecting to MySQL counts as one connection, and the default value for max_connections is 100. This article explains the detailed effects and performance implications of this parameter.

Features related to max_connections

MySQL in any case reserves one connection for the administrator (SUPER) login for the administrator to connect to the database for maintenance operations, even though the current number of connections has reached max_connections. Therefore, the actual maximum connectable number of MySQL is max_connections+1.
The actual maximum value of this parameter (the actual maximum connectable number) is 16384, that is, the maximum value of this parameter cannot exceed 16384, and if it does, 16384 shall prevail.
Increasing the value of the max_connections parameter does not consume too much system resources. The occupancy of system resources (CPU, memory) mainly depends on the density and efficiency of query.
The most obvious feature of setting this parameter too small is the "Too many connections" error.

Let's first look at how to view the current mysql's max_connections value:

The following sql

show variables like "max_connections";

The results are shown in the following format

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

You can set the value of max_connections to 200 by using the following statement, provided, of course, that the user currently logged in has sufficient permissions:

set global max_connections = 200;

This setting will take effect immediately, but will fail when mysql is rebooted. A better approach is to modify my.ini's ini profile

Find the mysqld block and modify or add the following Settings:

max_connections=200

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

However, for the sake of safety, it is suggested that you go directly to ES78en. ini for modification. Can you add it?

Adjusts the value of the max_connections parameter

There are several ways to adjust this parameter, either at compile time, in the MySQL configuration file my.cnf, or by using the command directly and taking effect immediately.

1. Set the default maximum number of connections at compile time

Open the source code of MySQL, enter sql directory, modify 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, change to the desired value, save and exit. Then perform

./configure;make;make install

Recompile and install MySQL; Note that since the MySQL source code has been compiled and installed and modified, this is best done before installing MySQL;

2. Set the value of max_connections in configuration file ES108en.cnf

Open the MySQL configuration file ES115en.cnf

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

Find the max_connections1 line, change it to (if not, add it yourself),

max_connections = 1000

The 1000 above is the value of the parameter.

3. Real-time (temporary) change the value of this parameter

First log in mysql and execute the following command:

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

Then enter the password for MySQL Root.

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 changes take effect in real time after completion, without the need to restart MySQL.

In general, this parameter should be set as large as possible if the server resources are sufficient to allow multiple clients to connect simultaneously. Otherwise an error similar to "Too many connections" will occur.
1. Generally, we set a relatively comprehensive number according to the number of people online at the same time, and the number we set is 10000.