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
process.max-file-descriptor
basic 256 - deny 20773
privileged 65.5K - deny -
system 2.15G max deny -
$ ulimit -S -n ### soft limit 256 $ ulimit -H -n ### hard limit 65535