AMAP

MySQL 8 Table Cache Manager

​ ​ ​ Compiled by: Rajorshi Sen ,On: Feb 8, 2026
Architectural Evolution and Technical Implementation of the MySQL 8.0 Table Cache Manager
Structural Framework of the Table Cache Subsystem
The Coordinator: Table_cache_manager
The Partition Layer: Table_cache Instances
The Blueprint: TABLE_SHARE
The Handle: TABLE Object
Implementation Details: Source Code Analysis of Data Structures
Intrusive Linked Lists (I_P_List)
The Table_cache_element and Hash Mapping
MEM_ROOT and Memory Efficiency
Functionality and Lifecycle of a Table Handle
Step 1: Initialization and the Role of table_open_cache_instances
Step 2: Requesting a Handle (The Open Operation)
Step 3: Use and Metadata Locking
Step 4: Release and LRU Management
Step 5: Eviction and Closing
Integration with the Transactional Data Dictionary
The Death [Link] Files
Atomic DDL and Cache Invalidation
Performance Impact and Information Schema Optimization
Information Schema as Views
Dynamic Statistics and Caching
Configuration Variables and System Tuning
table_open_cache
table_definition_cache
table_open_cache_instances
Percona-Specific Enhancements: table_open_cache_triggers
Mathematical Analysis of Cache Performance
Source Code Verification: Lifecycle and State Transitions
Verification of table_cache_manager::init
Verification of Table_cache::release_table
Verification of Table_cache_element::free_tables
Analysis of Table_cache_manager Instance Variables
1. The Partition Array (Table_cache[])
2. The Global Registry for TABLE_SHARE
3. Intrusive List Pointers (Embedded Variables)
4. Configuration State Variables
Summary Table: Manager Responsibilities
Explanation of Embedded Variable - Intrusive List Pointers
The Comparison
1. The Standard Way (Non-Intrusive)
2. The MySQL Way (Embedded / Intrusive)
Why this is critical for the Table Cache Manager
Real-World Example from the Manager
Table Cache Manager Class Definition
Declaration Hierarchy
Key Takeaways from the Declaration:
LRU Logic Implementation for Table Cache
Why this logic is superior for MySQL:
Architectural Linkage: Thread Context and the Table Cache Handle Pool
1. The Linkage: THD to TABLE
2. The Table_cache and the Intrusive List
3. The Lifecycle: Checking Out a Handle
4. Why this matters for Memory and Concurrency
Summary of the "Chain"
MySQL Table Management and Memory Allocation: A Thread-Based Workflow Analysis.
1. The Template Relationship
2. The Manufacturing Process
3. Why This Separation Exists
4. Directing Your Investigation
1. The TABLE Handle as the Logical Entry Point
2. The innodb_handler Object as the Functional Bridge
3. Relation to InnoDB Storage Details
Summary of the Relationship
MySQL Table Management and Memory Allocation: A Thread-Based Workflow Analysis
The Linking Mechanism: The "Handler" Object
Workflow of the Linkage
Summary of the Workflow Linkage
Why this is relevant to a 16GB spike in 37GB buffer pool & 50GB RAM host
MySQL Table Management and Memory Allocation: A Thread-Based Workflow Analysis
1. The TABLE Handle as the Logical Entry Point
2. The innodb_handler Object as the Functional Bridge
3. Relation to InnoDB Storage Details
Summary of the Relationship
Conclusion: The Modernized Metadata Engine
Works cited
Quick Summary​
The document, "MySQL Table Cache Manager Deep Dive ," provides a detailed technical analysis of the architectural
overhaul of the metadata management subsystem in MySQL 8.0, focusing on the Table Cache Manager and its
integration with the Transactional Data Dictionary.

The core theme is the elimination of the legacy performance bottleneck caused by the global LOCK_open mutex through a
sophisticated, multi-tiered architecture based on partitioning and specialized data [Link] Architectural Components
 ●​ The Coordinator: Table_cache_manager: A global singleton that manages an array of independent
Table_cache instances. It uses a hashing algorithm (based on table name/database) to route requests to a
specific partition, minimizing lock contention.
●​ The Partition Layer: Table_cache Instances: Self-contained units that each manage a subset of open table
handles. They utilize an Instance Mutex (m_lock) to protect their internal structures, distributing the load across
multiple CPU cores. The number of partitions is set by the table_open_cache_instances system variable.
●​ The Blueprint: TABLE_SHARE: A unique, shared object containing the static metadata (blueprint) of a table, such
as column and index definitions. It is stored in a global registry managed by table_definition_cache and is
the object that prevents DDL operations while a table is in use. It uses its own MEM_ROOT arena-based allocator for
memory efficiency.
●​ The Handle: TABLE Object: A non-shared, session-specific handle used by a thread for data operations. Multiple
TABLE objects for the same table all point back to a single TABLE_SHARE.

Data Structures and Performance

The manager achieves high performance by avoiding generic containers and utilizing Intrusive Pointer Lists
(I_P_List).

●​ I_P_List is used for lists like m_unused_tables (Global LRU list of unused handles in a partition),
used_tables, and free_tables.
●​ This design allows moving a TABLE object between lists (e.g., from free_tables to used_tables) by simply
updating four pointers, achieving O(1) performance for checking out and returning handles without memory
allocation/deallocation overhead.

Table Handle Lifecycle (The Open Operation)

1.​ Request: A query requests a handle (e.g., SELECT * FROM employees).
2.​ Dispatch: The request is hashed to one of the Table_cache partitions.
3.​ Checkout: The partition's m_lock is acquired. A handle is taken from the element's free_tables list. If none
exists, a new handle is created and linked to the TABLE_SHARE.
4.​ Activation: The handle is added to the element's used_tables list.
5.​ Release (LRU Management): Upon query completion, the handle is returned to free_tables and appended to
the tail of the partition’s m_unused_tables list (Most Recently Used position).
6.​ Eviction: If the partition exceeds its quota (based on table_open_cache), the Least Recently Used (LRU)
handle from the head of m_unused_tables is removed, closed, and deleted.

Integration with the Data Dictionary

●​ Transactional Data Dictionary: MySQL 8.0 eliminates file-based metadata (.frm files) in favor of an
InnoDB-backed, transactional Data Dictionary.
●​ Atomic DDL: DDL operations are now atomic, and the Table Cache Manager is notified on commit to invalidate
and close associated TABLE_SHARE handles across all partitions, ensuring data consistency.
●​ Information Schema Optimization: INFORMATION_SCHEMA tables are implemented as views over the Data
Dictionary, which allows the optimizer to use indexes for lookup, leading to up to 30x performance improvements
for static metadata queries compared to legacy filesystem scans.
Architectural Evolution and Technical
Implementation of the MySQL 8.0 Table Cache
Manager
The architectural paradigm of MySQL 8.0 represents a watershed moment in the history of open-source relational
database management systems. Central to this transformation is the complete overhaul of the metadata management
subsystem, facilitated by the introduction of the Transactional Data Dictionary and the modernization of the Table
Cache Manager.1 In previous iterations, particularly those prior to the 5.6 and 5.7 series, MySQL suffered from a
notorious performance bottleneck centered around a global mutex known as LOCK_open. This single lock governed
almost all operations related to table opening, closing, and metadata access, effectively serializing these operations and
causing massive contention on high-core-count servers.3 The MySQL 8.0 Table Cache Manager was engineered to
dismantle this bottleneck through a sophisticated partitioning strategy, custom high-performance data structures, and a
deep integration with the InnoDB-backed Data Dictionary.3

