$ guides / mysql / mysql-table-open-cache-pressure ▌

Operations Guides

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

Cause	What it looks like	First thing to check
`table_open_cache` too small for schema size	`Open_tables / table_open_cache` > 0.95 sustained; `Opened_tables` climbs steadily	`SHOW GLOBAL VARIABLES LIKE 'table_open_cache';` and compare against the number of tables accessed concurrently
Partitioned tables accessed by many sessions	Cache fills even though the table count appears low; latency spikes during partition pruning	`SHOW CREATE TABLE` on hot tables to count partitions
High concurrency on many distinct tables	Many sessions each hold open handles for different tables; total entries exceed cache size	`SHOW GLOBAL STATUS LIKE 'Threads_connected';` and compare with `Open_tables`
Frequent temporary table creation	`Opened_tables` rises alongside `Created_tmp_tables` as temp tables are opened and closed	`SHOW GLOBAL STATUS LIKE 'Created_tmp%';` and the temp table creation rate
File descriptor limit too low	`open_files_limit` prevents `Open_tables` from reaching `table_open_cache`; FD exhaustion appears first	OS file descriptor limit versus MySQL `open_files_limit`

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

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.
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.
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.
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.
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.
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

Signal	Why it matters	Warning sign
`Open_tables / table_open_cache`	Direct occupancy of the table open cache	Ratio > 0.95 sustained for > 5 minutes
`Opened_tables` rate	Churn rate; high values mean repeated opens instead of reuse	Sustained positive rate while occupancy is high
`open_files_limit` vs actual FD usage	Hard ceiling for descriptors; every miss consumes one	`table_open_cache` x 2 approaching `open_files_limit`
`Threads_connected`	Each connection can hold separate table entries	Sudden spike correlating with `Open_tables` growth
`Created_tmp_tables` rate	Temporary tables also open and consume cache entries	Sustained increase alongside `Opened_tables`

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.

The Netdata solution

MySQL monitoring with Netdata

Netdata monitors MySQL and MariaDB with per-second metrics and ML anomaly detection. Track connection usage, query throughput, slow queries, redo-log pressure, and replication lag alongside the host and storage signals that explain them.

See MySQL monitoring → Start monitoring free