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%";

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:


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/ file (you can specify “infinity” to remove the restriction):


If MariaDB is used, then we will specify in the file /etc/systemd/system/mariadb.service.d/override.conf

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

Leave a comment

Leave a Reply