MySQL Open Files Limit

and umm... systemd

So, very often, I come across a super annoying aspect of MySQL on so-called "modern" Linux (so-called because systemd doesn't render something suddenly up to date) where MySQL will not obey configuration variables set in its cnf file. The most annoying of these is `open_files_limit` because it's read only once MySQL starts. This means: it will not obey the cnf, and I cannot change it after start... so it's now what? Immutable? Nope.

First, make sure that you do not have a lower limit set in either /etc/security/limits.conf or in /etc/sysctl.conf. If either of those is set lower than what you are trying to set in MySQL, you will have some problems. If you have no limits set within limits.conf, you can add something like:

mysql hard nofile 8192
mysql soft nofile 8192

Naturally, you would replace 8192 with whatever limit you wish to impose. Next, we need to set the variable in the SystemD service file. On Ubuntu, this is something like: /etc/systemd/system/multi-user.target.wants/mysql.service. You will simply want to add:

LimitNOFILE=8192

Then you need to:

sudo systemctl daemon-reload 
sudo systemctl restart mysql.service

With this complete, you should now be able to view the variable:

mysql -e "show variables like '%open_files_limit%';"

While certain things are always going to be annoying, MySQL's unwillingness to obey the system administrator is not one of those things.