Structural Framework of the Table Cache Subsystem

The Table Cache Manager does not operate as a simple storage container but rather as a multi-tiered coordination
framework designed to optimize the lifecycle of table objects. To understand its functionality, one must first distinguish
between the various layers of the hierarchy: the global manager, the partitioned cache instances, the shared metadata
templates, and the individual session handles.3 This hierarchical approach ensures that the database can scale to
handle thousands of concurrent connections and millions of tables without suffering from the linear performance
degradation characteristic of legacy architectures.5

The Coordinator: Table_cache_manager

At the apex of the hierarchy sits the Table_cache_manager class. Defined within the sql/table_cache.cc and
sql/table_cache.h files, this class acts as a global singleton that coordinates the entire subsystem.6 Its primary role is to
manage an array of Table_cache instances. During server initialization, the manager reads the
table_open_cache_instances system variable to determine the number of partitions to create.3 Each partition is an
independent Table_cache object with its own internal locking mechanism, allowing the server to distribute the load of
table management across multiple CPU cores.7

The manager serves as a dispatcher. When a thread requires a table, it does not lock the entire manager; instead, the
manager uses a hashing algorithm—typically based on the table’s name and database—to identify which specific
Table_cache instance should handle the request.3 This minimizes the radius of lock contention, as threads accessing
different tables are likely to be routed to different cache partitions.

The Partition Layer: Table_cache Instances

Each Table_cache instance is a self-contained unit responsible for a subset of the system's open tables. This
partitioning is the fundamental mechanism for reducing contention.3 Within a single Table_cache object, several key
members manage the state of the tables:

Technical
Component Identification Functional Purpose
Instance Mutex m_lock Protects all internal structures of the specific partition instance.
Unused Table An intrusive list of TABLE objects currently not in use by any thread,
List m_unused_tables managed via LRU.
Cache Element A hash map (std::unordered_map) mapping table names to
Hash m_cache Table_cache_element objects.
A running counter of all TABLE objects (used and unused) in this specific
Table Count m_table_count partition.

The partition layer acts as the "checkout counter" for the server's threads. When a thread needs a table handle, it
interacts with the assigned Table_cache instance, acquires its m_lock, and retrieves an available handle from the

m_unused_tables list.7 This design ensures that the locking cost of checking out a table handle is isolated to of

the total table cache activity, where is the value of table_open_cache_instances.3

The Blueprint: TABLE_SHARE

While the Table_cache manages instances of open tables, the TABLE_SHARE structure represents the static metadata
of the table itself.3 A TABLE_SHARE is a unique, shared object for every physical table in the database. It contains the
"blueprint" of the table, including:
●​ Column definitions and data types.
●​ Index definitions and key information.
●​ Foreign key constraints and check constraints.
●​ The table's storage engine handler definition. 9

The TABLE_SHARE is owned by the Table_cache_manager and is stored in a separate global registry governed by the
table_definition_cache configuration variable.3 Unlike the TABLE handle, which is thread-local once checked out, the
TABLE_SHARE is accessed by all threads that have an open instance of that table. Crucially, the TABLE_SHARE contains
its own mem_root, a private memory allocator used to store metadata that must persist for the entire lifespan of the
share.9 This lifecycle management is critical because it allows the server to evict table definitions from memory
independently of the table handles.3

The Handle: TABLE Object

The TABLE object is the actual handle used by a session to perform data operations.3 It is a non-shared object, meaning
that if ten threads are simultaneously querying the orders table, there will be ten distinct TABLE objects, all pointing
back to a single TABLE_SHARE.9 Each TABLE handle tracks session-specific information, such as the current record
being read, the state of the storage engine's cursor, and query-specific flags.9
When a thread finishes its operation, it does not destroy the TABLE handle. Instead, it returns the handle to its
originating Table_cache partition.7 The handle is then placed in the m_unused_tables list, where it can be quickly reused
by the next thread that needs access to the same table, avoiding the significant overhead of re-opening files and
re-initializing the storage engine interface.7

Implementation Details: Source Code Analysis of Data Structures

The high performance of the MySQL Table Cache Manager is largely attributed to its avoidance of generic containers in
favor of specialized, intrusive data structures. Source code digging into the MySQL 8.0 repository reveals a

sophisticated use of templates and memory management techniques aimed at achieving complexity for common
7
operations.

Intrusive Linked Lists (I_P_List)

A defining characteristic of the MySQL source code is the use of the I_P_List template (Intrusive Pointer List). Standard
linked list containers, such as std::list, typically allocate a "node" that contains a pointer to the data and pointers to the
next and previous nodes. This results in fragmented memory and extra allocation overhead. In contrast, an intrusive list
requires the managed object to provide the pointers themselves.7

In sql/table.h, the TABLE and TABLE_SHARE structures define members such as next and prev (or cache_next and
cache_prev) that are used specifically by the I_P_List template.7

List Type Object Managed Usage in Table Cache

m_unused_tables TABLE Global LRU list for all unused table handles in a partition.
List of handles currently assigned to active sessions for a specific
used_tables TABLE table.
free_tables TABLE List of handles for a specific table available for immediate reuse.
share_list TABLE_SHARE List used for global tracking of all cached table definitions.

The technical advantage of this implementation is that a TABLE object can be moved from the free_tables list to the
used_tables list simply by updating four pointers (the next/prev pointers of the object and its neighbors). There is no

memory allocation or deallocation during these state transitions.3 This provides performance for checking out
and returning handles, which is critical for high-throughput environments.

The Table_cache_element and Hash Mapping

The Table_cache instance uses an std::unordered_map to map table names to Table_cache_element objects.7 A
Table_cache_element acts as a sub-container for a specific table within a single cache partition. It holds the
used_tables and free_tables lists for that table.7

When a thread requests a handle for table X, the system hashes the name and finds the corresponding
Table_cache_element in the partition's hash map. If the free_tables list in that element is not empty, the system unlinks a
handle from the list and provides it to the thread.7 If it is empty, the system must either create a new handle or evict an
unused handle of a different table to make room, depending on the table_open_cache limits.7

MEM_ROOT and Memory Efficiency

Memory for metadata is managed through the MEM_ROOT system, an arena-based allocator. Each TABLE_SHARE owns
its own mem_root, which is used for all metadata that must remain active as long as the table is in the cache.9 This is a
strategic choice for performance. Instead of performing hundreds of small allocations for columns, indexes, and
constraints—which would lead to memory fragmentation—the server performs a few large allocations within the
mem_root.9 When the TABLE_SHARE is finally destroyed, the entire mem_root is cleared at once, resulting in a single
deallocation operation for all associated metadata.9

Functionality and Lifecycle of a Table Handle

The functionality of the Table Cache Manager is best viewed through the lifecycle of a TABLE object, from its creation
via the Data Dictionary to its eventual eviction from memory.

Step 1: Initialization and the Role of table_open_cache_instances

Upon startup, the server initializes the table_cache_manager variable. The number of partitions is determined by
table_open_cache_instances. This setting is critical because it dictates the number of independent mutexes.3 For
example, on a server with 128 cores, setting this value to 1 or 16 might still result in significant contention. Setting it to 64
or 128 ensures that threads are highly likely to operate on different cache partitions.3

The total capacity is defined by table_open_cache. If table_open_cache is set to 4000 and table_open_cache_instances
is set to 40, each instance is effectively limited to 100 table handles.3 If an instance exceeds its local limit of unused
tables, it will begin closing them using the LRU algorithm to maintain the soft limit.11

