In CentOS 7, MySQL connections are limited to 214. centosmysql

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.