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.