Could not increase the number of max_open_files beyond… (Solution)

quick resolution of warnings “Could not exceed the number of max_open_files” When starting MySQL or MariaDB. read on for some background How MySQL Opens and Closes Tables, Here is an excerpt: “The table_open_cache and max_connections system variables affect the maximum number of files a server can keep open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems allow you to increase the open-files limit, although the method varies widely from system to system. See your operating system’s documentation to determine if it is possible to increase the limit and how to do so.

Also read MySQL open_files_limit documentationWhich state: “Number of files with operating system permissions mysqld to open. The value of this variable at runtime is the actual value allowed by the system and may be different from the value you specify at server startup. The value is 0 on systems where MySQL cannot change the number of open files. …effective open_files_limit The value is based on the value specified at system startup (if any) and the values ​​of max_connections And table_open_cache, The server tries to get the number of file descriptors using the maximum of those three values. If so many descriptors cannot be obtained, the server tries to acquire as many as the system will allow. ,

So, for example, if MySQL starts up with the following warning:

Could not increase number of max_open_files to more than 65536

…that means the limit is being hit somewhere. Let’s solve this by editing any configured limits. Take a look at the following files:

/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
/etc/systemd/system/mysqld.service.d/limits.conf
/usr/lib/systemd/system/mariadb.service
/usr/lib/systemd/system/mariadb.service
/etc/systemd/system/mysql.service
/etc/systemd/system/mysqld.service

Look within those files for the following configuration lines:

LimitNOFILE=
LimitMEMLOCK=

Change these lines to your new range. for example:

LimitNOFILE=100000
LimitMEMLOCK=100000

please do not use infinity as a value; It is 65536 by default. ,as described here) so if you look at the value infinityReplace it with your required range.

update: Related MySQL or MariaDB The files may be in other locations on non-RHEL systems. For example, on Ubuntu create a new file /etc/systemd/system/mysql.service.d/override.conf Then add new border there:

LimitNOFILE=100000

Once this is done, you should now reload the system daemon and restart the MySQL service:

systemctl daemon-reload
systemctl restart mysql

To check the new limit via the MySQL command line. You can use the following query:

SHOW VARIABLES LIKE 'open_files_limit';

Make sure your new limit is set:

MariaDB [(none)]> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 100000 |
+------------------+--------+

Published: November 16, 2019
Updated: November 1, 2021

Leave a Comment