Shulou(Shulou.com)06/01 Report--

This article mainly introduces the concept, function and modification of the maximum number of connections in mysql, and what it involves is learned from theoretical knowledge. There are many books and documents for your reference. From the perspective of practical significance, the accumulated practical experience for many years can be shared with you.

The maximum number of connections to a CVM at the same time, that is, there can be 1000 users, but only 100 users can access the database at the same time, that is, the maximum number of processes that respond to client connections. Note that one connection process is not the number of computers. After missing the maximum number of connections, the new database connection cannot initiate the old one to remain normal.

What is the reason for the sudden increase in the number of database connections?

It may be that the performance of the database suddenly slows down, and it takes a long time for the connected customer to get a response, and the customer thinks that he or she has not confirmed it, so the customer keeps connecting, so the session increases, the database becomes busier, and may eventually hang up.

How to modify it?

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

Features related to max_connections

In any case, MySQL retains a connection for administrator (SUPER) login for administrator to connect to the database for maintenance operations, even if the current number of connections has reached max_connections. Therefore, the actual maximum number of connections available for MySQL is max_connections+1.

The maximum value of this parameter that actually works (the actual maximum number of connections) is 16384, that is, the maximum value of this parameter cannot exceed 16384, and even if it exceeds 16384, it shall prevail.

Increasing the value of the max_connections parameter does not take up too much system resources. The occupation of system resources (CPU, memory) mainly depends on the density and efficiency of the query.

The most obvious feature of the small setting of this parameter is the "Too many connections" error.

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

The following sql

The copy code is as follows:

Show variables like "max_connections"

The results displayed are in the following format

+-+ +

| | Variable_name | Value |

+-+ +

| | max_connections | 100 | |

+-+ +

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

Set global max_connections = 200

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

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

Max_connections=200

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

However, for the sake of security, it is recommended that you modify it directly in my.ini. Nothing can be added.

Adjust 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 directly with the command and take effect immediately.

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

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

The copy 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 desired value, and save the disk to exit. Then execute

The copy code is as follows:

. / configure;make;make install

Recompile and install MySQL; Note: due to the compilation installation and modification of the MySQL source code, this is best done before installing MySQL

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

Open the MySQL configuration file my.cnf

The copy code is as follows:

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

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

The copy code is as follows:

Max_connections = 1000

The 1000 above is the value of this parameter.

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

First log in to mysql and execute the following command:

The copy code is as follows:

[root@www] # mysql-uroot-p

Then enter the password for MySQL Root.

View the current Max_connections parameter values:

The copy code is as follows:

Mysql > SELECT @ @ MAX_CONNECTIONS AS 'Max Connections'

Set the value of this parameter:

The copy code is as follows:

Mysql > set GLOBAL max_connections=1000

(note the case of the above command)

The modification takes effect in real time, and there is no need to restart MySQL.

In general, 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.

After reading the introduction of the concept, function and modification of the maximum number of connections in mysql above, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.