Step 2: Requesting a Handle (The Open Operation)

When a query like SELECT * FROM employees is executed, the following sequence occurs within the Table Cache
Manager:

1.​ Partition Hashing: The system hashes the string "employees" to determine which of the partitions to access.3
2.​ Lock Acquisition: The thread acquires the m_lock mutex for that specific Table_cache partition.7
3.​ Element Lookup: The system looks for the Table_cache_element for "employees" in the partition's m_cache hash
map.7
4.​ Checkout:
○​ If a handle exists in the element's free_tables list, it is removed from that list and from the partition's
m_unused_tables list.7
○​ If no handle exists, the system checks if the TABLE_SHARE for "employees" is in the global definition cache. If
not, it is loaded from the Transactional Data Dictionary.2
○​ A new TABLE handle is instantiated and linked to the TABLE_SHARE.7
5.​ Activation: The handle is added to the element's used_tables list, and the mutex is released.7
Step 3: Use and Metadata Locking
While the handle is in the used_tables list, it is considered "In Use".7 During this phase, the TABLE_SHARE prevents any
DDL operations from modifying the table structure.4 If another thread attempts an ALTER TABLE employees, it will
request an exclusive metadata lock. The Table Cache Manager will detect that there are active handles in the
used_tables lists across various partitions and will force the ALTER statement to wait until those handles are released.4

Step 4: Release and LRU Management

Once the query completes, the thread calls Table_cache::release_table(). This is the inverse of the checkout process.7
The handle is moved from used_tables back to free_tables and is appended to the tail of the partition’s
m_unused_tables list.7

This "Most Recently Used" (MRU) placement ensures that tables frequently accessed by queries remain at the end of
the list, safe from eviction. Tables that are not accessed for a long period naturally migrate toward the head of the list as
newer entries are added to the tail.7

Step 5: Eviction and Closing

Eviction occurs when the number of handles in a partition exceeds the allocated quota. The
free_unused_tables_if_necessary() method is called during the release process.7 It checks the head of the
m_unused_tables list—the Least Recently Used (LRU) position—and removes the handle. This removal involves:
1.​ Unlinking the TABLE handle from the free_tables list of its Table_cache_element.
2.​ Unlinking it from the m_unused_tables list.
3.​ Decrementing the partition's m_table_count.
4.​ Calling the storage engine's close() function and deleting the TABLE object. 7

Integration with the Transactional Data Dictionary

The most significant architectural shift in MySQL 8.0 is the transition from a file-based metadata system to a
transactional, InnoDB-backed Data Dictionary.1 The Table Cache Manager is the primary consumer of this new
dictionary, and the relationship between the two is foundational to the server's improved reliability.

The Death [Link] Files

In all versions of MySQL prior to 8.0, the "blueprint" for a table was stored in a binary .frm file on the filesystem. Opening
a table required an OS-level file open and read operation, which was a major performance bottleneck in systems with
many tables.1 Furthermore, because the .frm file was separate from the storage engine's internal dictionary (e.g.,
InnoDB's system tablespace), it was possible for the two to become desynchronized during a crash or a failed DDL
operation.2

MySQL 8.0 eliminates .frm files entirely. All metadata—column definitions, index information, triggers, and partitions—is
now stored in hidden InnoDB tables within a dedicated tablespace called [Link].4
 Legacy Metadata (5.7 and
Feature earlier) MySQL 8.0 Transactional Data Dictionary
Storage Medium Filesystem (.frm, .trg, .par) InnoDB Tables ([Link]) 4
Non-transactional; prone to
Consistency desync ACID compliant; atomic transactions 4
Lookup Speed Limited by filesystem I/O Accelerated by InnoDB buffer pool and indexes 1
Concurrent Access Serialized by LOCK_open Scalable via Data Dictionary Cache and partitioned Table Cache 2

Atomic DDL and Cache Invalidation

Because the Data Dictionary is transactional, DDL operations in MySQL 8.0 are atomic. When a user executes RENAME
TABLE t1 TO t2, the change is made as a single InnoDB transaction.4 The Table Cache Manager plays a critical role in this
atomicity.

When a DDL transaction commits:

1.​ The Data Dictionary is updated.
2.​ The Table_cache_manager is notified to invalidate the TABLE_SHARE for the old table name.2
3.​ The manager uses the Table_cache_iterator to find and close all handles associated with that share across all
partitions.7

If the DDL transaction fails or the server crashes mid-operation, the InnoDB rollback mechanism ensures the Data
Dictionary reverts to its previous state. Upon restart, the Table Cache Manager simply re-loads the original, consistent
metadata, ensuring that the server layer and engine layer are never out of sync.4

Performance Impact and Information Schema Optimization

The combination of a partitioned Table Cache and a transactional Data Dictionary has transformed the performance of
metadata-heavy workloads. This is most evident in the INFORMATION_SCHEMA subsystem, which provides users with
views into the database's internal state.

Information Schema as Views

In legacy MySQL, a query against INFORMATION_SCHEMA.TABLES was an incredibly expensive operation. The server
would create a temporary table, scan the data directory for .frm files, open each file, and extract the metadata.1 For a
database with 100,000 tables, this could take minutes and potentially crash the server due to memory exhaustion or file
descriptor limits.1

In MySQL 8.0, INFORMATION_SCHEMA tables are implemented as views over the actual Data Dictionary tables.1 When a
user queries INFORMATION_SCHEMA.COLUMNS, the MySQL optimizer can now use indexes on the internal Data
Dictionary tables to find the relevant data quickly. This eliminates the need for temporary tables and filesystem scans,
leading to performance improvements of up to 30x for static metadata queries.5
Dynamic Statistics and Caching
The Table Cache Manager also handles the caching of dynamic table statistics, such as the number of rows or the next
auto-increment value.5 Because these values change frequently, retrieving them from the storage engine (e.g., asking
InnoDB for an exact row count) can be expensive.

MySQL 8.0 introduces the information_schema_stats variable, which controls whether the server retrieves latest
statistics directly from the engine or uses cached values from the Data Dictionary.5

Configuration Behavior Performance Impact

Fastest; 30x speedup; may

information_schema_stats= Uses values cached in the
cached Data Dictionary / Table be slightly outdated. 5
Cache.

Slower; 10-60% faster than

information_schema_stats= Forces a call to the storage
latest engine for real-time stats. 5.7; always accurate. 5

Configuration Variables and System Tuning

The behavior of the Table Cache Manager is governed by several interconnected system variables. Tuning these is
essential for optimizing MySQL for specific hardware and workloads.

table_open_cache
This is the primary limit on the number of open table handles.10 It is related to the max_connections setting. A common

rule of thumb is to set table_open_cache to at least max_connections * N, where is the maximum number of tables
10
involved in a single join in the most complex query.

Increasing this value allows the server to keep more handles in the m_unused_tables lists, increasing the hit rate and
reducing the need to open and close files. However, it also increases the number of open file descriptors. Administrators
must ensure that the OS-level open_files_limit is sufficiently high to accommodate this setting.10

table_definition_cache
This variable limits the number of TABLE_SHARE objects stored in the global registry.3 Unlike the table handles, which
can have multiple instances for the same table, there is only ever one TABLE_SHARE per table. Therefore, this setting
should ideally be equal to the total number of unique tables in the database.

