How to change open_files_limit in MySQL

I will give an example of viewing and increasing the open_files_limit parameter in MySQL.
Will perform operations in the Ubuntu Server 16.04.

First, let’s see how many files are currently open:

sudo lsof -u mysql | wc -l

Connect to the MySQL server and examine the maximum value (default 1024):

mysql -u root -p
show variables like "open_files%";
exit

If it is necessary to increase, then we will see if the parameter is already specified and open the MySQL server configuration file in a text editor:

sudo grep -r "open_files_limit" /etc
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

And for example, we specify:

open_files_limit=20480

We will also increase the system limit for users by adding to the /etc/security/limits.conf file:

* hard nofile 30720
* soft nofile 20480
root hard nofile 30720
root soft nofile 20480
mysql hard nofile 30720
mysql soft nofile 20480

Also add to the “Service” section in the /etc/systemd/system/multi-user.target.wants/mysql.service file (you can specify “infinity” to remove the restriction):

LimitNOFILE=20480

Restart the MySQL server to apply the changes (on the go, applying through “set global …” will not work, since “open_files_limit” is read-only):

sudo systemctl daemon-reload
sudo systemctl restart mysql.service

If you do not increase in the system, then after restarting MySQL in /var/log/mysql/error.log there will be an error and the value will remain the same:

[Warning] Could not increase number of max_open_files to more than 1024 (request: 20480)

See also my article:
How to change “open files” in Linux

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a comment

Leave a Reply