Increase MySQL max_connections when it stucking as 214 on Fedora/CentOS/Debian

Ngày 13 tháng 10 năm 2017 Trương Chương Dương
If you found MySQL was being annoying earlier and not "accepting" the max_connections = XYZ directive and being seemingly stuck on having 214 connections, and you are sure your file "my.conf" and all other configuration file are correct, let think about the OS file limitation.

For Debian, please read this.
If you are using Fedora/CentOS with systemd to manage the mysql service, let do following steps:

Let change the file /etc/security/limits.conf as same as above step for Debian to adjust the maximum number of file handles (nofile) levels appropriately – which fixes the problem of the system not allowing MySQL to use more file handles for more connections.

e.g.

/etc/security/limits.conf :

.... other stuff ...

*   hard    nofile  8192
*   soft    nofile  4096

(If you then login to the system again, and run ‘ulimit -a’ you’ll see the new numbers appear)

Next – reconfigure MySQL :

/etc/my.cnf:

[mysqld]
....
max_connections = 450
....
DO NOT start/restart mysqld for now, it still doesn't accept your configuration.
Please edit the file /usr/lib/systemd/system/mysqld.service (by vim, nano or any text editor)
Search for text LimitNOFILE, change the value to infinity.
....
# Sets open_files_limit
LimitNOFILE=infinity
LimitMEMLOCK=infinity
....

Save and close that file, then run:
sudo systemctl daemon-reload

Now, you can restart mysql and see new setting.

Important note: Don't forget to bookmark this page because you have to edit the file /usr/lib/systemd/system/mysqld.service every time you update mysql-server package, so I think might you still need this info in future.

If you are on Debian/Ubuntu, choice this solution:
Debian Wheezy doesn’t have pam_limits.so enabled by default in /etc/pam.d/* files. (But it is commented out, so easy enough to put into place).

/etc/pam.d/common-session :

# without comments.
session	[default=1] pam_permit.so
session	requisite   pam_deny.so
session	required    pam_permit.so
session	required    pam_unix.so 
session required    pam_limits.so
session	optional    pam_ck_connector.so nox11
# end of pam-auth-update config

Once that’s enabled, you can edit /etc/security/limits.conf and adjust the maximum number of file handles (nofile) levels appropriately – which fixes the problem of the system not allowing MySQL to use more file handles for more connections.

e.g.

/etc/security/limits.conf :

.... other stuff ...

*   hard    nofile  8192
*   soft    nofile  4096

(If you then login to the system again, and run ‘ulimit -a’ you’ll see the new numbers appear)

Next – reconfigure MySQL :

/etc/my.cnf:

[mysqld]
....
max_connections = 450
....

and now, once MySQL is restarted :

mysql> show variables like '%max_connecti%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 450   |
+-----------------+-------+
1 row in set (0.00 sec)

and

mysql> show variables like '%open_files_limit%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| open_files_limit           | 5000     |
+----------------------------+----------+
Đang tải dữ liệu...
web site traffic statistics