Solution to the problem that the number of MySQL connections is limited to 214 in CentOS 7

Find the problem

Recently, I encountered a problem in the project. Due to too many connections, the prompt "Too many connections" appeared and I needed to increase the number of connections.

I modified 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

think

If I set the number of connections to less than 214, for example 200, then the actual number of connections is 200, which means there is no problem with my configuration file.

Check the MySQL official documentation, 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 should be able to support at 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many 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 probably means that the maximum number of connections that MySQL can support is limited by the operating system. If necessary, you can increase the open-files-limit. In other words, the number of connections is related to the number of file opens.

Workaround

[root@sqzr ~]# ulimit -n
1024

It can be seen that the maximum file descriptor limit of the operating system is 1024.

To change the maximum file descriptor limit for MySQL in Linux, edit the /usr/lib/systemd/system/mysqld.service file and add the following to the end of the file:

LimitNOFILE=65535
LimitNPROC=65535

After saving, execute the following command to make the configuration take effect

$ systemctl daemon-reload
$ systemctl restart mysqld.service

The actual number of connections has reached 2000.

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in set

refer to

https://dev.mysql.com/doc/refman/5.7/en/too-many-connections.html

https://www.oschina.net/question/853151_241231

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.