MySQL ERROR 24 (HY000): Too many open files – raising open_files_limit

ERROR 24 (HY000): Too many open files means the OS refused a file descriptor request because the mysqld process hit its hard ceiling. Clients may see Can't create/write to file with errno: 24. Tables that were accessible moments ago fail to open, and new connections may be rejected even though Threads_connected is well below max_connections.

MySQL holds file descriptors for cached tables, client connections, binary logs, relay logs, and on-disk temporary tables. When combined demand exceeds the OS-enforced limit, the server returns errno 24. Unlike connection exhaustion, which is gated by max_connections, file descriptor exhaustion is gated by open_files_limit and the OS limits that enforce it. Raising open_files_limit in my.cnf is not enough if systemd, PAM, or the shell ulimit blocks the request.

What this means

File descriptor exhaustion is a capacity boundary, not a query bug. MySQL calculates a minimum requirement from table_open_cache, max_connections, and internal files, then requests that many descriptors from the OS. The runtime value of open_files_limit reflects what the OS actually granted. If the granted ceiling is lower than workload demand, ERROR 24 is inevitable.

The table cache is usually the largest consumer. Each entry in table_open_cache typically uses one or two file descriptors per open table , and each table can have multiple entries in the cache under concurrent access. Partitioned tables multiply this because each partition counts as a separate table for cache purposes. Client connections add one descriptor each. Replication threads, binlogs, relay logs, and on-disk temporary tables add more.

The OS enforces the limit outside MySQL, so changing open_files_limit in my.cnf only works when the OS allows it. On systemd hosts, the service unit’s LimitNOFILE is the effective ceiling. On older systems, the shell limit or /etc/security/limits.conf governs the daemon. If the OS cap is lower than MySQL’s request, MySQL logs a warning at startup and continues with the lower value. The failure appears later when demand crosses that ceiling.

flowchart TD
  connections["Client connections"] -->|"1 FD each"| limit["FD ceiling: open_files_limit"]
  tables["Table cache entries"] -->|"1-2 FDs each"| limit
  logs["Binlogs / relay logs / temp files"] -->|"variable"| limit
  limit -->|"demand exceeds ceiling"| error["ERROR 24 (HY000)"]
  error -->|"check"| os["OS limit: systemd / ulimit"]
  error -->|"check"| cache["table_open_cache sizing"]

Common causes

Quick checks

# Verify the error meaning
perror 24

# Inspect the OS hard limit for the mysqld process
cat /proc/$(pgrep -x mysqld | head -n 1)/limits | grep "Max open files"

# Compare granted limit, cache config, and current usage
mysql -e "SHOW VARIABLES LIKE 'open_files_limit'; SHOW GLOBAL VARIABLES LIKE 'table_open_cache'; SHOW GLOBAL STATUS LIKE 'Open_tables'; SHOW GLOBAL STATUS LIKE 'Opened_tables';"

# Check current connection count against the maximum
mysql -e "SHOW GLOBAL VARIABLES LIKE 'max_connections'; SHOW GLOBAL STATUS LIKE 'Threads_connected';"

How to diagnose it

1. Confirm the error. Client output or the error log shows ERROR 24 (HY000): Too many open files. Run perror 24 to verify it maps to OS errno 24.
2. Check the granted ceiling. SHOW VARIABLES LIKE 'open_files_limit'; displays the limit MySQL is actually operating under. If this is surprisingly low, the OS restricted it at startup.
3. Check table cache sizing. SHOW GLOBAL VARIABLES LIKE 'table_open_cache'; shows the configured cache. Each entry typically uses one or two file descriptors . Multiply by two for a conservative FD estimate.
4. Check current table cache pressure. SHOW GLOBAL STATUS LIKE 'Open_tables'; and SHOW GLOBAL STATUS LIKE 'Opened_tables';. If Open_tables is near table_open_cache and Opened_tables is climbing, the cache is churning because it cannot grow.
5. Inspect the OS enforcement. Read /proc/<mysqld_pid>/limits to see the Max open files hard limit. If the hard limit is lower than open_files_limit, the OS wins and MySQL silently accepted the lower value.
6. Check connection volume. SHOW GLOBAL STATUS LIKE 'Threads_connected'; against max_connections. Each connection consumes a descriptor.
7. Look for multipliers. Partitioned tables consume one cache entry per partition. High concurrency against the same table can create multiple cache entries. If you recently added partitions or increased concurrency, demand may have doubled.
8. Verify the service manager. On systemd hosts, check systemctl show mysql --property=LimitNOFILE. On hosts using SysV init, check the init script for ulimit -n. Do not rely on /etc/security/limits.conf for systemd services, because PAM limits often do not apply to services started by systemd.

