Skip to content
ServerPilot

Increase the MySQL open files limit

The open files limit is the maximum number of file descriptors the operating system will allow processes such as MySQL to have.

As file descriptors are used to identify network sockets as well as files, you may need to increase MySQL’s open files limit if you are increasing MySQL’s max_connections or table_open_cache.

Don’t set open_files_limit

By default, MySQL will automatically calculate how many open file descriptors it needs based on configured values such as max_connections and table_open_cache. MySQL will then ask the operating system to increase the number of open file descriptors it is allowed to have.

You should not set a value for MySQL’s open_files_limit as that will prevent MySQL from calculating the correct value.

To increase the number of open files MySQL is allowed to have, you only need to remove the limit that’s imposed on MySQL by the operating system’s service manager (systemd).

Change LimitNOFILE for the MySQL service

In systemd, the LimitNOFILE setting restricts the maximum number of open files a service can have. The setting is called LimitNOFILE because “NOFILE” stands for “Number of Open Files”.

To allow MySQL to choose its own open files limit, create the file:

/etc/systemd/system/mysql.service.d/open_files_limit.conf

with the contents:

/etc/systemd/system/mysql.service.d/open_files_limit.conf
[Service]
LimitNOFILE=infinity

Reload the systemd configuration.

Terminal window
sudo systemctl daemon-reload

And then restart MySQL.

Terminal window
sudo service mysql restart

Confirm MySQL’s open files limit is not restricted

You can check if MySQL was prevented from raising its open files limit by running the following command:

Terminal window
sudo grep "Changed limits" /var/log/mysql/error.log

If you see no output from the above command, then MySQL was not prevented from setting its own open files limit to the value it needed.

If you see output such as the following, MySQL detected it could not set its open files limit to the necessary value.

2024-06-08T19:33:23.022255Z 0 [Warning] Changed limits: max_open_files: 10000 (requested 16161)
2024-06-08T19:33:23.022414Z 0 [Warning] Changed limits: table_open_cache: 4919 (requested 8000)

In the example log messages above, MySQL calculated it needed an open files limit of 16161 but was restricted to only 10000 by systemd. As a result, MySQL was forced to decrease the open table cache size.