What is the maximum number of MySQL connections? What's the use? How to modify?

Server simultaneous maximum number of connections, that is, can have 1000 users, but only 100 users to access the database, that is, the maximum number of processes in response to client connections note that there is a connection process count is not the number of computer. After you miss the maximum number of connections, the new database connection fails to start the old and remains normal.

What is the reason why the number of database connections has suddenly increased?

May be the database performance suddenly slow, the connection of the customer to get a response, the customer thought it was not confirmed, so the customer is constantly connected, so that the session is increased, the database is more busy, the end may be hung.

How to modify?

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 for Max_connections is 100. This article explains the detailed effects and performance implications of this parameter.

Features related to Max_connections

MySQL will keep a connection for administrator (SUPER) login In any case, 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;

Let's look at the value of how to view the current MySQL max_connections:

The following SQL

Copy the Code code as follows:

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 SQL statement, if 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, a better way is to modify the MySQL ini configuration file My.ini

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

max_connections=200

After this modification, the configuration will be loaded by default even if you restart MySQL.

However, in order to secure the period, we recommend that we go directly to the My.ini to modify, you can add.

Adjust the value of the Max_connections parameter

There are several ways to adjust this parameter, either at compile time or in MySQL configuration file my.cnf, or directly with the command adjustment and immediate effect.

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

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

Copy the 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 exits. And then execute

Copy the Code code as follows:

./configure;make;make Install

Recompile install MySQL; Note that this is best done prior to installing MySQL due to the installation and modification of MySQL source code;

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

Open MySQL configuration file my.cnf

Copy the Code code as follows:

[Email protected] ~]# VI/ETC/MY.CNF

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

Copy the Code code as follows:

max_connections = 1000

The 1000 above is the value of the parameter.

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

First log in to MySQL and execute the following command:

Copy the Code code as follows:

[Email protected] ~]# mysql-uroot-p

Then enter the password for MySQL root.

To view the current max_connections parameter values:

Copy the Code code as follows:

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

Set the value of this parameter:

Copy the Code code as follows:

Mysql> set GLOBAL max_connections=1000;

(Note the upper case of the command)

After the modification is complete, it takes effect in real time without restarting MySQL.

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".

What is the maximum number of MySQL connections? What's the use? How to modify?