Could not increase number of max_open_files to more than… (Solution)

A quick solution to the warning “Could not increase number of max_open_files to more than” when starting MySQL or MariaDB. For some background, read How MySQL Opens and Closes Tables. Here’s an excerpt: “The table_open_cache and max_connections system variables affect the maximum number of files the server keeps 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 permit you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so.”

Also, read MySQL’s open_files_limit documentation, which states: “The number of files that the operating system permits mysqld to open. The value of this variable at runtime is the real value permitted by the system and might 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. …The effective open_files_limit value is based on the value specified at system startup (if any) and the values of max_connections and table_open_cache. The server attempts to obtain the number of file descriptors using the maximum of those three values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit.”

Too many open files

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

Could not increase number of max_open_files to more than 65536

This means the limit is being hit somewhere. Let’s resolve that by editing any configured limits. Have 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 config lines:

LimitNOFILE=
LimitMEMLOCK=

Change these lines to your new limit. For example:

LimitNOFILE=100000
LimitMEMLOCK=100000

Please don’t use the infinity as a value; it defaults to 65536. (as reported here) So if you see the value infinity, replace it with your required limit.

Update: The related MySQL or MariaDB 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 Update: for MySQL 8, check /usr/lib/systemd/system/mysqld.service then edit or add the new limit there:

LimitNOFILE=100000

Once you’ve done this, 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 16th, 2019 | Updated: August 5th, 2022

Tags: , , , , , ,

Comments

  1. Today I had this issue on a Ubuntu 20.04 LTS server with MySQL 8.0.30 installed:

    Could not increase number of max_open_files to more than 10000

    I found the file here:

    /usr/lib/systemd/system/mysqld.service

    I edited to 100000:

    Then ran:

    systemctl daemon-reload
    systemctl restart mysql

    Updated the article to reflect this.

  2. Just don’t forget to check if your distro uses BubbleWrap. When I got the error it turned out to be that instead and all I had to do modify a file to allow more namespaces. As per the bug in Debian I filed: “This is a more general Linux kernel parameter, not specific to bubblewrap, which affects bubblewrap and anything else that relies on Linux namespaces. Installing the bubblewrap package does not change it.
    It might be set to 0 during boot by /etc/sysctl.conf, a file in /etc/sysctl.d, or a file in /usr/lib/sysctl.d, in which it’s referred to as “user.max_user_namespaces”.It might also be changed by some privileged program writing to /proc/sys/user/max_user_namespaces directly, or running the sysctl command.”

    I asked: So what is the default for this with 8GB RAM?

    Which I received this reply: “I don’t know specifically, but I’d guess slightly more than 30000 (it’s 63652 on a system with 16G RAM).” and I divided his setting by 2 and put that for a setting and haven’t had to mess with it since.

    EDIT: Fixed formatting so it was readable.

  3. Thanks for adding this for those who will no doubt have that issue also.

Join the discussion - post a comment.

Participants