Metrics and signals to monitor

Fixes

Immediate relief without restart

If ERROR 24 is active and you cannot restart MySQL, free descriptors by terminating idle or long-running connections. Use SHOW PROCESSLIST to find connections in Sleep state with high Time values, or connections holding many tables. Kill them with KILL <thread_id>. This is temporary and disruptive: it aborts queries and may break application transactions. The ceiling is still too low.

Raise the OS file descriptor ceiling

This is the only permanent fix that allows MySQL to use a larger open_files_limit. On systemd-managed hosts, create a service drop-in at /etc/systemd/system/mysql.service.d/override.conf:

[Service]
LimitNOFILE=100000

Then run systemctl daemon-reload and restart MySQL. Set an explicit numeric value. Do not use infinity, which may resolve to an unexpectedly low hard ceiling depending on your systemd version.

On systems without systemd, update the init script or the mysql user’s limits in /etc/security/limits.conf, then restart the service. Always verify the new limit in /proc/<pid>/limits after startup.

Tradeoff: Higher limits consume kernel memory for file descriptor tables, but the cost is negligible for modern servers.

Right-size table_open_cache

If the cache is genuinely too small for your schema, raise both table_open_cache and the OS FD limit together. Each table can have multiple entries in the cache under concurrency. If you raise table_open_cache significantly, also raise table_open_cache_instances to reduce mutex contention.

If the cache is unnecessarily large and you need to fit within a constrained open_files_limit, lower table_open_cache and restart. This increases Opened_tables churn and adds latency, but it prevents ERROR 24.

Tradeoff: A larger cache uses more memory and file descriptors. Size it to your actual table count times peak concurrent access, not an arbitrary round number.

Address partitioned tables

If you use many partitions, remember each partition counts as a separate table for cache purposes. Either consolidate partitions or size the cache and FD limits for the total partition count.

Prevention

• Size open_files_limit to cover at least table_open_cache multiplied by 2 (for the one to two FDs per entry) plus max_connections, plus headroom for binlogs, relay logs, and temp files.
• Monitor Open_tables / table_open_cache continuously. A ratio trending above 0.8 is a leading indicator.
• After changing table_open_cache, verify the new demand fits inside /proc/<pid>/limits.
• Increase table_open_cache_instances when you raise the cache to avoid internal mutex contention.
• Review partition counts during schema design. Hundreds of partitions per table multiply cache demand quickly.

How Netdata helps

• Netdata collects Open_tables, Opened_tables, and Threads_connected from SHOW GLOBAL STATUS and surfaces them with MySQL configuration variables. Use these to spot cache pressure climbing toward the open_files_limit ceiling.
• Correlate rising Opened_tables rates with OS-level process limits to distinguish a MySQL sizing problem from a systemd or PAM enforcement issue.
• Alert on Open_tables approaching table_open_cache to get a leading indicator before ERROR 24 appears in logs.

Related guides

• How MySQL actually works in production: a mental model for operators
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis
• MySQL adaptive hash index latch contention: high CPU, low throughput
• MySQL InnoDB buffer pool hit ratio collapse: the cliff edge
• MySQL slow after restart: buffer pool warm-up and the cold cache
• MySQL innodb_buffer_pool_size tuning: 60-80% of RAM and when that breaks
• MySQL Innodb_buffer_pool_wait_free > 0: buffer pool memory pressure
• MySQL InnoDB checkpoint age: the redo log capacity signal nobody watches
• MySQL connection exhaustion: detection, diagnosis, and prevention
• MySQL innodb_deadlock_detect=OFF: when deadlock detection becomes the bottleneck
• MySQL ERROR 1213: Deadlock found when trying to get lock; try restarting transaction
• MySQL Got error 28 from storage engine / No space left on device – recovery