If this limit is too small, the server will have to frequently evict and re-load table definitions from the Data Dictionary.
While this is faster than reading .frm files, it still incurs a cost that can be avoided with proper sizing.1
table_open_cache_instances
​
As discussed, this variable determines the number of partitions in the Table Cache Manager.3
●​ Low Values (1-8): Suitable for small servers or those with low concurrency.
●​ High Values (16-64): Recommended for modern multi-core systems to reduce mutex contention.3

A potential drawback of very high values is the fragmentation of the cache capacity. Since each instance has its own
quota, setting this too high with a small table_open_cache could lead to premature evictions in some partitions while
others are nearly empty.8

Percona-Specific Enhancements: table_open_cache_triggers

Percona Server for MySQL 8.0 adds a specialized variable, table_open_cache_triggers, to address memory consumption
in workloads with many triggers.11 Triggers can consume significant memory when fully loaded into the table cache. This
variable allows the server to limit the number of handles with loaded triggers, evicting the trigger body while keeping
the base table handle in the cache to save memory.11

Mathematical Analysis of Cache Performance

The performance of the Table Cache Manager can be analyzed through the lens of queueing theory and probability. The
goal of the partitioning strategy is to minimize the probability of "lock wait" states.

If threads are concurrently attempting to access the table cache, and there are partitions, the probability that a

specific partition is requested by threads follows a binomial distribution:

The probability of zero contention for a specific thread is the probability that no other thread is accessing its assigned
partition:

For a high-concurrency server with threads and partition (legacy behavior), the probability of

contention is . If the partitions are increased to , the probability of no contention for any given thread
rises to approximately , significantly reducing the average wait time for the m_lock mutex.

Furthermore, the hit rate of the table cache can be expressed as:

A low hit rate indicates that the table_open_cache is too small, forcing the server into expensive "open-from-disk"
cycles. This can be monitored via the Opened_tables status variable. If Opened_tables is increasing rapidly, it suggests
that the cache is frequently evicting handles that are needed shortly thereafter.8
Source Code Verification: Lifecycle and State Transitions
Further digging into sql/table_cache.cc confirms the specific implementation of the state transitions.

Verification of table_cache_manager::init
The init() function of the manager is responsible for allocating the array of Table_cache objects.6 The code confirms that

it allocates instances (where ) and initializes the mutex for each. This
verification proves the "partitioned array" architecture described in the architectural documentation.3

Verification of Table_cache::release_table
The implementation of release_table() shows the handle being moved to the tail of the m_unused_tables list.7 The use of

link_unused_table() and unlink_unused_table() inline methods demonstrates the optimization for movements.7

Verification of Table_cache_element::free_tables
The Table_cache_element class, as verified in sql/table_cache.h, indeed uses the I_P_List template to manage its internal
used_tables and free_tables.7 The source code shows that these lists are back-linked, ensuring that any handle can be
removed in constant time regardless of its position in the list.7

Analysis of Table_cache_manager Instance Variables

The Table_cache_manager serves as the high-level coordinator. While the document describes the logic rather than
providing raw C++ headers, it identifies several critical internal structures that the manager uses to handle TABLE and
TABLE_SHARE objects.

1. The Partition Array (Table_cache[])

The most significant variable in the manager is an array of Table_cache instances.

●​ Relevance to TABLE: The manager does not store TABLE handles directly. Instead, it delegates this to the array.
Each Table_cache instance in the array owns the "Used" and "Free" intrusive lists that actually contain the
TABLE objects.
●​ Variable Context: The size of this array is determined by the table_open_cache_instances configuration
variable.

2. The Global Registry for TABLE_SHARE

The manager maintains a Global Registry (often implemented as a hash map or specialised search tree) for
TABLE_SHARE objects.

●​ Relevance to TABLE_SHARE: This registry ensures that for any physical table (e.g., [Link]), only one
"Blueprint" (TABLE_SHARE) exists in memory.
●​ Ownership: The document explicitly states that the Table_cache_manager "owns" these objects, managing
their lifecycle and ensuring they are shared across all sessions.

3. Intrusive List Pointers (Embedded Variables)

While these reside inside the TABLE and TABLE_SHARE classes, the manager is the primary logic engine that manipulates
them:

●​ TABLE Variables: Each handle contains next and prev pointers. The manager/instances use these to move a
table handle between the Free List (ready for reuse) and the Used List (currently locked by a thread).
●​ TABLE_SHARE Variables: Each share contains a pointer to its own mem_root. The manager uses this to access
the metadata blueprint without re-reading the .frm or data dictionary information from disk.

4. Configuration State Variables

The manager tracks three specific state variables that define its capacity:

1.​ table_open_cache: An integer defining the maximum total number of TABLE handles allowed across all
partitions.
2.​ table_definition_cache: An integer defining the maximum number of TABLE_SHARE objects allowed in the
global registry.
3.​ table_open_cache_instances: An integer defining the size of the partition array mentioned in point #1.

Summary Table: Manager Responsibilities

Object Type Managed By Storage Location Key Purpose

TABLE Table_cache Intrusive Lists Tracks session-specific state

Instance (Used/Free) (e.g., cursor position).

TABLE_SHA Table_cache_manag Global Registry Stores shared metadata (e.g.,

RE er column types).

Explanation of Embedded Variable - Intrusive List Pointers

In the context of MySQL's Table Cache Manager, "embedded variables" refer to the practice of placing the data
structure's "plumbing" (the next and prev pointers) inside the actual data object itself.

In standard programming, you usually put an object into a container (like a list or an array). In an intrusive list, the
object is the container node.

The Comparison
To understand this, compare how a standard list works versus how MySQL's intrusive list works.

1. The Standard Way (Non-Intrusive)

In a standard library like C++ std::list, the list creates a "wrapper" or "node" that holds your object.

C++

None
// Your data
struct Table {
 string name;
};

// The Standard Library creates this behind the scenes:

struct ListNode {
ListNode* next; // Pointer logic is OUTSIDE your data
ListNode* prev;
Table data; // Your data is "wrapped" inside
};

​
Problem: Every time you add a Table to the list, the computer has to perform a second memory allocation for the
ListNode. This causes memory fragmentation and "cache misses" (slower performance).

2. The MySQL Way (Embedded / Intrusive)

In the MySQL source code, the TABLE and TABLE_SHARE objects have the pointers built directly into their class
definition.

C++

None

// The pointers are EMBEDDED variables

struct TABLE {
// Embedded Pointers
TABLE* next; // These variables are "Embedded"
TABLE* prev; // They live inside the object

// Actual Data
string table_name;
uint32_t record_count;
};

Why this is critical for the Table Cache Manager

By using embedded variables, the Table_cache_manager achieves three things:
1.​ Zero-Allocation Insertion: When the manager needs to move a TABLE handle from the "Free List" to the "Used
List," it doesn't need to allocate any new memory. It simply updates the next and prev variables that already exist
inside the object.
2.​ Memory Locality: When the CPU loads a TABLE object into its cache, it also automatically loads the pointers to
the next table in the list. This makes iterating through thousands of tables significantly faster.
3.​ No "Wrapping" Overhead: Since there is no ListNode wrapper, the manager saves several bytes of memory for
every single table handle—which adds up when you have 4,000+ tables open.

Real-World Example from the Manager

In the Table_cache partitions, these embedded variables allow for a "LRU" (Least Recently Used) eviction policy.
●​ Logic: When a table is used, the manager accesses the embedded next/prev pointers to "unhook" the table
from its current position and "hook" it to the front of the list.
●​ Efficiency: This happens in constant time (O(1)) without ever calling a new or malloc command, which is why
MySQL can handle high-concurrency traffic without slowing down.
Table Cache Manager Class Definition

