Cant increase MAX_CONNECTIONS mysql on Ubuntu, stuck at 214

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.

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     |
+----------------------------+----------+

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:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

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.

Where are packages installed in Ubuntu : Debian Derivatives

Ubuntu, which is a Debian derivative, follows a very precise structure when installing packages. In other words, all software installed through the packaging tools, such as apt-get or synaptic, will put the stuff in the same locations. If you become familiar with these locations, you’ll always know where to find your stuff.

As a short cut, you can always open a tool like synaptic, find the installed package, and inspect the “properties”. Under properties, you’ll see a list of all installed files. Again, you can expect these to always follow the Debian/Ubuntu conventions; these are highly ordered Linux distributions. IN short, binaries will be in /usr/bin, or some other location on your path ( try ‘echo $PATH’ on the command line to see the possible locations ). Configuration is always in a subdirectory of /etc. And the “home” is typically in /usr/lib or /usr/share.

For instance installing maven,

sudo apt-get install maven

The Apt-get installation will install all the required files in the following folder structure

/usr/bin/mvn

/usr/share/maven2/

/etc/maven2

P.S The Maven configuration is store in /etc/maven2

Note, it’s not just apt-get that will do this, it’s any .deb package installer.