MySQL Opened_tables climbing: table_open_cache and open-files pressure

Steadily climbing Opened_tables with Open_tables pinned near table_open_cache means the instance is churning table file descriptors instead of reusing them. Each cache miss opens a table, consumes a file descriptor, and adds metadata lock overhead. The first symptom is usually sporadic query latency during peak traffic. Left unchecked, the instance exhausts its file descriptor limit and returns errors like Too many open files or refuses connections.

This pattern is common on schemas with many tables, heavy use of partitioned tables, or connection pools that hold many concurrent sessions against distinct table sets. It is easy to mistake for a query regression because the latency is distributed across many queries rather than concentrated in the slow log.

What this means

Opened_tables is a cumulative counter that increments every time MySQL opens a table. In a healthy system with a warm cache, this counter stays nearly flat because MySQL reuses cached file handles. Open_tables is the instantaneous count of currently cached entries. When Open_tables / table_open_cache exceeds 0.95, the cache is effectively full. New table accesses evict the least recently used entry and open a new file, producing a sustained positive Opened_tables rate relative to the Questions rate.

Each cached table entry holds a file descriptor. If table_open_cache * 2 approaches open_files_limit, the server risks descriptor exhaustion. Partitioned tables multiply the problem because each partition counts as a separate table for the cache. table_definition_cache stores metadata but does not relieve pressure on the table open cache.

flowchart TD
    A[Query accesses table] --> B{Cache hit?}
    B -->|Yes| C[Reuse open file descriptor]
    B -->|No| D[Open table file]
    D --> E[Consume file descriptor]
    E --> F[Insert into table open cache]
    F --> G{Cache full?}
    G -->|Yes| H[Evict LRU entry]
    H --> I[Release file descriptor]
    I --> F
    G -->|No| F
    E --> J{FD limit near?}
    J -->|Yes| K[Errors: Too many open files]

Common causes

Quick checks

-- Check cache occupancy and churn
SHOW GLOBAL STATUS WHERE Variable_name IN ('Open_tables', 'Opened_tables', 'Questions');

-- Check cache and FD configuration
SHOW GLOBAL VARIABLES WHERE Variable_name IN ('table_open_cache', 'table_open_cache_instances', 'open_files_limit');

-- Compute current occupancy ratio
SELECT 
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Open_tables') AS open_tables,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='table_open_cache') AS table_open_cache;

# Check OS file descriptor limit for the mysqld process.
# If multiple mysqld processes exist, target the correct PID manually.
cat /proc/$(pgrep -x mysqld)/limits | grep "Max open files"

# Count current file descriptors opened by mysqld
ls /proc/$(pgrep -x mysqld)/fd | wc -l

-- Identify heavily partitioned tables.
-- Warning: querying information_schema.PARTITIONS can stall on servers with thousands of tables.
SELECT TABLE_NAME, COUNT(*) AS partition_count
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_NAME
HAVING partition_count > 1
ORDER BY partition_count DESC LIMIT 10;

-- Verify table_definition_cache is not conflated with table_open_cache
SHOW GLOBAL VARIABLES LIKE 'table_definition_cache';

How to diagnose it

1. Confirm cache saturation. Compute Open_tables / table_open_cache. If the ratio stays above 0.95 for more than five minutes, the cache is full and evictions are constant.

2. Quantify churn rate. Sample Opened_tables twice over a five-minute window. A warm cache should show a near-zero open rate relative to the Questions rate. If the rate is sustained and positive while occupancy is high, you have active churn.

3. Check for restart warmup. If Uptime is below 600 seconds, a spike in Opened_tables is expected as the cache warms. Do not tune based on post-restart behavior alone.

4. Identify partition inflation. Run the partition query from the quick checks. If your workload accesses partitioned tables with tens or hundreds of partitions, multiply the apparent table count by partition count when sizing the cache.

5. Inspect file descriptor headroom. Compare the current FD count from /proc/<pid>/fd to open_files_limit. If table_open_cache * 2 approaches the limit, the next traffic spike can trigger FD exhaustion. Also verify that open_files_limit is explicitly set in my.cnf; otherwise MySQL may silently lower it to match the OS soft limit at startup.

6. Correlate with latency. Check Threads_running and query latency percentiles. If both rise when Opened_tables climbs, cache churn is adding queuing delay and metadata lock overhead.

Metrics and signals to monitor

Fixes

Increase table_open_cache and FD limits

Raise table_open_cache dynamically with SET GLOBAL to accommodate the number of tables accessed concurrently, including partitions. Each entry consumes memory and one file descriptor, so increase open_files_limit commensurately. The limit must cover the cache, client connections, binlogs, redo logs, and administrative files.

open_files_limit is not dynamic. If you change the OS file descriptor limit, set open_files_limit explicitly in my.cnf and restart MySQL. Without an explicit setting, MySQL may silently adopt the lower OS soft limit at startup.

Reduce partition pressure

If partitioned tables are the primary driver, treat each partition as a distinct table in your capacity math. Consider pruning old partitions or revisiting the partitioning strategy if the partition count forces an impractically large cache.

Lower concurrent table access

Reduce the number of distinct tables touched simultaneously. Consolidate queries, eliminate unnecessary joins across many tables, or shard the workload so that a single instance does not need all tables open at once.

Increase table_open_cache_instances

If the cache is large and you see mutex contention (high Threads_running with low CPU), increase table_open_cache_instances to partition the cache across multiple segments. The tradeoff is higher memory usage and more complex eviction behavior.

Prevention

• Size table_open_cache for your peak concurrent table count, including a multiplier for partitions.
• Monitor Open_tables / table_open_cache as a leading indicator, not just Opened_tables.
• Maintain open_files_limit at least double table_open_cache plus max_connections plus headroom for binlogs, redo logs, and other background files.
• Review schema designs that create high partition counts or thousands of small tables.
• Avoid creating and dropping temporary tables in hot loops.

How Netdata helps

• Collects Open_tables, Opened_tables, and table_open_cache automatically and charts occupancy ratios over time.
• Correlates table cache pressure with Questions rate and Threads_running to distinguish cache churn from genuine query overload.
• Surfaces open_files_limit alongside OS file descriptor metrics so you can see the pressure chain from cache saturation to FD exhaustion.
• Enables alerts on Open_tables / table_open_cache > 0.95 without custom SQL polling.

Related guides

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