Based on the architectural details of the Table_cache_manager, the declaration follows a specific hierarchy. It is
important to distinguish between the Manager (which owns the partitions), the Cache Instance (which owns the list
heads), and the Table Object (which owns the embedded pointers).

In the MySQL source, this is implemented using a pattern where the manager coordinates an array of list managers.

Declaration Hierarchy
1.​ In Table_cache_manager: It declares an array (or pointer to an array) of Table_cache instances.
2.​ In Table_cache: It declares the "Heads" of the lists (the starting points).
3.​ In TABLE: It declares the "Embedded Pointers" (next and prev).

We will examine the representative C++ structure of these declarations below.

Class Declaration
/**
* 1. THE EMBEDDED POINTERS (Inside the TABLE object)
* This is what makes the list "intrusive." The TABLE object
* carries its own list metadata.
*/
class TABLE {
public:
// These are the "Embedded Variables" or pointers.
// In MySQL, these are often managed by a template like I_P_List.
TABLE *next;
TABLE *prev;

// Actual data member variables

TABLE_SHARE *s; // Pointer to the blueprint
THD *in_use; // Which thread is using this handle
// ... other table metadata ...
};

/**
* 2. THE CACHE INSTANCE (The partition)
* Each instance manages its own subset of the total cache.
*/
class Table_cache {
private:
/**
* These are the List Heads (Sentinels).
* They define the start/end of the intrusive lists.
* MySQL uses specialized templates (I_P_List) to handle the
* logic of these intrusive pointers.
*/
I_P_List<TABLE> m_unused_tables; // The "Free" list (LRU)
I_P_List<TABLE> m_used_tables; // The "Used" list (Active)

// The lock that protects THIS partition only

mysql_mutex_t m_lock;
};

/**
* 3. THE TABLE_CACHE_MANAGER (The Global Singleton)
 It coordinates the partitions.
*/
class Table_cache_manager {
public:
// Total number of partitions (table_open_cache_instances)
uint m_table_cache_instances;

/**
* THE ARRAY DECLARATION:
* This is how the manager holds the caches. It's a pointer to
* an array of Table_cache objects.
*/
Table_cache *m_table_caches;

// Logic to route a request to a specific cache instance

Table_cache* get_cache(THD *thd);
};

Key Takeaways from the Declaration:

●​ The Manager doesn't see the Pointers: The Table_cache_manager class itself usually only declares the array
(Table_cache *m_table_caches). It doesn't "see" the next or prev pointers of individual tables.
●​ The "Head" Variable: Inside each Table_cache instance, the list is declared using a template (like
I_P_List<TABLE>). This template expects the TABLE class to have those next and prev variables already defined
inside it.
●​ Encapsulation: This design allows the manager to scale. If you have 16 cores, the manager initializes 16
Table_cache instances. Each instance has its own m_unused_tables list head, and threads can "hook" and
"unhook" TABLE objects from those lists simultaneously without blocking each other.

Summary of the variables:

●​ m_table_caches: The variable in the Manager class that points to the array of partitions.
●​ m_unused_tables / m_used_tables: The variables in the Cache Instance that act as list anchors.
●​ next / prev: The embedded variables in the Table object that form the actual links.

LRU Logic Implementation for Table Cache

The Least Recently Used (LRU) algorithm in the MySQL Table Cache Manager is highly efficient because it leverages
those embedded pointers to move objects between lists in constant time $O(1)$.

In this system, "Least Recently Used" specifically applies to the Unused List. When a thread finishes with a table, it isn't
deleted; it is moved to the "Most Recently Used" end of the Unused List. If the cache hits its limit, the manager "evicts"
(closes) the tables at the "Least Recently Used" end.

I have generated a technical breakdown of the C++ logic used to manipulate these pointers for the LRU cache.

/**
* SIMULATED LRU LOGIC FOR MYSQL TABLE CACHE
* * This code demonstrates how the Table_cache_manager and its instances
* manipulate embedded pointers to perform LRU operations without
* memory allocation overhead.
*/

struct TABLE {
const char* table_name;
 TABLE *next; // Embedded pointer
TABLE *prev; // Embedded pointer
bool in_use;
};

class Table_cache {
private:
// Sentinel nodes for our intrusive lists
TABLE *unused_list_head = nullptr;
TABLE *unused_list_tail = nullptr;

TABLE *used_list_head = nullptr;

int unused_count = 0;
int max_cache_size = 2000;

/**
* UNHOOK (Internal)
* Removes a node from its current list by updating its neighbors.
* This is O(1) because the node 'knows' its neighbors.
*/
void unhook(TABLE *table) {
if (table->prev) table->prev->next = table->next;
if (table->next) table->next->prev = table->prev;

// Update list heads/tails if necessary

if (unused_list_head == table) unused_list_head = table->next;
if (unused_list_tail == table) unused_list_tail = table->prev;

table->next = nullptr;
table->prev = nullptr;
}

/**
* HOOK TO UNUSED (MRU Position)
* Adds a table to the "Most Recently Used" end of the unused list.
*/
void hook_to_unused_mru(TABLE *table) {
table->next = unused_list_head;
table->prev = nullptr;

if (unused_list_head) unused_list_head->prev = table;

unused_list_head = table;

if (!unused_list_tail) unused_list_tail = table;

unused_count++;
}

public:
/**
* RELEASE TABLE (The LRU "Push")
* Called when a thread is done with a table.
*/
void release_table(TABLE *table) {
// 1. Remove from the 'Used' list
unhook(table);

// 2. Mark as not in use

table->in_use = false;

// 3. Move to the front of the 'Unused' list (MRU)

hook_to_unused_mru(table);
 // 4. If cache is too big, evict the tail (LRU)
if (unused_count > max_cache_size) {
evict_lru();
}
}

/**
* EVICT LRU (The LRU "Pop")
* Closes the table that hasn't been used for the longest time.
*/
void evict_lru() {
if (!unused_list_tail) return;

TABLE *lru_table = unused_list_tail;

// The tail is the LEAST recently used.

unhook(lru_table);
unused_count--;

// Logic to physically close the table file descriptor

// close_frm_files(lru_table);
delete lru_table;
}

/**
* ACQUIRE TABLE (The Cache "Hit")
* Check if the table is in the unused list.
*/
TABLE* acquire_table(const char* name) {
// Search the unused list...
// If found:
// 1. unhook(found_table)
// 2. move to used_list_head
// 3. return found_table
return nullptr;
}
};

Why this logic is superior for MySQL:

1.​ The "Unhook" Advantage: In a standard std::list, if you want to remove an item, you often need an iterator to
that item. With embedded pointers, the TABLE object is the iterator. If the manager has a pointer to the table, it
can remove it from a list in exactly 3-4 CPU instructions by just reassigning the next and prev of its neighbors.
2.​ No Fragmentation: Because release_table just changes pointers and doesn't "delete" and "re-create" the
object, the memory stays exactly where it is. This is why MySQL can keep thousands of tables open without the
memory manager getting "confused" or fragmented.
3.​ The MRU/LRU Balance: By always "hooking" to the head and "evicting" from the tail, the manager ensures that
tables for "hot" queries (like a frequently accessed users table) stay near the head of the list and are never
evicted.

Summary of the process:

●​ Active use: Table is in the used_list.
●​ Query ends: Table is unhooked from used and hooked to the head of unused (it is now the Most Recently
 Used).
