If you are running MySQL on a Linux server and discover that you are getting errors about there being too many open files, then the standard advice is to set the ‘open_files_limit’ in my.cnf to a higher value. However, rarely is this option explained and it may not work as you expect.
The open_files_limit option does one thing. It sets the soft limit for open files (max files) but it can never be set higher than the hard limit, which is often imposed by the default Linux kernel configuration and set at an insanely low value. Unless the hard limit has been increased, setting the soft limit with open_files_limit in mysql may have little or no effect.
When you set the open_files_limit, either on the command line or in my.cnf, it tells mysqld to set the soft limit to the specified value – if it does not exceed the hard limit. Mysqld does this by calling the setrlimit() C function, which may or may not work depending on whether that function is available in your OS libraries. That is why it is best to use the mysqld_safe shell script to start mysqld, because that script will call ‘ulimit -n’ to set the limit in the shell before starting mysqld as extra insurance if you don’t happen to have setrlimit() on your system.
So, the question is not how to set the soft limit, but how to make sure your hard limit is high enough for the soft limit to work. To see the hard limit type:
# ulimit -Hn
4096
The soft limit you can see by typing:
# ulimit -Sn
1024
Those limits are typical of an Ubuntu defaults and not very good for a busy MySQL database installation. If you have a running instance of mysqld you can see what limits it is currently running with by typing:
# cat /proc/$(pgrep mysqld$)/limits
Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 159663 159663 processes Max open files 196608 196608 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 159663 159663 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us
Note: you can also run ‘ps aux’ to get a process list and use the pid (process id) number directly rather than using pgrep.
We are taking an interest in the row labeled, ‘Max open files’. We can see both the hard limit for the process owner user as well as the soft limit it is currently running. In the example above I have already increased my open file limits to an acceptably high level for my application. How do we change the hard limit?
If you are the root user then you can very easily change the setting for the shell by using the ulimit shell command and giving it a value:
# ulimit -Hn 196608
196608
However, this will only set it for the current shell session and you will want something more permanent. The best option is to modify your limits configuration, which in a typical Ubuntu installation is in /etc/security/limits.conf. Open that file with your editor of choice and add the following lines to the bottom of the file:
* hard nofile 199680 * soft nofile 1024 # End of file
That will set the hard open file limit (nofile) to a respectable 199680 files and the default soft limit to 1024 for all users – which is what the asterisk/wildcard means. That 1024 default is fine, since mysqld_safe or mysqld will set the soft limit based on the open_files_limit option.
You can also set the fs.file-max using sysctl command:
# sysctl fs.file-max 199680
fs.file-max = 199680
Though the value might be affected by some other settings such as settings directly in config files in /etc/sysctl.d/ or in /etc/sysctl.conf itself.
Ultimately, you will know that your open file limit issue is truly resolved when you can run:
# cat /proc/$(pgrep mysqld$)/limits
… and see that your mysql process is actually running the soft limit you specified with the open_file_limit option. Just remember that you CANNOT set your open_file_limit to be higher than the hard limit configured in the operation system.