Solution to limit the number of MySQL connections to 214 in CentOS 7

Find a problem

Recently, I encountered a problem in the project. Because there are too many connections, it is suggested that "Too many connections" needs to increase the number of connections.

I changed the/etc/my. cnf:

max_connections = 2000

However, the actual number of connections 1 is limited to 214:

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

Thinking

If I set connections to less than 214, say 200, then the actual number of connections is 200, which means my profile is fine.

Check the official document of MySQL, which says:

The maximum number of connections MySQL can support depends on on quality of the given platform platform platform platform platform platform platform platform platform the for for for each connection, the workload from each N Solaris should be able to support at 500 to 1000 simultaneous connections routinely and have have have have have many is is is is is is is is is is is is is is is 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 to raise the operating system limit how many can be used by by MySQL MySQL can be be used used

The maximum number of connections that MySQL can support 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 files opened.

Solution

[root@sqzr ~]# ulimit -n
1024

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

Change the maximum file descriptor limit of MySQL in Linux, edit /usr/lib/systemd/system/mysqld.service File, add at 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, so solve it

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

Summarize