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.
MySQL
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
table-open-cache 2000
which should be high enough.
Ubuntu
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
.
non-systemd
The non-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.
systemd
Raising the number of files MySQL can open on systemd
requires editing the MySQL service file.
The 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
system.
I reloaded 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 max_connections
:
$ 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 max_connections
?
Best Answer
Welcome.
As per MySQL Documentation, your MySQL server does not have enough resources.
Also go through this