Analysis of max_connections configuration parameters of Mysql performance optimization _mysql

The mysql max_connections parameter is used to set the maximum number of connections (users). Each user connected to MySQL counts as a connection, and the default value of Max_connections is 100. This article will explain the detailed action and performance impact of this parameter.

Characteristics related to Max_connections

MySQL anyway retains a connection for administrator (SUPER) landing, which is used by 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 for MySQL is max_connections+1;
The maximum value of the actual function of this parameter (the actual maximum number of connections) is 16384, that is, the maximum value of the parameter can not exceed 16384, even if more than 16384;
Increases the value of the Max_connections parameter and does not consume too many system resources. The utilization of system resources (CPU, memory) depends mainly on the density and efficiency of the query;
The most obvious feature of this parameter setting is too small is the "Too many connections" error;

Let's take a look at how to view the current MySQL max_connections values:

The following SQL

Copy Code code as follows:

Show variables like "max_connections";

The results shown are formatted as follows

+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+

You can set the value of Max_connections to 200 by using the following SQL statement, provided that the currently logged-on user has sufficient permissions:

Set global max_connections = 200;

This setting will take effect immediately, but this setting will fail when MySQL restarts, and a better way is to modify the MySQL ini configuration file My.ini

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

max_connections=200

After this modification, even restarting MySQL will load this configuration by default.

However, in order to be safe, we suggest that you go directly to the My.ini to modify, there can be added.

Adjust the value of the Max_connections parameter

There are several ways to adjust this parameter, either at compile time or in the MySQL profile my.cnf, or you can use the command to adjust and take immediate effect.

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

Open the source of MySQL, enter the SQL directory, modify the mysqld.cc file:

Copy Code code 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 for the parameter, modified to the desired value, and the disk is withdrawn. And then execute

Copy Code code as follows:

./configure;make;make Install

Recompile install MySQL; Note that because of the compilation and modification of MySQL source code, this operation is best done before installing MySQL;

2, set the Max_connections value in the configuration file my.cnf

Open MySQL configuration file my.cnf

Copy Code code as follows:

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

Find Max_connections row, modify to (if not, add yourself),

Copy Code code as follows:

max_connections = 1000

The above 1000 is the value of the parameter.

3, real-time (temporary) modify the value of this parameter

First login MySQL, execute the following command:

Copy Code code as follows:

[Root@www ~]# Mysql-uroot-p

Then enter the password for the MySQL root.

To view the current max_connections parameter values:

Copy Code code as follows:

Mysql> SELECT @ @MAX_CONNECTIONS as ' MAX connections ';

Set the value of this parameter:

Copy Code code as follows:

Mysql> set GLOBAL max_connections=1000;

(Note the case of the above command)

It takes effect immediately after the modification is complete without restarting MySQL.

Overall, this parameter should be set as large as possible when the server resources are sufficient to meet the requirements of simultaneous connections from multiple clients. Otherwise, there will be an error similar to "Too many connections".
In general, according to the number of simultaneous online set a more comprehensive number, we set the 10000.