A Mystery with open_files_limit


open_files_limitIn this blog, we’ll look at a mystery around setting the


 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


 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


 can open simultaneously is defined by the configuration


 option. You would expect it to work like any other MySQL Server option: set in the configuration file, restart


and use more or fewer descriptors. All other configuration variables work this way. But


also depends on the operating system (OS) limits. This makes setting the variable more complicated.


As a user, when you start any 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


to set


  to any value that is less than the OS limit. This is not the case, however. No matter what value you set for the


 variable, the OS limit is used unless it is set to “infinity”.

sveta@Thinkie:~$ ulimit -n
sveta@Thinkie:$ cat /etc/my.cnf
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
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 



static uint set_max_open_files(uint max_file_limit)
  struct rlimit rlimit;
  uint old_cur;
  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 */
      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));

Particularly these lines:

if (rlimit.rlim_cur >= max_file_limit)
  DBUG_RETURN(rlimit.rlim_cur);    /* purecov: inspected */

This code tells


to take the maximum value of what is specified in either the variable


, or the soft system user limit.

I reported this behavior as documentation bug #87681.



has its own


 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 


header in the configuration file is not used when you start


as a service. To explain the reason for this behavior, we need to step back into history.


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


) at system startup, depending on the runlevel.

The different implementations of


vary, but they have known drawbacks. For example,


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


 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 if they are logged in via the






MySQL Server

MySQL Server’s startup sequence for the service is as follow:

  1. <Perform another job>
  2. Start




    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:

  1. <Perform another job>
  2. Start

    as root and pass option


    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 


, and


 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 


 is not enough. You also need to have a row

session required pam_limits.so

in the file


. This is needed because the startup sequence for


users changed due to the design of  




Linux developers performed several trials to find a better startup solution than


. 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


 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 


. If you need to have your


process limits differ from the defaults for user


, you need to set the option


under the 


section in the service configuration file. Again, you cannot then lower this limit using


option, unless you set it to



Both packages

To make things more complex, Percona Server for MySQL packages for Ubuntu contain both the 


script (used by


) 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.


You should set the 


variable together with the operating system limits. You should study how


 or SystemD works if you see values that you don’t expect.

How to change



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+



Service configuration:

sudo systemctl edit mysql


section of the configuration file

Others init.d




section of the configuration file


section of the configuration file


Which values of


variable make sense?

Soft User Limit


Infinity Any
Positive Greater/equal than soft user limit and smaller than hard user limit



SELinux and the MySQL init script

I recently worked with a customer who had a weird issue: when their MySQL server was started (Percona Server 5.5), if they try to run service mysql start a second time, the init script was not able to detect that an instance was already running. As a result, it tried to start a second instance with the same settings as the first one. Of course this fails and this creates a mess. What was the issue? A missing rule in SELinux. At least it looks like


If SELinux is set to enforcing and if you are using Percona Server on CentOS/RHEL 6 (other versions could be affected), service mysql start doesn’t work properly and a fix is simple to run:

# grep mysqld_safe /var/log/audit/audit.log | audit2allow -M mysqld_safe
# semodule -i mysqld_safe.pp
# service mysql restart

Other options are:

  • Set SELinux to permissive
  • Use the CentOS/RHEL standard MySQL init script (note I didn’t extensively check if that could trigger other errors)

How did we see the issue?

That was pretty easy: if an instance is already running and if you run service mysql start again, you should see something like this in the MySQL error log:

150717 08:47:58 mysqld_safe A mysqld process already exists

But if you rather see tons of error messages like:

2015-07-17 08:47:05 27065 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-07-17 08:47:05 27065 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

it means that the init script is broken somewhere.


When the issue was brought to my attention, I tried to reproduce it on my local box, but with no luck. What was so special in the configuration used by the customer?

The only thing that was slightly out of the ordinary was SELinux which was set to enforcing. Then we set SELinux to permissive, and guess what? service mysql start was now working properly and it didn’t allow 2 concurrent instances to be run!

Next step was to look at the SELinux logs to find any error related to MySQL, and we discovered messages like:

type=SYSCALL msg=audit(1437121845.464:739): arch=c000003e syscall=62 success=no exit=-13
a0=475 a1=0 a2=0 a3=7fff0e954130 items=0 ppid=1 pid=5732 auid=500 uid=0 gid=0 euid=0 suid=0
fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=5 comm="mysqld_safe" exe="/bin/bash"
subj=unconfined_u:system_r:mysqld_safe_t:s0 key=(null)

At this point, we knew that a rule was missing for mysqld_safe, we needed to add a new one.

Deeper investigation

Actually what happens is that SELinux prevents this condition to be executed in mysqld_safe:

if kill -0 $PID > /dev/null 2> /dev/null

and then the script assumes that this means the mysqld process is not running. That’s why a second mysqld is started.

However users of Oracle MySQL will probably never experience that issue, simply because the init script is a bit different: before calling mysqld_safe, the init script tries to ping a potential mysqld instance and if it gets a positive reply or an Access denied error, it concludes that mysqld is already running and it doesn’t invoke mysqld_safe.

The fix

Fortunately, this is quite simple. You can generate the corresponding rule with audit2allow:

grep mysqld_safe /var/log/audit/audit.log | audit2allow -M mysqld_safe

And after checking the corresponding .te file, we were able to load that new module:

semodule -i mysqld_safe.pp

After stopping MySQL, you can now use service mysql start normally.


This issue was quite interesting to work on because finding the culprit was not that easy. Also it only triggers when SELinux is enabled and Percona Server is used. Now should the init script of Percona Server be fixed? I’m not sure of the potential problems that could occur if we did so, but of course feel free to leave your feedback in the comments.

The post SELinux and the MySQL init script appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com