In CentOS 7, MySQL connections are limited to 214. centosmysql
Problems Found
Recently, I encountered a problem in the project. Due to the excessive number of connections, "Too has connections" is displayed, and the number of connections needs to be increased.
I modified the following in/etc/my. cnf:
max_connections = 2000
However, the actual number of connections is always limited to 214:
mysql> show variables like "max_connections";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 214 |+-----------------+-------+1 row in set
Thoughts
If I set the connection to be less than 214, for example, 200, the actual number of connections is 200. That is to say, my configuration file is correct.
Check the MySQL official document, which says:
The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. linux or Solaris shoshould be able to support at 500 to 1000 simultaneous connections routinely and as same as 10,000 connections if you have your gigabytes of RAM available and the workload from each is low or the response time target undemanding. windows is limited to (open tables × 2 + open connections) <2048 due to the Posix compatibility layer used on that platform.
Increasing open-files-limit may be necessary. Also see Section 2.5, "Installing MySQL on Linux", for how to raise the operating system limit on how many handles can be used by MySQL.
It generally means that the maximum number of connections supported by MySQL is limited by the operating system, and open-files-limit can be increased if necessary. In other words, the number of connections is related to the number of opened files.
Solution
[root@sqzr ~]# ulimit -n1024
The maximum file descriptor of the operating system is 1024.
Modify the maximum file descriptor limit of MySQL in Linux/usr/lib/systemd/system/mysqld.service
File, add at the end of the file:
LimitNOFILE=65535LimitNPROC=65535
After saving the configuration, run the following command to make the configuration take effect.
$ systemctl daemon-reload$ systemctl restart mysqld.service
The actual number of connections reaches 2000.
mysql> show variables like "max_connections";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 2000 |+-----------------+-------+1 row in set
Reference
Https://dev.mysql.com/doc/refman/5.7/en/too-many-connections.html
Https://www.oschina.net/question/853151_241231
Summary
The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.