Learn with XTIVIA how to address the common MySQL Error, “Too many open files.”

If you encounter the error where MySQL reports “Can’t open file: (errno: 24)”, analyze a few variables. The error usually occurs when the maximum number of files that may be open simultaneously are reached. Limits are imposed by both MySQL and the operating system because tables in MySQL can be opened by more than one user at the same time.

The maximum number of open files is limited in a few different ways. Start by checking the read-only variable open_files_limit:

Show global variables like ‘open_files_limit’;

This is the total number of file descriptors available. Contrast this value to the status variable opened_files:

Show global status like ‘opened_files’;

If opened_files is very large given the uptime of the instance, the limit may need to be increased. The value must be defined in the configuration file for MySQL by defining the value under the [mysqld] section.

open_files_limit = (desired numerical value here)

The operating system may be imposing its own limit on the number of available file descriptors for MySQL. For Unix environments, check the value of ulimit.

The variables table_open_cache and max_connections should also be analyzed if errors are being reported for too many open files. If table_open_cache is too high, it may be allowing each thread to open more tables than the maximum. Max_connections is also related because if there is a large number of simultaneous connections, each connection may be opening files. Adjusting these two variables to lower values may be an option, but each environment differs and testing should be carried out.

Summary: It is not uncommon to see “Too many open files” and “Can’t open file: (errno: 24)” in the MySQL error log. Resolving this error typically involves adjusting the open_files_limit variable and consulting the limit imposed by the operating system.

Share This