MySQL Max_connections stuck on 214 ?

I found MySQL was being annoying earlier and not ‘accepting’ my max_connections = 450 directive on a Debian Wheezy install, and being seemingly stuck on having 214 connections….

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

6 thoughts on “MySQL Max_connections stuck on 214 ?”

  1. Hey David, thanks for clearing this out!

    One question, how are those values in /etc/security/limits.conf related to the max connections I’d like to set?

    I mean, how are they calculated?

    Thanks a lot in advance!

  2. Hi,following your steps, i found that, i can only set 818 max-connections in mariadb. if the number is bigger than 818, the max-connection in mariadb still is 214..
    i don’t know why…..

  3. Thank you so much, I did face this problem after upgrading from mysql 5.5 to mysql 5.7, you solution did work and saved me from this technical nightmare 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *