Changing process.max-file-descriptor using 'ulimit -n' can cause MySQL to change table_open_cache value

Before I get into details here is the bottom line. If you start MySQL on Solaris as a non-root (ie, mysql) user and for some reason you need to adjust the file descriptor resource limit for the parent shell, never use 'ulimit -n'. This will set both the soft and hard limit and may cause MySQL to adjust the max_connections and table_open_cache configuration variables upon next startup.

Use either:

 ulimit -S -n 1024

or something like:

  prctl -n process.max-file-descriptor -t basic -v  1024 -r -i process $$

The Details

The default 'basic' privilege value for the resource control process.max-file-descriptor is 256. This control represents the soft ulimit for file descriptors per process. The default 'privileged' privilege is set to 65535, which represents the hard ulimit. A non-root user can adjust the soft limit down or up to the hard limit. Unless it has PRIV_SYS_RESOURCE a non-root user can only decrease the hard limit.

Not being aware of the default values I started a sysbench run with 256 threads. It failed with the following message:

FATAL: unable to connect to MySQL server, aborting...
FATAL: error 2001: Can't create UNIX socket (24)
FATAL: failed to connect to database server!
FATAL: thread#252: failed to connect to database server, aborting...

Sysbench fails because the action for the basic privilege process.max-file-descriptors is deny. I was running sysbench logged in as mysql. I made the mistake of increasing the ulimit to what I thought was a reasonable 1024 via 'ulimit -n 1024'. When restarting mysqld it inherited a soft and hard limit of 1024. This had the effect of causing mysqld to recalculate the values for max_connections and table_open_cache as displayed in the error log:

090610  9:44:11 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64

This value is much too small for table_open_cache and is guaranteed to cause heavy contention on LOCK_open as concurrent threads increase.

Why did MySQL change table_open_cache and max_connections?

Upon startup mysqld will calculate the number of open files it wants selecting the greatest of:

  • myisam's requirement for 2 file handles per connection
    • 10+max_connections+table_cache_size*2
  • max_connnections*5
  • open_files_limit

For the purpose of this discussion let's call this max_open_files.

It then compares max_open_files with the soft resource limit for file descriptors per process. If the soft limit is less than max_open_files both the soft and hard limit will be set to max_open_files via setrlimit. If setrlimit fails the requested max_open_files is modified to the old soft limit. If the returned max_open_files is less than myisam's requirement for 2 file handles per connection and open_files_limit has not been specified, max_connections and table_open_cache will be recalculated to fit within the new boundaries.

Now take the case where I inadvertently set both the soft and hard limit via 'ulimit -n 1024'. I had set  open_table_cache=4096 and max_connections=2049, arbitrary large values so I could set them once and forget about them. In this case max_open_files=max_connections*5=10245. When trying to increase the limits to 10245, the setrlimit call fails because I started mysqld as the mysql user and it does not have the privilege to increase the hard limit from 1024 to 10245. In this case max_open_files is set to 1024, which causes mysqld to recalculate max_connections and table_open_cache:

090610  9:44:11 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64

How to observe process.max-file-descriptor

You can use either prctl or ulimit to observe the current values of process.max-file-descriptor:

$ prctl -n process.max-file-descriptor  -i process $$
process: 20773: -ksh
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
        basic             256       -   deny                             20773
        privileged      65.5K       -   deny                                 -
        system          2.15G     max   deny                                 -
$ ulimit -S -n ### soft limit
$ ulimit -H -n ### hard limit