On Ubuntu 16.04, increasing the max_connections variable on mysql, does not go beyong 214 connections. Initially the max_connections variable was 100, modified max_connections, under /etc/mysql/my.cnf. The change does not go beyond 214 connections, and whatever specified in max_connections it remains at 214 verified using the command show variables like ‘max_connections’.

To bring the about the change followed up with article https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/

It turns out,ubuntu 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.


/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 :


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)


mysql> show variables like '%open_files_limit%';
| Variable_name              | Value    |
| open_files_limit           | 5000     |

This did not yield expected results.

Going further to investigate, realised Ubuntu has moved from Upstart to Systemd in version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.

The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.

sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice

Add the following lines to the bottom of the file:


You could also set a numeric limit, eg LimitNOFILE=4510.

Now reload the Systemd configuration with:

sudo systemctl daemon-reload

Restart MySQL and it should now obey the max_connections directive.