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

I am facing this issue:
https://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?

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.

StackExchange Database Administrators Q#18036, answer score: 8