In this blog, we’ll look at a mystery around setting the MySQL open_file_limit variable in MySQL and Percona Server for MySQL.
MySQL Server needs file descriptors to run. It uses them to open new connections, store tables in the cache, create temporary tables to resolve complicated queries and access persistent ones. If mysqld is not able to open new files when needed, it can stop functioning correctly. A common symptom of this issue is error 24: “Too many open files.”
The number of file descriptors mysqld can open simultaneously is defined by the configuration open_files_limit option. You would expect it to work like any other MySQL Server option: set in the configuration file, restart mysqld and use more or fewer descriptors. All other configuration variables work this way. But open_files_limit also depends on the operating system (OS) limits. This makes setting the variable more complicated.
mysqld
As a user, when you start an application it cannot have limits set to be greater than the limits defined by the operating system for the user in question. Therefore, you would intuitively expect mysqld to set open_files_limit to any value that is less than the OS limit. This is not the case, however. No matter what value you set for the open_files_limit variable, the OS limit is used unless it is set to “infinity”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | sveta@Thinkie:~$ ulimit -n 32000 sveta@Thinkie:$ cat /etc/my.cnf [mysqld] open-files-limit=16000 ... sveta@Thinkie:$ ./bin/mysqld & sveta@Thinkie:$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.7.19-17-debug-log Source distribution Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select @@open_files_limit; +--------------------+ | @@open_files_limit | +--------------------+ | 32000 | +--------------------+ 1 row in set (0.00 sec) |
The reason for this can be found in the code contained in the mysys/my_file.c file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | static uint set_max_open_files(uint max_file_limit) { struct rlimit rlimit; uint old_cur; DBUG_ENTER("set_max_open_files"); DBUG_PRINT("enter",("files: %u", max_file_limit)); if (!getrlimit(RLIMIT_NOFILE,&rlimit)) { old_cur= (uint) rlimit.rlim_cur; DBUG_PRINT("info", ("rlim_cur: %u rlim_max: %u", (uint) rlimit.rlim_cur, (uint) rlimit.rlim_max)); if (rlimit.rlim_cur == RLIM_INFINITY) rlimit.rlim_cur = max_file_limit; if (rlimit.rlim_cur >= max_file_limit) DBUG_RETURN(rlimit.rlim_cur); /* purecov: inspected */ rlimit.rlim_cur= rlimit.rlim_max= max_file_limit; if (setrlimit(RLIMIT_NOFILE, &rlimit)) max_file_limit= old_cur; /* Use original value */ else { rlimit.rlim_cur= 0; /* Safety if next call fails */ (void) getrlimit(RLIMIT_NOFILE,&rlimit); DBUG_PRINT("info", ("rlim_cur: %u", (uint) rlimit.rlim_cur)); if (rlimit.rlim_cur) /* If call didn't fail */ max_file_limit= (uint) rlimit.rlim_cur; } } DBUG_PRINT("exit",("max_file_limit: %u", max_file_limit)); DBUG_RETURN(max_file_limit); } |
Particularly these lines:
1 2 | if (rlimit.rlim_cur >= max_file_limit) DBUG_RETURN(rlimit.rlim_cur); /* purecov: inspected */ |
This code tells mysqld to take the maximum value of what is specified in either the variable open_files_limit, or the soft system user limit.
I reported this behavior as documentation bug #87681.
mysqld_safe
mysqld_safe has its own open_files_limit option. This option allows you to overwrite the system soft limit any way you want. However, on:
- Red Hat Enterprise Linux 7
- Oracle Linux 7
- CentOS 7
- SUSE Linux Enterprise Server 12
- Fedora 25 and 26
- Debian 8 or higher
- Ubuntu 16.04 LTS or higher
This option as specified under the [mysqld_safe] header in the configuration file is not used when you start mysqld as a service. To explain the reason for this behavior, we need to step back into history.
init.d
For a long time, many Linux Operating Systems used init.d to start certain commands together with the OS. The Init daemon executes scripts (usually located in the directory /etc/init.d) at system startup, depending on the runlevel.
The different implementations of init.d vary, but they have known drawbacks. For example, init.d starts everything sequentially. This means a new process has to wait if another has already started. This makes the startup process on multi-core machine slow. Another drawback related to our topic is that daemons started by init.d inherit OS limits from the root user. If a program needs to be run by another user, the switch needs to happen in the startup script itself. But the order of option files that such users read can be different, depending on if they are logged in via the ssh, su or sudo commands.
MySQL Server
MySQL Server’s startup sequence for the service is as follow:
- <Perform another job>
- Start mysqld_safe as mysql user: su - mysql -s /bin/bash -c "mysqld_safe > /dev/null &"
This behavior has existed at least since version 5.5.
Percona Server for MySQL
Before version 5.7, Percona Server for MySQL had a different startup sequence:
- <Perform another job>
- Start mysqld_safe as root and pass option --user=mysql to it: "${PERCONA_PREFIX}"/bin/mysqld_safe > /dev/null 2>&1 &
With this sequence, you only need to set a hard limit for a mysql user in the file /etc/security/limits.conf, and mysqld_safe will do the rest.
In version 5.7, Percona Server for MySQL backported the startup sequence from MySQL Server. Since then, setting a hard limit on the number of open files for mysql users in /etc/security/limits.conf is not enough. You also need to have a row session required pam_limits.so in the file /etc/pam.d/common-session. This is needed because the startup sequence for mysql users changed due to the design of init.d.
SystemD
Linux developers performed several trials to find a better startup solution than init.d. Speaking for MySQL and Percona Server for MySQL startup, the most important innovation is SystemD. SystemD is becoming more and more popular. Therefore MySQL and Percona Server for MySQL do not use init.d on Red Hat Enterprise Linux 7, Oracle Linux 7, CentOS 7, SUSE Linux Enterprise Server 12, Fedora 25 and 26, Debian 8 or higher and Ubuntu 16.04 LTS or higher. Instead, they use SystemD.
What does this mean for MySQL users?
Scripts started by SystemD start as required by the system user from the start. Therefore they do not inherit limits from the root user and use their own limits specified in /etc/security/limits.conf. If you need to have your mysqld process limits differ from the defaults for user mysql, you need to set the option LimitNOFILE under the [Service] section in the service configuration file. Again, you cannot then lower this limit using open_files_limit option, unless you set it to Infinity.
Both packages
To make things more complex, Percona Server for MySQL packages for Ubuntu contain both the mysql.server script (used by init.d) and the service description for SystemD. In fact, SystemD is used after install — but you might be confused when looking at only the package files.
Conclusion
You should set the open_files_limit variable together with the operating system limits. You should study how init.d or SystemD works if you see values that you don’t expect.
How to change open_files_limit variable?
| Operating System | Startup daemon | Where to put configuration |
|---|---|---|
| Red Hat Enterprise Linux 7, Oracle Linux 7, CentOS 7 SUSE Linux Enterprise Server 12 Fedora 25 and 26 Debian 8+ Ubuntu 16.04 LTS+ | SystemD | /etc/security/limits.conf and /etc/pam.d/common-session Service configuration: sudo systemctl edit mysql [mysqld] section of the configuration file |
| Others | init.d | /etc/security/limits.conf and /etc/pam.d/common-session [mysqld_safe] section of the configuration file [mysqld] section of the configuration file |
Which values of open_files_limit variable make sense?
| Soft User Limit | open_files_limit range |
|---|---|
| Infinity | Any |
| Positive | Greater/equal than soft user limit and smaller than hard user limit |
The kernel absolute max for open files is 1048575 or 1048576, and it needs to be set explicitly per user, especially for §root§, for security.
To clarify: the setting in §/etc/security/limits.conf§.
On Linux this can be set with sysctl (fs.file-max), it’s not a hard limit. limits.conf only gets applied when PAM is invoked and pam_limits is applied, there are quite a few cases where this does not happen. I see it all the time where people set it in limits.conf and end up surprised.
Should had known that …
We never cease to learn 😉
Just to comment on another mystery.
Actually when you set
LimitNOFILE=infinity
by some reason it still limits open files to 65536
to increase this value even further I had to use
LimitNOFILE=1000000
Maybe this will shed some light: https://github.com/systemd/systemd/issues/6559
Hi,
I have a problem with xtrabackup.
I have an error:
…………
xtrabackup: open files limit requested 0, set to 1024
……………………….
I launched
ulimit -n 2048
and put in /etc/security/limits.conf
* soft nofile 2048
* hard nofile 4096
When I execute my script manually everything is OK (I can see : open files limit requested 0, set to 2048).
But when I configure the script in a cron It’s not work, I have open files limit requested 0, set to 1024 but not 2048.
Why Manually it works with 2048 but not automatically with cron (I still see 1024).
I rebooted the server but I have the same problem
Check /etc/security/limits.conf if you have an entry which overwrites this default. For example, if a user you run the cron job as is “kooky” following content of /etc/security/limits.conf will overwrite defaults:
* soft nofile 2048
* hard nofile 4096
kooky soft nofile 1024
Check also entries for groups.
Hi,
Thank you so much.
I had in /etc/security/limits.d/mysql.conf
root soft nofile 2048
root hard nofile 4096
root soft nproc 2048
root hard nproc 4096
And it works
This description shows how to change it for Ubuntu 18.04: https://stackoverflow.com/questions/30901041/can-not-increase-max-open-files-for-mysql-max-connections-in-ubuntu-15
For some reason sudo systemctl edit mysql (/etc/systemd/system/mysql.service.d/override.conf) is not honored in mysql 8.0.12
In CentOS 7 i always change the value here: /usr/lib/systemd/system/mysql.service
[Service]
LimitNOFILE=30000