MySQL file limit, table cache and max_connections

Reading Time: 4 minutes

MySQL variables open_files_limit, table_open_cache and max_connections are
inter-related, and this is for obvious reasons: all deal with file descriptors
one way or another.

If one of the value is provided but others are left out, mysqld calculates
others using a formula and in some cases, emits a warning if not possible.

The whole calculation behind obtaining the final file descriptor limit is a bit
byzantine and is as follows (for Linux):

EDIT: This applies to MySQL 5.5, in 5.6, as Daniël in comments pointed out,
few things have changed, check comment for details. I will probably make a
followup post on the differences.

1. First it tries to calculate a minimum requirement based on max_connections
   and table_open_cache. As for other variables, if it is not provided in cnf or
   on command line it uses defaults.

   <br />    /* MyISAM requires two file handles per table. */
       wanted_files= 10+max_connections+table_cache_size*2;
   

2. Next, it checks how much it checks how much it can actually allocate based on
   open_files_limit.
   • Here, it tries to allocate based on the assumption that atmost 5 file
     handles will be used by a connection and then obtains the maxima from the
     previous wanted_files and open_files_limit (Note, here it is considered
     to be 0 if not set).

   <br />    max_open_files= max(max(wanted_files, max_connections*5),open_files_limit);
       files= my_set_max_open_files(max_open_files);
   

3. After this, it obtains minimum of max_open_files and maximum file limit
   supported by that platform. For Linux, it is UINT_MAX.

   limit = min(max_file_limit, OS_FILE_LIMIT);
   

4. This is where it gets interesting¹.
   • Before setting with setrlimit², it obtains the current limit and if that
     limit is unlimited (in ulimit paralance), it tries to set it lower to limit
     obtained earlier (good guy mysql!).

   • If the current limit is not unlimited and is higher than limit, it
     returns.

   • If both the above conditions are not satisified, it actually sets it with
     setrlimit.

     • If this fails, it returns the value obtained with earlier getrlimit.

     • If this succeeds, it does a getrlimit again³ and returns the limit
       it returns (lets call it plimit).

5. Now if the limit it received is lower than what it wanted plimit <
wanted_files:

   • If open_files_limit is not set, then it readjusts max_connections and
     table_cache_size appropriately based on the plimit obtained before and
     prints a warning (based on log_warnings).

   The math in the formula above is left as an exercise to reader .
   TABLE_OPEN_CACHE_MIN is 400 .

   • If open_files_limit limit is set, then it prints the familiar warning
     “Could not increase number of max_open_files to more”.
6. Finally, it sets the variable open_files_limit to plimit. This is why
   you may often see open_files_limit different at runtime than what you set in
   my.cnf.

Now, what are the implications of this?

• MySQL can only change the limit upto soft limit imposed by OS. If it hits the
  hard limit, it falls back as in above (since it is not root, it cannot raise
  that by itself). On a capability based system, MySQL should be able to change
  the hard limit if it has CAP_SYS_RESOURCE. You can check both hard and soft
  limits with ulimit -H and ulimit -S for current user. You can set them with
  /etc/security for daemons.

• If you set – (max_connections or table_open_cache or both) AND
  open_files_limit, and MySQL is not able to obtain the desired limit due to OS
  restrictions, your connections can fail unexpectedly⁴ later on if and
  when it runs out of file descriptors (at max_connections or large number of
  tables).

  • Hence, one good thing to do here is to not set open_files_limit. Just look
    into global limits and MySQL is wise enough to figure out how much it needs
    (for instance, it sets lower if getrlimit returns unlimited). For global
    limits, set a sane upper limit and set max_connections/table_cache variables
    appropriately based on application needs.

  • Only downside with previous approach is that max_connections and
    table_cache will be adjusted accordingly (and is visible with log_warnings)
    if higher than OS limits but it still better than failing later on!

• You can set open_files_limit upto UINT_MAX (assuming OS allows it)! MySQL
  documentation mentions “platform dependent” but for a 64 bit system, you can
  set upto 4 million!

• This should be obvious but if you just set open_files_limit but not
  max_connections or table_open_cache, mysql won’t ramp these up to match
  open_files_limit (if the limit is higher than what is obtained with latter
  variables).

• You still may want to set open_files_limit in some cases. Xtrabackup, for
  instance, uses this variable to set its limits (it has to open all files at
  start due to DDL restrictions) and it doesn’t base this on max_connections
  or table_cache (since it doesn’t make sense to xtrabackup). Refer to Bug
  #1183793 for
  details.

• You may also want to set it if mysql is running in a shared environment with
  other services like Apache etc. and you want to limit for some reason. In this
  case, pay attention to warning in the error log! (may be there should be an
  option where it fails than just log to error log⁵).

• max_connections and table_open_cache are a dynamic variable, open_files_limt
  is not. If you change max_connections at runtime, no one of the above is done.
  Don’t set them too high at runtime if your open_files_limit is low (check the
  variable at runtime).

• Based on previous point, you may also want to set open_files_limit if you want
  to keep a buffer between what mysql feels it wants and what you feel may be
  needed later on (especially if you are going to change at runtime).

• In case of MariaDB and Percona Server, due to thread pool requirements (when
  used), an additional parameter extra_max_connections is also used.

Photo Credit