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

Posted

in

, , ,

by

Tags:

Comments

12 responses to “MySQL Max_connections stuck on 214 ?”

  1. Hernan Avatar
    Hernan

    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. David Goodwin Avatar

    Hernan – it’s just a case of MySQL looking to see how many open files it can create, and limiting the value of max_connections to be based on that.

    See also http://blog.endpoint.com/2013/12/increasing-mysql-55-maxconnections-on.html

  3. wangcong Avatar
    wangcong

    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…..

  4. Vincent Avatar
    Vincent

    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 🙂

  5. Gabriel Avatar
    Gabriel

    Thank you very much! You are a hero to me :D!.

  6. clayanddev Avatar
    clayanddev

    Thank you, that helps a lot!

  7. HRH Prince Sven Olaf von CyberBunker Avatar
    HRH Prince Sven Olaf von CyberBunker

    ‘default settings’ suitable for 386 laptops from 1993. not for the average desktop of 2017. much less the average server. handling one filedescriptor = 4kb ram. woopetiedoo. now go tell those debian nerds to fix it and change it to what? 10 million? something ‘acceptable’ in that range. as a default. not 100 or 214… the -entire point- of a relational database is to have 10000s of clients accessing the data R/W at the same time, unlike, let’s say, some direct file based system. if it doesn’t do that by default we might just as well use files. d0h.

  8. HRH Prince Sven Olaf von CyberBunker Avatar
    HRH Prince Sven Olaf von CyberBunker

    you know… nowadays you REALLY just EXPECT such things to just work out of the box ‘oh we wanna insert a few million rows from a few 10000 processes at the same time’. right NOW. it’s what sql databases were invented for. these here limits remind me of DOS… “will it work? oh i don’t know… maybe we need to configure it more before it can do it’s only job it was designed to do without unexpected errors and disconnects all over the place”… quite sure nobody runs this stuff on a microcontroller… on any actual computer, what it’s compiled for, the defaults should be -millions- not 100s.

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

Leave a Reply

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