MySQL Table Cache Eviction and Max Connections

This article explains how MySQL manages table cache eviction when the server reaches its maximum connection limit. It details the relationship between the table_open_cache and max_connections system variables, the mechanism MySQL uses to close and evict idle table descriptors, and how to optimize these settings to prevent performance degradation and file descriptor exhaustion under heavy connection loads.

The Relationship Between Connections and the Table Cache

In MySQL, every client connection (thread) requires its own file descriptors and table cache slots to access database tables. The two primary configuration variables governing these limits are: * max_connections: Controls the maximum number of simultaneous client connections. * table_open_cache: Controls the maximum number of open tables across all threads.

When the max_connections threshold is reached, MySQL blocks new incoming connections. However, the existing active connections continue to execute queries, which heavily increases the demand on the table open cache.

If many connections simultaneously execute queries involving different tables, the table cache can quickly become saturated.

How MySQL Evicts Tables from the Cache

MySQL manages the table cache using a Least Recently Used (LRU) eviction algorithm. When a thread needs to open a table that is not currently in the cache, and the cache has reached the limit set by table_open_cache, MySQL initiates the eviction process:

1. Search for Idle Tables: MySQL searches the cache for tables that are currently unused (tables with a reference count of zero, meaning no active thread is querying them).
2. LRU Eviction: From the pool of unused tables, MySQL selects the least recently used table, closes it, releases its file descriptors back to the operating system, and evicts it from the cache.
3. Insertion: The newly requested table is opened and placed into the cache.

What Happens When No Tables Can Be Evicted?

If max_connections is reached and all concurrent threads are actively using the tables in the cache, the reference count for all cached tables is greater than zero. In this scenario:

• Cache Overflows: MySQL cannot evict any tables because they are all in active use. To allow queries to execute, MySQL will temporarily expand the cache size beyond the configured table_open_cache limit.
• File Descriptor Pressure: This temporary expansion forces MySQL to open more physical operating system files than planned.

This behavior directly impacts the open_files_limit parameter. If the combined requirement of active connections and opened tables exceeds the operating system’s file descriptor limit, MySQL will trigger the error: Too many open files (Errcode: 24).

Tuning Formula to Avoid Eviction Issues

To prevent table cache thrashing and file descriptor exhaustion when connections spike, you must balance your MySQL configuration variables. A standard rule of thumb to determine the minimum safe value for open_files_limit is:

\[\text{open\_files\_limit} = \text{table\_open\_cache} \times 2 + \text{max\_connections} + 10\]

If you expect to hit your max_connections limit frequently, you should: 1. Increase open_files_limit at both the MySQL level and the OS level (ulimit -n). 2. Monitor Table_open_cache_hits and Table_open_cache_misses via SHOW GLOBAL STATUS to ensure your cache is large enough to prevent frequent table opening and closing overhead.