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

Where in the mysqld sourcecode is this recalculation done?

Posted by Arjen Lentz on June 11, 2009 at 11:03 AM PDT #

Its in sql/ function init_common_variables. In the MySQL 5.4-Beta source the block starts at line 3316.

Posted by Vincent Carbone on June 11, 2009 at 04:21 PM PDT #

We are getting "Too many open files" for mysqld process, specifically when opening /etc/hosts.allow (we've compiled TCP wrappers support into mysql). This despite fact the fact that pfiles for the mysqld process showed only 260 open files while prctl for the same process, and mysql mysql open_files_limit, indicate that the limit is 622 (10+max_connections+table_cache_size\*2).

So I modified the ulimit statement in the mysql.server script to increase the process open files limit ("ulimit -S -n 1024"). Now prctl shows the mysqld process limit is 1024 as does mysql open_files_limit. I forced mysql to open a bunch of files by creating and querying quantity of empty tables; pfiles says there are only 430 open files - far less than the 1024 limit - and we are still getting the "Too many open files" reports for the mysqld process when opening /etc/hosts.allow.

It would appear that the reports of "Too many open files" are honoring the 256 open files process limit of the mysql account and not the limit established by mysqld at runtime.


Posted by Beth Mercer on August 10, 2009 at 06:55 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

Vince Carbone


« July 2016