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 interesting1.
    • Before setting with setrlimit2, 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 again3 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 unexpectedly4 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 log5).

  • 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


  1. Assuming getrlimit (and RLIMIT_NOFILE) is available, which should
    be on any Linux. If not, it uses min(max_file_limit, OS_FILE_LIMIT) as a fallback. 
  2. http://linux.die.net/man/2/setrlimit 
  3. This looks redundant to me. 
  4. With “EAGAIN: Resource temporarily unavailable”. 
  5. MySQL is notorious in this aspect, in case of SSL for instance