Personal blog of Yzmir Ramirez

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

open_files_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 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

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

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:

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:

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 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:

1. <Perform another job>
2. 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:

1. <Perform another job>
2. 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?

/etc/security/limits.conf

/etc/pam.d/common-session

sudo systemctl edit mysql

[mysqld]

/etc/security/limits.conf

/etc/pam.d/common-session

[mysqld_safe]

[mysqld]

Which values of

open_files_limit

variable make sense?

open_files_limit