MySQL open files limit: open-files-limit vs open_files_limit on Linux
Did you think that underscores and hyphens in MySQL server option names can be used interchangeably in configuration files? Read this tutorial to find out about an exception to this general rule.
We recently faced this issue where MySQL server showed different behavior while using open-files-limit vs open_files_limit in MySQL server configuration file and also the limit for this variable is not properly documented in MySQL manual.
If you look at MySQL documentation for open-files-limit, the documentation says that limit is 65535 but this is not true in case you are running your MySQL server on Linux. In this tutorial, I am going to try to explain the difference between open-files-limit and open_files_limit and also the upper limit for open files MySQL variable.
Lets start with the difference first
The general rule is: underscore '_' and hyphen '-' in option names can be used interchangeably in MySQL server configuration files. But, in case of open files limit there is a bug that can prevent you from setting higher values in case you used open_files_limit in your configuration file. This isn't entirely true actually, let me explain.
You can use open-files-limit and open_files_limit in configuration files to set an upper limit on open files but if you wish to set a limit higher than 65535 then you have to use open-files-limit instead of open_files_limit. This bug was recently fixed in newer MySQL versions. In a nutshell, both are permitted and valid syntax but if you want to set limit higher than 65535 files using open_files_limit (only works on Linux) then you have to use MySQL version >= 5.1.52.
Why is it important to set limits that high?
The open file limit becomes important for performance when MySQL server has to open several files at once. One particular case that immediately comes to mind is when you have thousands of tables in your MySQL server (in all schemas) and you are running a query that touches all of them e.g. counting no. of columns in all tables in all schemas etc or joing the COLUMNS and TABLES tables in information_schema. If your limit is set to a lower value then is required by MySQL to run the query then your query might fail causing other problems or might take too long to execute. Please refer to another similar tutorial here for more info on table_cache configuration variable.
Note: Please refer to MySQL bug 58121 for more info.
Here is a proof that MySQL can open more files than 65K even in version 5.0.67 but only if you use open-files-limit in your MySQL server's configuration file or use MySQL server version >= 5.1.52 (tested on 5.1.52) and also you must be using Linux to run MySQL server.
Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production. Use the icons below to share this tutorial with your friends.