In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >
Share
Shulou(Shulou.com)06/02 Report--
This article will explain in detail how to achieve the maximum number of connections in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.
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.
I. Preface
You may encounter the exception of MySQL: ERROR 1040: Too many connections "in the project; Why: one reason for this is that the traffic is too high and the MySQL server cannot resist it, so you should consider increasing the distributed reading pressure from the server; another reason is that the max_connections value in the MySQL configuration file is too small.
Second, knowledge points
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 of max_connections varies slightly from version to version.
Mysql5.5 mysql5.6 mysql5.7: the default maximum number of connections is 151and the upper limit is 100000
Mysql5.1 varies according to its minor version, the default maximum number of connections and the upper limit of the number of connections that can be modified
Mysql5.0 version: the default maximum number of connections is 100 and the upper limit is 16384
Complain about the naming of the mysql version, jumping from 5.7 to 8.0. Mysql5.5 mysql5.6 mysql5.7: the default maximum number of connections is 151s. This number is far from enough for database applications with a lot of concurrent connections. When the connection request is greater than the default number of connections, there will be an error that cannot connect to the database, so we need to make it larger appropriately. A problem often encountered when using a MySQL database is a "Can not connect to MySQL server. Too many connections"-mysql 1040 error, which is because the number of connections that have not been released to MySQL has reached the MySQL limit. 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 that can be connected to MySQL increases the value of the max_connections parameter for max_connections+1;, which will not take up too much system resources. The occupation of system resources (CPU, memory) mainly depends on the density and efficiency of the query, and the most obvious feature of the small setting of this parameter is the "Too many connections" error.
III. Practical operation
1. View the maximum number of connections
Mysql5.5 mysql5.6 mysql5.7: the default maximum number of connections is 151and the upper limit is 100000
Mysql > show variables like "% max_connections%"; ±- ±- + | Variable_name | Value | ±- ±- + | max_connections | 151 | ±- ±- + 1 row in set (0.00 sec)
2. View the maximum number of connections responded by the server
Mysql > show global status like 'Max_used_connections' +-- +-+ | Variable_name | Value | +-- +-+ | Max_used_connections | 11 | +-- +-+ 1 row in set (0.00 sec)
You can see that the maximum number of connections the server responds to is 11, which is much lower than the maximum number of connections allowed by the mysql server. The ideal setting range for the maximum connection value of the mysql server is that the maximum connection value responded by the server accounts for more than 10% of the upper limit connection value of the server. If it is less than 10%, the setting of the maximum connection limit value of the mysql server is too high.
3. Modify the maximum number of connections
Two common ways to modify the maximum number of connections are as follows:
First: the command line modifies the maximum number of connections (max_connections) to set the maximum number of connections to 1000.
Mysql > set global max_connections = 1000
There is a problem with this approach, that is, the maximum number of connections set is only valid for the current MySQL service process, and once MySQL is restarted, it will return to its initial state. Because the initialization work of MySQL after startup reads data from its configuration file, and no changes are made to its configuration file in this way.
Second: modify the maximum number of MySQL connections (max_connections) by modifying the configuration file.
Enter the MySQL installation directory, open the MySQL configuration file my.ini or my.cnf to find max_connections=100, modify it to max_connections=1000, and restart the MySQL service.
This is the end of the article on "how to achieve the maximum number of connections in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.