I'm using MySQL 5.7.29 on Ubuntu 16.04. I'd like to increase the number of connections allowed by the MySQL server. This involves setting configuration for both MySQL and Ubuntu.
The primary config parameter is MySQL's
max_connections. I've edited
/etc/mysql/mysql.conf.d/mysqld.cnf to have
max_connections = 800
(previously it was commented out and set to 100). I also need to make sure that
table-open-cache is high enough; for our application that's about
2 x max_connections plus a little extra.
$ mysqld --verbose --help reports
which should be high enough.
For every open connection, MySQL opens a placeholder file for it. Thus, in addition to increasing MySQL's
max_connections, I need to make sure that the OS allows the MySQL process to open a sufficient number of files.
Existing answers for doing this divide into
systemd and non-
systemd approaches. This system uses
systemd approach involves editing
/etc/security/limits.conf. I followed instructions for doing this before I learned that
systemd ignores this file for this case. The original version of this question concerned that effort; I've edited it to remove what I now know is irrelevant. I'm happy to restore information about what I did if anyone thinks it's relevant. Long story short is that I successfully increased the output of
$ ulimit -n from 1024 to 16384.
Raising the number of files MySQL can open on
systemd requires editing the MySQL service file.
systemd MySQL service file on this system is
/lib/systemd/system/mysql.service. Rather than editing the file directly, which I understand is bad practice, I created the directory
/etc/systemd/system/mysql.service.d/ and the file
override.conf within it. To
override.conf, I added
[Service] LimitNOFILE=65536 LimitMEMLOCK=infinity
I also added
fs.file-max = 65536 to
/etc/sysctl.conf, though I'm still not clear on what relation that file has to
systemd or whether it has any effect on a
systemd config (
$ sudo systemctl daemon-reload) and MySQL (
$ sudo service mysql restart).
Checking to see if it worked, I note that
$ service mysql status indicates that the new
override.conf file is being recognized:
$ sudo service mysql status ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled Drop-In: /etc/systemd/system/mysql.service.d └─override.conf ...
However, MySQL continues to report a value of 151 for
$ sudo mysqld --verbose --help | grep max-connections --max-connections=# The number of simultaneous clients allowed max-connections 151
(I get the same result checking
mysql> SHOW VARIABLES LIKE 'max_connections' from within the interactive MySQL shell).
What else do I need to do to permanently increase