●​ Cache full: The table at the very tail of unused (the one that hasn't moved for the longest time) is unhooked
and deleted.

Architectural Linkage: Thread Context and the Table Cache Handle Pool

To understand the connection between a Thread (THD class) and the Table (TABLE class) within the Table_cache
architecture, it helps to visualize it as a "checkout" system.

The THD object doesn't "own" the table permanently; it "borrows" a handle from the cache and links to it for the
duration of a query.
1. The Linkage: THD to TABLE
Within the THD class (the thread object), there is a pointer to a list of tables currently being used by that session.
●​ The Pointer: When a thread needs to open tables, it populates its own THD::temporary_tables list or its
THD::open_tables list.
●​ The Connection: Each entry in this list is a pointer to a TABLE object. This TABLE object is the specific "handle"
that contains the thread's private state (like its current position in a scan).

2. The Table_cache and the Intrusive List

The Table_cache partition manages the "pool" of these TABLE handles. It organizes them using intrusive linked lists
(often referred to as the "Free" list and the "Used" list).
●​ The Header: The Table_cache object contains the head and tail pointers for these lists.
●​ The Intrusive Nature: The "links" (next and previous pointers) are not stored in a separate container; they are
actual member variables inside the TABLE object itself (usually via a prev and next pointer).

3. The Lifecycle: Checking Out a Handle

When a session (Thread) wants to query a table, the following "linking" operation occurs:
1.​ Search: The Thread asks the Table_cache_manager for a table. The manager identifies the correct Table_cache
partition.
2.​ Checkout: The Table_cache looks at its Free List. It finds an available TABLE handle that points to the correct
TABLE_SHARE (the blueprint).
3.​ Relinking:
○​ The Table_cache removes that TABLE object from its Free List.
○​ The Table_cache adds that TABLE object to its Used List.
○​ The Thread (THD) adds a pointer to this same TABLE object into its own open_tables list.
4.​ Execution: The Thread now has a direct path to the data through that TABLE handle.

4. Why this matters for Memory and Concurrency

●​ Shared Metadata: Even though the THD is linked to a private TABLE handle, that handle points back to the
global TABLE_SHARE. This is how the Data Dictionary remains shared.
●​ Efficient Cleanup: When the query ends, the Thread simply "returns" the handle. The TABLE object stays in
memory but is moved from the Table_cache Used List back to the Free List. The pointers in the THD object are
cleared.

Summary of the "Chain"

●​ THD -> holds a list of pointers to...
●​ TABLE -> which contains "next/prev" pointers used by...
●​ Table_cache -> to maintain its global linked lists of handles.

This architecture allows a thread to have a dedicated, private workspace (TABLE) while the manager maintains a
high-speed inventory of all available handles without constantly allocating and deallocating memory.
MySQL Table Management and Memory Allocation: A Thread-Based Workflow
Analysis.

This report outlines the technical interaction between User Threads and Background Threads, focusing on how memory
is allocated for table blueprints (TABLE_SHARE) and execution handles (TABLE).

When the User Thread fails to find an idle handle in the Table Open Cache, it must "manufacture" a new one. This
process relies on the TABLE_SHARE acting as the master template to ensure every handle for the same table is
consistent.
1. The Template Relationship
A TABLE_SHARE is a singleton object; there is only one per table in the entire MySQL instance. It contains all the
"unchanging" metadata that every thread needs to know:
●​ Structural definition: Column names, data types, and nullability.
●​ Key information: Primary keys and secondary index definitions.
●​ Storage engine details: Which engine owns the table (e.g., InnoDB).

The TABLE handle, by contrast, is a "working copy". While the TABLE_SHARE tells the thread what the table looks like,
the handle tracks where that specific thread is currently located within the data.

2. The Manufacturing Process

When the User Thread finds the blueprint in the Table Definition Cache, it performs the following steps to build the
handle:
●​ Memory Allocation: The User Thread allocates a new block of memory (tracked by the memory/sql/TABLE
instrument).
●​ Pointer Linking: The newly created handle is given a pointer that "points back" to the TABLE_SHARE. This allows
the handle to reference the schema without duplicating the metadata in memory.
●​ Initialization: The User Thread initializes thread-specific variables within the handle, such as read/write buffers
and record pointers.
●​ Engine Handshake: The handle opens a connection to the storage engine (e.g., InnoDB), establishing the
pathway to fetch actual data pages.

3. Why This Separation Exists

This "blueprint and handle" architecture is designed for high concurrency:

Feature TABLE_SHARE (Blueprint) TABLE (Handle)

Quantity One per table, globally. Many per table (one per active use).

Lifecycle Stays in cache until evicted. Created/destroyed or cached for

reuse.

Memory Stores static schema data. Stores dynamic execution state.

4. Directing Your Investigation

In the context of your CloudSQL memory spike, this "manufacturing" step is critical. If a User Thread is forced to
manufacture many handles because of a high max_connections setting or complex joins, the memory/sql/TABLE
allocation grows.

If the User Thread also cannot find the blueprint, the manufacturing process stalls as the thread hits the disk to rebuild
the TABLE_SHARE first, causing a spike in both memory (for the new share) and latency.
In the MySQL architecture, the relationship between the TABLE handle, the innodb_handler object, and the underlying
storage details is a layered hierarchy that allows a User Thread to navigate data stored in the Buffer Pool.

The following breakdown explains how these components relate to one another based on the details contained within
the handle:

1. The TABLE Handle as the Logical Entry Point

The TABLE handle is the "working copy" used by a User Thread to track its position during a query. By looking at the
details within this handle, the thread identifies:
●​ The Blueprint Link: A pointer back to the TABLE_SHARE, which provides the static schema (columns, types, and
index definitions).
●​ Operational State: The handle stores thread-specific information, such as which indexes are currently being
used for a scan and the status of the "current" row being processed.

2. The innodb_handler Object as the Functional Bridge

Contained within the TABLE handle is the storage engine-specific handler object (e.g., ha_innodb). This object acts as
the functional bridge between MySQL's logical SQL layer and InnoDB's physical storage layer.
●​ Interface Implementation: While the TABLE handle defines what data is needed, the innodb_handler knows
how to get it from InnoDB.
●​ Session State: It maintains the internal state of the storage engine for that specific thread, such as active
transaction information and row-level locking status.

3. Relation to InnoDB Storage Details

When the User Thread uses the details in the handle to request data, the innodb_handler translates these logical
requests into physical memory addresses within the Buffer Pool.
●​ Data Mapping: The handler object communicates with Background Threads to ensure the correct data and
index pages are loaded into the Buffer Pool.
●​ Memory Visibility: By using the pointer chain (User Thread → TABLE Handle → innodb_handler), the thread can
"see" and manipulate specific data pages residing in the 37GB Buffer Pool.
●​ Record Access: Details in the handle (like record buffers) are populated with data fetched by the handler from
the physical pages, allowing the User Thread to perform operations like sorting in its private memory (e.g., the
sort_keys buffer).

Summary of the Relationship

Component Information Contained Role in Storage Access

TABLE Handle Pointers to TABLE_SHARE and Defines the logical structure

handler object. and current query state.
 innodb_handler InnoDB-specific engine state Translates SQL commands
and API methods. into engine-level requests.

Buffer Pool Physical data and index pages. The destination where the
handler finds the actual
data.

This structure ensures that the User Thread remains isolated from the complexities of physical storage while
maintaining a direct, efficient path to the data managed by the Background Threads.

THD , Table Handle and Handler Object

In the MySQL architecture, the User Thread (represented by the THD class object) is the entity responsible for
coordinating the link between a table handle and the data residing in the Buffer Pool.

The Table Cache Manager provides the TABLE handle, but that handle is essentially an empty vessel until the User
Thread initializes the connection to the storage engine.

The Linking Mechanism: The "Handler" Object

The connection is not a direct pointer from the THD object to the Buffer Pool. Instead, it uses an intermediary called the
Handler Object:
1.​ Allocation: When the User Thread manufactures or acquires a TABLE handle, it also instantiates a storage
engine-specific handler interface (e.g., ha_innodb).
2.​ The Link: This handler object is stored within the TABLE handle. The User Thread then uses this handler to
request specific data pages.
3.​ Data Access: The handler object communicates with the Background Threads (InnoDB) to locate the required
pages within the Buffer Pool.

Workflow of the Linkage

●​ User Thread Context: All of this activity—searching the cache, linking the handler, and requesting data—happens
within the execution context of the User Thread.
●​ The Pointer Chain: The THD (User Thread) points to the TABLE handle, which contains the handler object, which
in turn holds the internal state necessary to interface with the Buffer Pool managed by Background Threads.
●​ Buffer Pool Interaction: If the required data is not in the Buffer Pool, the Background Threads perform the
physical I/O to bring it from disk into the pool so the User Thread's handle can "see" it.

Summary of the Workflow Linkage

To conclude the specific technical chain we discussed:

●​ The Actor: The User Thread (THD class) is the primary driver.
●​ The Link: The User Thread creates a Handler Object (ha_innodb) inside the Table Handle (TABLE).
●​ The Bridge: This handler object acts as the bridge to the Buffer Pool managed by Background Threads.
●​ The Result: This allows the User Thread to "see" and process the data pages it needs for your query.
Why this is relevant to a 16GB spike in 37GB buffer pool & 50GB RAM host
A 16GB peak in sort_keys was for the User Thread using these very handles to pull massive amounts of data through the
handler interface from the 37GB Buffer Pool into its own private memory area for sorting. The crash occurred because
the User Thread requested more memory for that sort than the physical RAM remaining after the Background Threads
had claimed their 37GB.

Architectural Analysis: Thread-Based Memory Allocation and

Concurrency Peaks
To understand the incremental growth of memory peaks, we must examine the "handshake" between User Threads
(the workers) and Background Threads (the storage managers). In this architecture, these two groups operate in
different functional areas but coordinate at the Buffer Pool.

1. The Role of the Handler Object

When a query is executed, the User Thread does not directly interface with the raw data files. Instead, it creates a
Handler Object, which acts as a specialized translator or "API client" for the storage engine.
●​ Interface: The User Thread instructs the Handler to retrieve specific data, such as requesting the next row from
a specific table based on a unique identifier.
●​ Communication: The Handler then issues a request to the storage engine (e.g., InnoDB) to fulfill that precise
data requirement.

2. Coordination with Background Threads

Background Threads act as the primary managers for the global Buffer Pool. Their objective is to ensure that the data
requested by a Handler is available in RAM rather than residing only on disk.
●​ Locating Pages: When a Handler requests a row, it is effectively looking for a specific data page.
●​ The Buffer Pool Check: Background Threads maintain a hash map of all pages currently residing in the Buffer
Pool.
●​ Access Management: If the page is already cached (a "Cache Hit"), the Background Thread grants the Handler
access to that memory space.
●​ Disk I/O Operations: If the page is missing, the Background I/O threads must fetch the data from disk, populate
the Buffer Pool, and notify the Handler once the data is ready for processing.

3. Mechanics of Incremental Memory Spikes

This interaction explains why global memory peaks often grow in small, incremental steps:
●​ Contention: As multiple User Threads utilize their Handlers to communicate with Background Threads, they
must compete for synchronization primitives (mutexes or locks) on the shared Buffer Pool resources.
●​ Resource Clashes: Each individual Handler requires its own transient workspace, such as a sort_keys or
record_buffer, to process the data retrieved from the global pool.
●​ The Aggregate Peak: The observed global peak is the result of many Handlers requesting data and allocating
private workspace memory at the exact same microsecond. As the number of active Handlers increases or their
operations overlap more frequently, the "global high-water mark" of their combined memory usage climbs.
Summary: Thread-Based Workflow Analysis
●​ Observation: Data access is a coordinated effort between transient User Threads and persistent Background
Threads.
●​ Conclusion: The Handler Object serves as the bridge that allows User Threads to pull data from the global
Buffer Pool into private, operational sorting and processing buffers.
●​ Risk Factor: The incremental increase in the memory peak is the statistical high-water mark created by
concurrent Handlers clashing as they compete for Background Thread resources.

Would you like me to help you draft a section on how to tune the TABLE_OPEN_CACHE to reduce the memory footprint
of these Handler objects?

InnoDB Handler within Table handle Object

In the MySQL architecture, the relationship between the TABLE handle, the innodb_handler object, and the underlying
storage details is a layered hierarchy that allows a User Thread to navigate data stored in the Buffer Pool.

The following breakdown explains how these components relate to one another based on the details contained within
the handle:

1. The TABLE Handle as the Logical Entry Point

The TABLE handle is the "working copy" used by a User Thread to track its position during a query. By looking at the
details within this handle, the thread identifies:
●​ The Blueprint Link: A pointer back to the TABLE_SHARE, which provides the static schema (columns, types, and
index definitions).
●​ Operational State: The handle stores thread-specific information, such as which indexes are currently being
used for a scan and the status of the "current" row being processed.

2. The innodb_handler Object as the Functional Bridge

Contained within the TABLE handle is the storage engine-specific handler object (e.g., ha_innodb). This object acts as
the functional bridge between MySQL's logical SQL layer and InnoDB's physical storage layer.
●​ Interface Implementation: While the TABLE handle defines what data is needed, the innodb_handler knows
how to get it from InnoDB.
●​ Session State: It maintains the internal state of the storage engine for that specific thread, such as active
transaction information and row-level locking status.

3. Relation to InnoDB Storage Details

When the User Thread uses the details in the handle to request data, the innodb_handler translates these logical
requests into physical memory addresses within the Buffer Pool.
●​ Data Mapping: The handler object communicates with Background Threads to ensure the correct data and
index pages are loaded into the Buffer Pool.
●​ Memory Visibility: By using the pointer chain (User Thread → TABLE Handle → innodb_handler), the thread can
"see" and manipulate specific data pages residing in the 37GB Buffer Pool.
 ●​ Record Access: Details in the handle (like record buffers) are populated with data fetched by the handler from
the physical pages, allowing the User Thread to perform operations like sorting in its private memory (e.g., the
sort_keys buffer).

Summary of the Relationship

Component Information Contained Role in Storage Access

TABLE Handle Pointers to TABLE_SHARE and Defines the logical

handler object. structure and current
query state.

innodb_handler InnoDB-specific engine state Translates SQL commands

and API methods. into engine-level requests.

Buffer Pool Physical data and index The destination where the
pages. handler finds the actual
data.

This structure ensures that the User Thread remains isolated from the complexities of physical storage while
maintaining a direct, efficient path to the data managed by the Background Threads.

Identifying Table Cache Pressure

Based on the architectural relationship between the Table Cache and the InnoDB Buffer Pool, it is important to clarify
a distinction in how MySQL tracks these metrics.

The information_schema.innodb_metrics table focuses primarily on the Storage Engine (InnoDB), while the caches you
mentioned—table_open_cache and table_definition_cache—are managed at the Server Level (the SQL Layer).
Consequently, there are no specific metrics within the innodb_metrics table named after "open_cache" or
"table_definition_cache," as those are not InnoDB-internal structures.

However, high activity in these caches can be identified through the following metrics in the Global Status and InnoDB
Metrics tables:

1. Identifying Table Cache Pressure (Server Level)

To see if your table caches are hitting their limits and causing LRU-style turnover, you monitor these Global Status
variables:

●​ Table_open_cache_hits / Table_open_cache_misses: A high miss rate indicates the User Threads are
frequently failing to find a "warm" handle in the cache.
●​ Table_open_cache_overflows: This is the most direct indicator of LRU activity; it counts how many times a
cache entry was evicted because the cache reached its capacity.
2. Correlated InnoDB Metrics (Storage Level)
While InnoDB doesn't manage the table cache, high cache turnover often correlates with specific activity in
information_schema.innodb_metrics. The metrics to watch for high "LRU-like" activity related to table metadata and
data access are:

Metric Name (in

innodb_metrics) Description of Activity
dict_table_open Tracks the number of tables currently opened within the InnoDB Data Dictionary.
dict_table_close Tracks how often InnoDB closes a table object internally.
buffer_pool_pages_lru_flus Shows high LRU pressure in the Buffer Pool, which often occurs when many different tables
hed are being accessed rapidly due to cache misses.
buffer_LRU_batch_flush_av Indicates how hard the Background Threads are working to clear space in the Buffer Pool
g_time LRU list.
3. The Mechanism of "Activity"
When the table_open_cache is full, the User Thread must perform an eviction.

1.​ Eviction: The Thread identifies the Least Recently Used (LRU) handle in the cache.
2.​ Notification: It notifies the InnoDB Handler that the handle is being destroyed.
3.​ InnoDB Cleanup: This triggers a dict_table_close event inside InnoDB as the storage engine synchronizes the
table's state.
4.​ Replacement: The User Thread then loads the "Blueprint" from the table_definition_cache to create a new
handle for the requested table.

Summary for Report

To track high turnover (LRU activity) for these caches, do not look for a single metric in innodb_metrics. Instead, use the
Table_open_cache_overflows status variable to detect server-level evictions and correlate it with
dict_table_open/close in information_schema.innodb_metrics to see the impact on the storage engine.

To see the correlation between server-level Table Cache overflows and InnoDB Data Dictionary activity, you can join
the global status variables with the InnoDB metrics. This provides a clear view of how cache pressure at the SQL layer
forces the storage engine to open and close internal table objects.

Run this query to see the relationship (MySQL 8.0.30)

SELECT 'Server Status' AS Source,

VARIABLE_NAME AS Metric_Name,
VARIABLE_VALUE AS Value,
'N/A' AS Subsystem
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ( 'Table_open_cache_hits', 'Table_open_cache_misses',
'Table_open_cache_overflows' )
UNION ALL
SELECT 'InnoDB Metric' AS Source,
NAME AS Metric_Name,
COUNT AS Value,
SUBSYSTEM AS Subsystem
FROM information_schema.innodb_metrics
WHERE NAME IN ( 'dict_table_open', 'dict_table_close' )
ORDER BY Source DESC;

MySQL 8.0.42​
SELECT 'Server Status' AS Source,
VARIABLE_NAME AS Metric_Name,
VARIABLE_VALUE AS Value,
'N/A' AS Subsystem
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ( 'Table_open_cache_hits', 'Table_open_cache_misses',
'Table_open_cache_overflows' )
UNION ALL
SELECT 'Dictionary Layer' AS Source,
VARIABLE_NAME AS Metric_Name,
VARIABLE_VALUE AS Value,
'Metadata Cache' AS Subsystem
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ( 'Open_table_definitions',
'Opened_table_definitions' )
ORDER BY Source DESC;

How to Interpret the "LRU" Activity in the Results

●​ Table_open_cache_overflows: This is your primary indicator of LRU eviction. Every time this increments, a User
Thread has been forced to kick a table handle out of the cache to make room for a new one.
●​ dict_table_close: In the InnoDB subsystem, this metric increments when the storage engine releases its internal
metadata for a table. High values here, following a high overflow count, confirm that the cache churn is
impacting the Background Threads' ability to keep the data dictionary stable.
●​ Table_open_cache_misses: This shows how often a User Thread had to stop and "manufacture" a new handle
because the requested table wasn't in the cache.

Why this Matters for the Independent Report

1.​ Thread Efficiency: High overflows mean User Threads are wasting CPU cycles performing LRU management
(evictions and re-allocations) rather than processing data.
2.​ Memory Jitter: Each time a handle is evicted and recreated, the User Thread private memory usage fluctuates.
This contributes to the "Incremental" memory peaks discussed earlier, as multiple threads clashing during
eviction cycles create global memory pressure.
3.​ Storage Engine Impact: While the cache is at the server layer, the churn creates a "ripple effect" into InnoDB,
forcing it to frequently open and close file handles at the OS level.

Does the number for Table_open_cache_overflows in your result look significantly higher than the number of
hits? If so, your cache is currently "thrashing."

Conclusion: The Modernized Metadata Engine

The MySQL 8.0 Table Cache Manager is a masterpiece of systems engineering, designed to meet the demands of
modern, cloud-scale database workloads. By transitioning to a partitioned architecture and integrating deeply with a
Transactional Data Dictionary, it has eliminated decades-old bottlenecks and introduced a level of reliability and
atomicity previously reserved for the most expensive proprietary database systems.

The move from file-based .frm metadata to the InnoDB-backed dictionary has not only improved performance—as seen
in the 30x speedup of INFORMATION_SCHEMA queries—but has also ensured that metadata operations are
ACID-compliant.4 The use of intrusive lists and arena-based memory management (mem_root) demonstrates a

commitment to low-level efficiency, ensuring that the "checkout" and "release" of table handles remain
operations.7

For the database professional, understanding these internal mechanisms is more than an academic exercise. It is the
key to effectively tuning table_open_cache, table_definition_cache, and table_open_cache_instances to maximize
throughput and minimize latency in production environments. As MySQL continues to evolve, the framework established
in version 8.0 will serve as the foundation for even greater advancements in concurrency and metadata management.

Works cited

1.​ MySQL 8.0: Improvements to Information_schema, accessed February 8, 2026,

[Link]
2.​ A Deep-Dive into MySQL: An Exploration of the MySQL Data Dictionary - Alibaba Cloud, accessed
February 8, 2026, [Link]
3.​ MySQL Table Cache Manager
4.​ Exploring MySQL 8 New Transaction Data Dictionary: Storing Information About Database Objects -
Percona, accessed February 8, 2026,
[Link]
abase-objects/
5.​ MySQL 8.0: Scaling and Performance of INFORMATION_SCHEMA, accessed February 8, 2026,
[Link]
6.​ MySQL: sql/table_cache.cc File Reference - MySQL :: Developer Zone, accessed February 8, 2026,
[Link]
7.​ MySQL: sql/table_cache.h Source File, accessed February 8, 2026,
[Link]
8.​ MySQL Bugs: #77715: table_open_cache_instances Does not Really Split table_open_cache, accessed
February 8, 2026, [Link]
9.​ sql/table.h Source File - MySQL :: Developer Zone, accessed February 8, 2026,
[Link]
10.​[Link] How MySQL Opens and Closes Tables, accessed February 8, 2026,
[Link]
11.​ Trigger updates - Percona Server for MySQL, accessed February 8, 2026,
[Link]
12.​Blog Archive » MySQL 8.0 DMR, new features, part 1 - [Link], accessed February 8, 2026,
[Link]