mysql: How do you determine the ideal 'open_files_limit' setting value?

Question

I am facing this issue: http://serverfault.com/questions/104618/out-of-resources-for-mysqldump

Questions

• I know I need to increase open_files_limit, but to how much?
• Can I call a command in mysql that tells me how much this should be?

Answer 1

You may find this surprising, but mysqld actually determines the correct value for you upon startup.

The MySQL Documentation says the default value for open_files_limit is 0. Yet, when you run the command SHOW VARIABLES LIKE 'open_files_limit';, it does not come back with 0. It returns what mysqld would comfortably run with.

The MySQL Documentation says the maximum value for open_files_limit is 65535. I have seen some systems with the setting above 100000 upon startup (132332 to be exact). That being the case, there are some instances where you cannot set open_files_limit any higher.

Just run the command I mentioned

SHOW VARIABLES LIKE 'open_files_limit';

If the number comes back less that 65535, you can experiment with higher values up to 65535.

Keep in mind two more things

• If you are using InnoDB with innodb_file_per_table enabled, you have to set innodb_open_files (Default is set to 300).
• Keep your eye on the error log. It will post a warning if your setting for open_files_limit is too high.