← ServerPilot Docs

How to Increase the Open Files Limit for MySQL

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 MySQL's 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).

Allow MySQL to set its open files limit as needed

In systemd, the LimitNOFILE setting restricts the maximum number of open files a service can have. ("NOFILE" in "LimitNOFILE" 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:

[Service]
LimitNOFILE=infinity

Reload the systemd configuration:

sudo systemctl daemon-reload

And then restart MySQL:

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:

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.