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 | +----------------------------+----------+
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!
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
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…..
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 🙂
Thank you very much! You are a hero to me :D!.
Thank you, that helps a lot!
‘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.
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.