AMAPMySQL Memory Allocation

MySQL Memory Allocation

Compiled by: Rajorshi Sen , On: Nov 16, 2025
1. Overview of MySQL Memory Allocation Frameworks
2. SQL Layer Memory Management (MEM_ROOT)
Source Code Snippet: MEM_ROOT::AllocSlow
3. InnoDB Layer Allocation Managers
3.1 General Allocation (ut_allocator)
Source Code Snippet: ut_malloc Macro
3.2 Heap Allocation (mem_heap_t allocator)
Source Code Snippet: mem_block_info_t
4. Performance Schema and Memory Limiting (Tracing)
Source Code Snippets: my_malloc and Thd_mem_cnt_conn::alloc_cnt
4.1 Allocation Tracking Entry (my_malloc)
4.2 Counter Update and Limiting (Thd_mem_cnt_conn::alloc_cnt)
5. mem_heap_t allocator in mysql allocate memory
What it Allocates Memory For
How it Works: The "Bulk" Strategy
Monitoring Heap Memory
Why this matters for your previous tests
mem_heap_t allocator & ut_allocator for Data Dictionary
1. The Short-Term vs. Long-Term Allocation
2. Summary of the Relationship
3. Quick Reference for Allocators
6. Allocator for Data Dictionary
1. The Table Definition Cache (Server Layer)
2. The Data Dictionary Cache (InnoDB Layer)
Comparison Summary
Why the distinction matters
3. Monitoring Allocators
1. Enable Memory Instrumentation (If needed)
2. Monitor InnoDB Allocators (ut_allocator & mem_heap)
3. Monitor the Table Definition Cache (Server Layer)​
The Server-layer caches use different naming conventions. To see the memory used by the Table Definition
Cache and the Data Dictionary, use:
4. Finding the "Root Page" of an Index
Summary Checklist for Memory Analysis
TABLE object allocation in table_open_cache
How it Works
The Lifecycle of a TABLE Object
1. Enable the Memory Instrumentation
2. Query the Memory Usage
Understanding the Memory Layout
Why this matters for Sizing
The Hit Rate Formula
Step-by-Step Diagnostic Query
How to Adjust
table_definition_cache
1. Table Definition Cache (table_def_cache)
 2. Table Open Cache (table_open_cache)
The Workflow Relationship
Sizing Rules of Thumb
Pro-Tip: The "Wait" Metric
What it means: If this is high, your memory/sql/TABLE allocations are "thrashing"—constantly being destroyed and
recreated because the cache limit is too low.
2. The Performance Schema Wait Instrument
The Difference: TABLE vs. TABLE_SHARE
What specifically is in memory/sql/TABLE?
Which allocator is used?
Comparison Table
Checking table_open_cache Efficiency
Understanding the Lifecycle
Diagnosing Memory "Leaks"
Memory Allocator for /sql/memory/dd%
1. mem_root (The Data Dictionary Choice)
2. ut_allocator (The InnoDB Choice)
3. mem_heap_t allocator (The Internal Utility Choice)
Comparison Summary
Memory Comparison Query
Why this matters for Performance
How to Tune Based on the Results
One final check
mem_heap_alllocator VS mem_heap_t
1. The Distinction (Engine vs. SQL Layer)
2. Reason for mem_heap_allocator being used in the SQL Layer
3. Elaboration of the Use Cases
Points for Investigation
mem_heap_allocator and MEM_ROOT
1. The Relationship
2. Why this distinction matters
3. Comparison Summary
Global Comparison Query
Key Takeaways for Your Investigation
How to see the "Current" vs "Peak" distribution
Cross-Checking the Memory Layout
How to catch the "High Tide" next time
my_alloc, mem_heap_allocator & special allocator - Unclutter
1. MEM_ROOT: The "Storage Tank"
2. my_alloc & mem_heap_allocator: The "Pipes"
3. The "Execution Buffers": sort_buffer and join_buffer
Summary Table: Who uses what?
Why this explains your crash/spike
Data Dictionary & The Accessibility Scope
1. Global Cache vs. Thread Usage
2. Why it appears in main_mem_root
3. The Role of TABLE_SHARE::mem_root
Clarifying the Table
1. Global Cache vs. Thread Usage
 2. Where the mem_root actually lives
3. Summary of Ownership and Scope
1. The Table_cache_manager Class
2. Why the Data Dictionary is Global (but uses Session Memory)
3. Allocation and Accounting
4. Direct Answer: Is it part of the Thread?
MySQL Table_cache_manager
1. Structure of the Table_cache_manager
2. How Table Handles are Managed
3. Summary of Data Dictionary Logic
Conclusion for Documentation
Configuration Variables for the Table_cache_manager
Internal Relationship Summary
InnoDB mem_heap_t description
1. The Strategy: Block-Based Allocation
2. Primary Use Cases within InnoDB
3. The "All-or-Nothing" Lifecycle
4. Comparison to ut_allocator
Monitor InnoDB internal Heaps
Monitoring InnoDB Heaps
Understanding the Results
Comparison with Long-Lived Allocations
Distinction between Buffer Pool and Table Open Cache
What exactly is stored there?
Key Components:
The "Per-Thread" Nuance
Comparison at a Glance
How do you know if it's working?
UT (Utilities)
MySQL Memory Allocation and Management (Part I)​
MySQL Memory Allocation and Management (Part II)​
MySQL Memory Allocation and Management (Part III)
Allocation of memory for system variables:
read_rnd_buffer_size,sort_buffer_size,join_buffer_size,binlog_cache_size,thread_stack,tmp_table_size,net_buffer_length
1. The "Immediate" Allocations
2. The "On-Demand" (Query-Dependent) Allocations
3. The "Hybrid" Allocations
Summary Table
Why this matters
1. Set Operations (UNION)
2. Complex Grouping and Aggregates
3. Window Functions
4. Derived Tables and CTEs
5. Joins and Subqueries
Operations that skip Memory and go straight to Disk
How to check your usage
Memory Allocator for Buffers
Why mem_root?
Why it isn't the others:
How to see these in Performance Schema
 A Quick Warning on Memory Multipliers
Location of Buffers
1. Dedicated Allocations
2. Specialized MEM_ROOTs
3. The Hierarchy
Summary
Monitor the TRANSIENT vs. PERSISTENT memory usage of a specific Thread
1. Identifying Persistent vs. Transient Memory
2. The Monitoring Query
The Correct "Sort" Instruments
What about Join and Read Rnd Buffers?
Why the names are different
In prod-000:58c0 Sort Buffer & Join Cache
How to read your specific table
Is this a "Single Query" or "Total Server" peak?
Why Filesort_buffer::sort_keys is so high
Next Steps to Investigate
3. Understanding the Thread Memory Hierarchy
4. How to Spot "Memory Bloat"
Dictionary Cache , Table Open Cache and Table Definition Cache
Relationship Overview
Detailed Comparison
Key Nuance in MySQL 8.0+
Summary
Details of Dictionary Cache
1. What are the "Hidden Tables"?
2. The "Internal SQL Lookup" Problem
3. How the Dictionary Cache Alleviates This
Summary of the Flow
MySQL 8 Improvement on Legacy Dictionary Cache
1. The "Hidden" Tables
2. What is an "Internal SQL Lookup"?
3. How the Dictionary Cache Stops This
How to Check Your Cache Performance
Configuration Tips
How to Interpret the Results
1. The Table Definition Cache (table_definition_cache)
2. The Table Open Cache (table_open_cache)
A Quick Note on "Hidden" Performance
Query: What's in my Definition Cache?
Understanding the Cache "Eviction"
Summary Checklist for a Healthy Cache
Difference between a BluePrint in the table_definition_cache and the file being opened in table_open_cache
1. The Blueprint: table_definition_cache
2. The Active File: table_open_cache
Comparison at a Glance
How they work together (The "Library" Analogy)
Summary
Estimation of Table Definition & Table Open Cache
 1. Estimating Table Definition Cache Memory
2. Estimating Table Open Cache Memory
3. The SQL Query to See Real-Time Usage
When to Adjust Based on Memory
Summary Checklist
Operation System Open file limits & Open tables
1. Check the Current Limits
2. The Golden Rule of Sizing
3. How to increase the limit
Summary of the "Cache Chain"
Health Check Scripts
MySQL Cache Health Monitor
How to read this report:
Final Pro-Tip: The "Wait" Factor
Significance of opened_table_definition status variable
Why does this number increase?
Is a high number bad?
The "Cache Thrashing" Warning
How to diagnose
1. The Variable You Are Looking For: Dictionary memory allocated
2. Performance Schema: The "Real" Miss Counter
3. Visualizing the "Double Miss"
4. How to detect the "Internal Query" specifically
Summary for your Troubleshooting:
Concurrent DB creation and drop - Out Of Memory Kill
Why CREATE/DROP DATABASE Hogs MEM_ROOT
1. Table Share (TABLE_SHARE) Objects
2. The Dictionary Client and DD Objects
3. Event and Procedure Caches
The "Zombie" Memory Effect
How to Verify and Mitigate
Recommendations for your Pipeline:
Memory Starvation Situation & Memory Component Analysis
Memory Breakdown
Analysis & Observations
The Mapping Query
Instrumentation Breakdown
Requirements for this data
my_alloc memory allocator
1. Its Identity: The General Purpose Allocator
2. Layer Placement
Why it exists in the Server Layer
How it differs from InnoDB's Memory
1. The Global Summary Query
2. The sys Schema "Quick View"
What these results tell you
Important: Why you might see "0"
Top-Level Grouping of Instruments with Not Null Documentation
1. The "Integrity" Group: wait/synch
 2. The "Interface" Group: statement/sql & statement/abstract
3. The "Self-Monitoring" Group: memory/performance_schema
4. The "Big Three" Memory Consumers:
The "Documentation" Strategy
Top-Level Grouping of Instruments with Null Documentation
1. The wait/ Category
2. The stage/ Category
3. The statement/ Category
4. The memory/ Category
Why this is useful
Usual Causes of Spike in Data Dictionary Memory
1. Mass Parallel Opening of Unique Tables
2. Large Scale ALTER TABLE Operations
3. Deeply Nested Views and Stored Procedures
4. Excessive Temporary Table Creation
5. High Connection Concurrency (Handle Bloat)
Summary of Memory Targets
References

1. Overview of MySQL Memory Allocation Frameworks

MySQL uses multiple custom memory managers tailored for performance, efficiency, and debuggability (traceability via
Performance Schema). The three main allocation strategies:
Framework Layer Type Key Purpose

MEM_ROOT SQL Layer Arena Manages memory for

(Server) Allocator 1.​ statement parsing,
2.​ query execution plans, and
 3.​ thread context (THD).

ut_allocator InnoDB Layer General Wraps standard C/C++ memory

Allocator calls (new, malloc) for basic
InnoDB structures.

mem_heap_t InnoDB Layer Heap/Block Combines small memory requests

allocator Allocator into larger blocks (mem_block_t)
to reduce malloc/free
overhead.

All memory requests eventually pass through the Performance Schema (PSI) layer for tracking and limiting .

2. SQL Layer Memory Management (MEM_ROOT)

The MEM_ROOT structure implements an arena allocator (or memory pool) widely used in the MySQL Server layer,
particularly associated with the THD (Thread Handle) structure to manage memory related to a single connection or
statement lifecycle.
Source Code Snippet: MEM_ROOT::AllocSlow
C
None
void *MEM_ROOT::AllocSlow(size_t length) {
// The memory applied is very large or an exclusive memory is required.
if (length >= m_block_size || MEM_ROOT_SINGLE_CHUNKS) {
Block *new_block = AllocBlock(/*wanted_length=*/length,
/*minimum_length=*/length);
if (new_block == nullptr) return nullptr;

// ... insertion logic for large/exclusive blocks ...

} else {
// ... logic for appending a new block ...
}
// ... update m_current_block, m_current_free_start, etc.
}

Explanation:
●​ Function: AllocSlow() is called by MEM_ROOT::Alloc() when the currently managed block
(m_current_block) does not have enough free space to satisfy the request of size length.
●​ Arena Concept: MEM_ROOT stores memory in a linked list of contiguous blocks (Block). When a request comes
in, it's allocated linearly from the current block's free space. This is very fast (pointer increment).
●​ Large Allocation Handling: The if condition handles cases where the requested length is very large (>=
m_block_size) or when the memory needs to be exclusive (MEM_ROOT_SINGLE_CHUNKS). In these cases, the
new, large block is typically not set as the new m_current_block to prevent the large block size from interfering
with the sizing of subsequent, smaller blocks, thus preserving memory control predictability.
●​ m_current_block: Tracks the block currently being used for small, incremental allocations.

3. InnoDB Layer Allocation Managers

3.1 General Allocation (ut_allocator)
ut_allocator acts as the primary wrapper for standard memory operations within InnoDB. When the Performance
Schema tracing is enabled (UNIV_PFS_MEMORY), this layer is crucial for adding the required tracing metadata.
Source Code Snippet: ut_malloc Macro
C
 None
#ifdef UNIV_PFS_MEMORY
#define ut_malloc(n_bytes, key) static_cast<void
*>(ut_allocator<byte>(key).allocate(n_bytes, ...))
// ...
#else /* UNIV_PFS_MEMORY */
#define ut_malloc(n_bytes, key) ::malloc(n_bytes)
#endif

Explanation:
●​ Conditional Compilation: The logic is split based on the UNIV_PFS_MEMORY flag. This shows how MySQL
separates the core allocation logic from the instrumentation/tracing logic.
●​ Instrumentation: When enabled, the macro routes the call through ut_allocator::allocate(), which
allocates an extra piece of metadata, ut_new_pfx_t, before the actual requested memory. This metadata stores
the key (Performance Schema memory key) and the size for subsequent tracking and analysis.
●​ Purpose: By encapsulating malloc/new, ut_allocator ensures that memory allocated for various InnoDB
components (like the Log Buffer, or internal structures) is traceable in the Performance Schema tables.
3.2 Heap Allocation (mem_heap_t allocator)
The mem_heap_t allocator (and its underlying structure mem_heap_t) is a secondary allocation layer within InnoDB
designed to manage memory more efficiently for structures that have a long but deterministic lifespan, such as those used
during indexing or system startup.
Source Code Snippet: mem_block_info_t
C
None
struct mem_block_info_t {
// ...
ulint len; /*!< The size of the current block. */
ulint total_size; /*!< The total size of all blocks. */
ulint type; /*!< The allocation type. */
void *free_block; /* Pointer to memory for MEM_HEAP_BTR_SEARCH type */
void *buf_block; /* Pointer to a buf_block_t if memory is from Buffer Pool */
};

Explanation:
●​ Structure: mem_heap_t is essentially a linked list of mem_block_info_t structures. It functions similarly to
MEM_ROOT by aggregating small allocations into larger blocks to minimize system call overhead.
●​ Allocation Types (type): The type field is critical as it dictates the memory source:
○​ MEM_HEAP_DYNAMIC: Uses ut_allocator (which may call malloc).
○​ MEM_HEAP_BUFFER: Obtains memory directly from the InnoDB Buffer Pool (buf_block_alloc).
○​ MEM_HEAP_BTR_SEARCH: Used for memory related to B-tree (index) search activities, often leveraging
memory from a special free_block.
●​ Lifetime: This allocator is used for session-long or transaction-long data. A key feature is that it does not support
individual free() operations (deallocate is a null operation). The memory is released all at once when the
entire heap is destroyed, avoiding fragmentation caused by small, scattered deallocations.

4. Performance Schema and Memory Limiting (Tracing)

In MySQL 8.0.28+, a new feature introduced connection and global memory limits, which relies on integrating memory
requests with the Performance Schema (PSI) tracing layer.
Source Code Snippets: my_malloc and Thd_mem_cnt_conn::alloc_cnt
4.1 Allocation Tracking Entry (my_malloc)
C
 None
void *my_malloc(PSI_memory_key key, size_t size, myf flags) {
// ... allocate memory header (my_memory_header *mh) ...
// Call the service.
mh->m_key = PSI_MEMORY_CALL(memory_alloc)(key, raw_size, &mh->m_owner);
// ...
return user_ptr;
}

Explanation:
●​ my_malloc: This is the SQL layer's equivalent of malloc (used for memory not managed by MEM_ROOT). It
prepends a header (my_memory_header) that contains the PSI_memory_key.
●​ PSI_MEMORY_CALL: This macro entry point is the gateway to the Performance Schema instrumentation
(pfs_memory_alloc_vc), which is where the memory counter is updated and checked against limits.
4.2 Counter Update and Limiting (Thd_mem_cnt_conn::alloc_cnt)
C
None
bool Thd_mem_cnt_conn::alloc_cnt(size_t size) {
mem_counter += size;
max_conn_mem = std::max(max_conn_mem, mem_counter);

// Check connection limit

if (mem_counter > m_thd->variables.conn_mem_limit) {
(void)generate_error(ER_DA_CONN_LIMIT, ...);
}

// Check global limit (lazily)

if ((curr_mode & MEM_CNT_UPDATE_GLOBAL_COUNTER) && max_conn_mem > glob_mem_counter)
{
// ... calculate delta based on connection_memory_chunk_size ...
// Update global counter using global lock (LOCK_global_conn_mem_limit)
// ...
// Check global limit and generate error if exceeded
}
return true;
}

Explanation:
●​ Per-Connection Counter: The function immediately updates the connection's mem_counter. If this exceeds the
conn_mem_limit, an error is generated to kill the connection.
●​ Lazy Global Update: Instead of updating the global_conn_mem_counter on every allocation, the update is
lazy and chunked. It only happens when max_conn_mem exceeds the memory previously allocated to the global
counter (glob_mem_counter). The update uses a size rounded up to connection_memory_chunk_size to
minimize the frequency of acquiring the expensive LOCK_global_conn_mem_limit mutex, significantly
improving concurrency.
●​ Mode Check: The curr_mode check ensures that memory tracking and limiting logic are only applied if enabled
for the user (e.g., SUPER users often skip the limit checks).
5. mem_heap_t allocator in mysql allocate memory
In MySQL (specifically within the InnoDB storage engine), the mem_heap_t allocator is used to manage memory for
internal InnoDB memory heaps.

Instead of calling the standard system malloc() for every small object—which is computationally expensive and causes
memory fragmentation—InnoDB creates "heaps" (memory pools). The mem_heap_t allocator handles the allocation of
memory chunks from these pools for short-lived tasks.

What it Allocates Memory For

The heap allocator is primarily used for metadata and temporary structures that exist only for the duration of a specific
operation or transaction.
●​ Lock Information: When you execute SELECT ... FOR UPDATE, InnoDB must create lock structures. The
memory for these lock_t objects is allocated from a heap.
●​ Transaction Objects: Structures tracking active transactions (trx_t) and their associated undo logs.
●​ Query Parsing and Execution: Temporary structures used during the parsing of a SQL statement or the building
of an execution plan within the engine.
●​ Internal Buffers: Small, temporary buffers used for record conversion, sorting tiny datasets, or handling dynamic
arrays within the engine's internal logic.
●​ Dictionary Cache: Metadata about tables and indexes (the "Data Dictionary") as they are loaded into memory.

How it Works: The "Bulk" Strategy

The core philosophy of the InnoDB heap allocator is speed through simplicity:
1.​ Block Allocation: It requests a large block of memory from the system (e.g., 8KB or 16KB).
2.​ Pointer Bumping: When the engine needs 64 bytes, it simply moves a pointer forward within that block. This is
much faster than a system call.
3.​ No Individual Freeing: You cannot "free" a single object within a heap.
4.​ Bulk Release: The entire heap is destroyed (freed) all at once when the task is finished (e.g., when a transaction
commits or a statement finishes).

Monitoring Heap Memory

If you see high memory usage related to heaps in your SHOW ENGINE INNODB STATUS, it is often tied to:
●​ Large Transactions: A transaction involving millions of row locks will consume significant heap memory.
●​ Complex Schemas: Hundreds of open table handles and metadata objects.

You can view the current heap memory usage in the Performance Schema:

SQL

None
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%mem_heap%';

Why this matters for some previous tests

When you run the SELECT ... FOR UPDATE tests, InnoDB used a mem_heap_t allocator to create the Lock
Structures you saw in the performance_schema.data_locks table. If you had thousands of such locks, you
would see the "heap size" increment in the TRANSACTIONS section of the InnoDB status:

2 lock struct(s), heap size 1136, 1 row lock(s)

The 1136 bytes shown there are exactly what the mem_heap_t allocator managed for that specific
transaction's locking needs.

memory management in MySQL and InnoDB is layered and different allocators are used for different lifecycle stages of the
same object.

mem_heap_t allocator & ut_allocator for Data Dictionary

Both statements are correct, but they refer to different levels of the memory hierarchy:

1. The Short-Term vs. Long-Term Allocation

The key to resolving this is the lifespan of the data being stored:
●​ mem_heap_t allocator (Short-Lived): As mentioned in the earlier response, this is used when the Data
Dictionary metadata is being loaded or processed. For example, during query parsing or the start of a transaction,
InnoDB creates a "heap" to quickly grab the metadata it needs. This memory is batch-allocated and batch-freed
once the specific operation is done.
●​ ut_allocator (Persistent/Global): The Data Dictionary Cache itself is a long-term, global structure. Once those
table objects are loaded, they need to stay in memory to be shared across many different threads and
transactions. For these persistent, long-term allocations, InnoDB uses ut_allocator.

2. Summary of the Relationship

Think of it like a construction project:
●​ mem_heap_t allocator is like a temporary bucket used to carry bricks (metadata) to a site for a specific task. Once
the task is done, the bucket is emptied.
●​ ut_allocator is the permanent foundation where the bricks are eventually placed to stay for the long term.

3. Quick Reference for Allocators

Component Primary Allocator Why?

Table Definition Cache my_malloc Global Server-layer cache; stays until the table is closed.
Data Dictionary Cache ut_allocator Global InnoDB-layer cache; persistent across many queries.
Metadata
Loading/Parsing mem_heap_t allocator Temporary "workspace" memory; freed after the operation.
Query Execution MEM_ROOT Server-layer per-thread memory; freed when the query ends.

In short: mem_heap_t allocator handles the "work in progress," while ut_allocator handles the "permanent residency" of
the Data Dictionary objects in InnoDB.
6. Allocator for Data Dictionary
In MySQL (specifically focusing on InnoDB and the Server Layer), memory management is layered. The table definition
cache or the Data Dictionary are managed via specialized memory structures designed for long-term storage and quick
lookups.

1. The Table Definition Cache (Server Layer)

The Table Definition Cache (TDC) stores .frm file data (or its equivalent in 8.0+) and TABLE_SHARE objects.
●​ Allocator: It primarily uses the C++ std::map or hash structures, and the memory for individual objects is typically
allocated via my_malloc.
●​ Is it MEM_ROOT? While the TDC itself isn't a MEM_ROOT, individual TABLE objects often use a MEM_ROOT for
short-term allocations during the execution of a specific query. However, the cached shareable definition is meant
to persist, so it uses more permanent heap memory.

2. The Data Dictionary Cache (InnoDB Layer)

Since MySQL 8.0, the Data Dictionary is much more integrated. InnoDB manages its own internal representation of tables.
●​ Allocator: InnoDB uses its own memory pool systems.
○​ ut_allocator: This is InnoDB’s wrapper for system-level allocations (like malloc or mmap). It is used for
larger, more permanent structures.
○​ Mem_heap_t allocator: This is used for "heaps" (short-lived memory batches).
●​ The Data Dictionary Cache (the in-memory objects representing tables, indexes, and columns) generally uses
ut_allocator for the persistent object allocations. It does not use MEM_ROOT, as MEM_ROOT is a Server-layer
construct, not an InnoDB-layer one.

Comparison Summary

Cache Component Primary Allocator Scope

Table Definition Cache my_malloc / std::allocator Server Layer (Global)

Data Dictionary Cache ut_allocator InnoDB Layer (Engine)
Query Execution MEM_ROOT Thread/Session (Temporary)
​
Note: MEM_ROOT is specifically designed for "allocate many, free all at once" patterns, which is perfect for parsing
a single SQL statement but inefficient for a cache where individual items (like one specific table definition) need to be
evicted while others stay.

Why the distinction matters

If you are seeing high memory usage:
1.​ MEM_ROOT spikes usually indicate complex queries or many concurrent connections.
2.​ ut_allocator spikes in the context of the Data Dictionary usually indicate a very large number of tables or partitions
being accessed.
3. Monitoring Allocators
To monitor these allocators, you need to use the memory_summary_global_by_event_name table in the
performance_schema. Note that some instruments might be disabled by default to save overhead, so we will check that
first.

1. Enable Memory Instrumentation (If needed)

Before you can see detailed data for ut_allocator or mem_heap_t allocator, ensure the instruments are enabled:

SQL

None
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'memory/innodb/%';

2. Monitor InnoDB Allocators (ut_allocator & mem_heap)

This query filters for the specific InnoDB memory structures we discussed.
●​ ut_allocator typically shows up under general InnoDB memory usage or specific long-term blocks.
●​ mem_heap is explicitly tracked as the engine's internal heap system.

SQL

None
SELECT
EVENT_NAME,
COUNT_ALLOC,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS CURRENT_MB,
HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024 AS PEAK_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb/ut0mem'
OR EVENT_NAME LIKE 'memory/innodb/mem0mem';

●​ mem0mem: This correlates to the mem_heap_t allocator (short-term tasks).

●​ ut0mem: This correlates to the ut_allocator (persistent allocations).

3. Monitor the Table Definition Cache (Server Layer)​

The Server-layer caches use different naming conventions. To see the memory used by the Table Definition
Cache and the Data Dictionary, use:
SQL

None
SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS CURRENT_MB
 FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/sql/TABLE_SHARE'
OR EVENT_NAME LIKE 'memory/sql/dd::%';

●​ memory/sql/TABLE_SHARE: The Table Definition Cache objects.

●​ memory/sql/dd::...: The new C++ Data Dictionary objects introduced in MySQL 8.0.

4. Finding the "Root Page" of an Index

Earlier, I mentioned that the Root Page of an index is usually Page 3, but for secondary indexes, it varies. You can find
exactly where your index starts (to know which 16KB block to hexdump) by querying the information schema:

SQL

None

SELECT
NAME AS TABLE_NAME,
INDEX_ID,
PAGE_NO
FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE NAME LIKE '%your_index_name%';
Take the PAGE_NO and multiply it by 16,384 to find your hexdump -s starting point.

Summary Checklist for Memory Analysis

If you see growth in... It likely means...

memory/innodb/mem0mem High volume of concurrent transactions or complex locking.
You have too many open tables (increase
memory/sql/TABLE_SHARE table_open_cache).
Large number of metadata objects (tables/columns) being
memory/sql/dd:: held in memory.
This query will show you which active connections are consuming the most memory in their private MEM_ROOT (the
memory/sql/THD::main_mem_root instrument).
SELECT
t.THREAD_ID,
t.PROCESSLIST_ID,
t.PROCESSLIST_USER,
t.PROCESSLIST_INFO AS CURRENT_QUERY,
m.EVENT_NAME,
m.CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS CURRENT_MB_USED
FROM performance_schema.threads t
JOIN performance_schema.memory_summary_by_thread_by_event_name m
ON t.THREAD_ID = m.THREAD_ID
WHERE m.EVENT_NAME = 'memory/sql/THD::main_mem_root'
ORDER BY CURRENT_MB_USED DESC;
5. Growth of MEM_ROOT per thread

The main_mem_root is a "group allocator." Instead of freeing memory for every single string or object
used during a query, MySQL keeps it all in this MEM_ROOT and frees it only when the query finishes or
the connection closes.

If a thread has a high CURRENT_MB_USED:

●​ Massive IN() clauses: Large sets of values in a query consume significant MEM_ROOT during
parsing.
●​ Complex Joins/Sorting: Heavy optimization paths require temporary memory structures.
●​ Prepared Statements: If many are opened and not closed, they can linger in the thread's
memory.

To keep your allocators straight, here is how they relate to the physical storage and the server layers:​

Level Component Primary Allocator

Connection Per-thread buffers MEM_ROOT
Table Definition my_malloc (primary implementation of memory allocation
Server Global Cache by MEM_ROOT)
Data Dictionary
InnoDB Global Cache ut_allocator
InnoDB Temp Row/Lock processing mem_heap_allocator

TABLE object allocation in table_open_cache

To assign memory for a TABLE object in the table_open_cache, the correct allocator is 1. MEM_ROOT.

How it Works
In MySQL’s internal architecture, MEM_ROOT is a specialized memory pool allocator designed for efficiency. It allocates
memory in large chunks and then doles out smaller pieces for specific objects.
●​ MEM_ROOT: This is the primary mechanism for objects like TABLE and TABLE_SHARE (actually TABLE_SHARE
are what table_definitiion_cache holds and those are allocated by my_malloc which is the primary
memory allocation implementation by MEM_ROOT). It allows the system to allocate all the memory needed for a
table's metadata and structure at once and, crucially, free it all at once when the table is closed or flushed from the
cache. This prevents memory fragmentation.
●​ ut_allocator: This is specific to the InnoDB storage engine. While it handles memory for internal InnoDB structures
(like the buffer pool or redo logs), it is not the allocator for the high-level TABLE objects in the server-level cache.
●​ mem_heap_allocator: This is also largely an InnoDB-specific construct used for short-lived memory heaps within
the storage engine.
The Lifecycle of a TABLE Object
When a query requires a table that isn't currently in the cache:
1.​ MySQL looks for a TABLE_SHARE (it is what resides in table_definition_cache also managed via MEM_ROOT’s
my_alloc).
2.​ If it needs to instantiate a new TABLE instance, it uses a MEM_ROOT associated with that table to allocate the
necessary memory.
3.​ The object is then placed into the table_open_cache for reuse by other threads.

Note: Modern versions of MySQL (8.0+) have further refined how the Data Dictionary and Table Cache interact,
but MEM_ROOT remains the foundational "arena" allocator for these objects.

To monitor the memory overhead of your table_open_cache and see how MEM_ROOT is performing in the real world, you
can dive into the Performance Schema.

Since TABLE objects are allocated within the server layer, you want to track the memory instruments specifically tied to the
table definition and the cache.

1. Enable the Memory Instrumentation

By default, some memory instruments are disabled to save overhead. You can ensure they are active by running:

SQL

None
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'memory/sql/TABLE';

2. Query the Memory Usage

You can see exactly how much memory is being held by these objects using this query. This will show you the current
number of bytes allocated via MEM_ROOT for table objects:

SQL

None
SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_MB,
HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024 AS peak_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/sql/TABLE%';

Understanding the Memory Layout

When you run the query above, you are essentially looking at the "footprint" of your table_open_cache.
●​ memory/sql/TABLE: Memory used for the TABLE structure itself.
●​ memory/sql/TABLE_SHARE: This is shared memory across all threads for a specific table's definition. This is also
managed via MEM_ROOT and is often where the bulk of metadata memory lives.
Why this matters for Sizing
If you see that peak_MB is significantly higher than current_MB, it suggests your table_open_cache might be fluctuating or
that FLUSH TABLES was recently run. If current_MB is creeping toward your available system RAM, it’s a sign that your
table_open_cache is set too high for your hardware.

To determine if your table_open_cache is actually sized correctly, you shouldn't just look at memory—you should look at
efficiency. If your hit rate is high, your current memory allocation is doing its job; if it's low, you're constantly hitting the disk
for metadata.

The Hit Rate Formula

You can calculate the efficiency of your cache using two global status variables: Opened_tables (the misses) and
Open_tables (the current occupancy).

A common way to view the "Miss Rate" (how often MySQL has to open a file from scratch) is:

●​ Low Miss Ratio: (e.g., < 1/sec) Your cache is performing well.
●​ High Miss Ratio: Your cache is too small; MySQL is constantly closing tables to make room for new ones.

Step-by-Step Diagnostic Query

Run this query to get a snapshot of your cache performance:

SQL

None
SHOW GLOBAL STATUS LIKE 'Open%_tables';

What the results mean:

| Variable | Description |

| :--- | :--- |

| Open_tables | The number of tables currently residing in the cache. |

| Opened_tables | The total number of times a table has been opened since the server started. |

The Red Flag: If Open_tables is equal to your table_open_cache setting AND Opened_tables is increasing
rapidly, you have a bottleneck. You are "thrashing" the cache.

How to Adjust
If you find you need to increase the size, you can do it dynamically without restarting the server:

SQL
 None
GLOBAL table_open_cache = 4000;
-- Remember to also check/increase open_files_limit in your config file!

Would you like me to explain how the Table Definition Cache (table_def_cache) differs from the Table Open Cache, as
they are often confused during tuning?

table_definition_cache
It is a common pitfall! While they sound identical, they serve two distinct stages of "opening" a table. Think of it as the
difference between a blueprinter's archive and the actual tools on a workbench.

1. Table Definition Cache (table_def_cache)

This is the Archive. It stores the .frm file data (or the Data Dictionary info in 8.0+) in memory.
●​ What it stores: The table structure, column types, and index definitions.
●​ Sharing: It is global. Only one entry exists per table, regardless of how many users are accessing it.
●​ Memory: Generally very small footprint.

2. Table Open Cache (table_open_cache)

These are the Workbenches. This cache stores the actual file handles and state for performing queries.
●​ What it stores: The specific "instance" of a table used by a thread.
●​ Sharing: It is not shared between concurrent sessions. If 10 users are all querying Table_A at the exact same
time, you need 10 entries in the table_open_cache, but only 1 entry in the table_def_cache.
●​ Memory: Higher overhead because it scales with concurrent activity.

The Workflow Relationship

When a query hits the server, MySQL follows this logic:
1.​ Check Definition Cache: "Do I know what this table looks like?" (If not, read from disk).
2.​ Check Open Cache: "Do I have an available handle/instance of this table for this thread?" (If not, instantiate one).
3.​ Execute: Run the query.

Sizing Rules of Thumb

Cache Type Recommended Sizing

Should be large enough to hold all tables in your database (e.g., if you have 2,000 tables, set this to
table_def_cache 2,000).
Needs to be table_def_cache X max_connections (roughly), or at least large enough to handle your
table_open_cache peak concurrent thread volume.

Pro-Tip: The "Wait" Metric

If you want to see if your table_open_cache is causing actual performance lag, check for "contention" (threads waiting for a
slot in the cache):

SQL
 None
SHOW GLOBAL STATUS LIKE 'Table_open_cache_overflows';

What it means: If this is high, your memory/sql/TABLE allocations are "thrashing"—constantly being destroyed and
recreated because the cache limit is too low.

2. The Performance Schema Wait Instrument

If you want to see actual time lost waiting for the cache (the "standing in line" part), you have to look at the Performance
Schema mutex waits. This is where the real "lag" shows up:

SQL

None
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS Wait_MS
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%table_open_cache_manager%';

To see if the threads were actually fighting over the cache:

SQL

None
SELECT
EVENT_NAME,
COUNT_STAR,
ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS Wait_MS
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME = 'wait/synch/mutex/sql/LOCK_table_cache';

The Difference: TABLE vs. TABLE_SHARE

In MySQL's internal architecture, a table is managed in two distinct parts:
1.​ memory/sql/TABLE_SHARE::mem_root: This tracks the memory for the "blueprint" of the table (the
TABLE_SHARE object). It’s loaded once and shared across all sessions.
2.​ memory/sql/TABLE: This tracks memory for individual TABLE descriptors (or "handles").

Every time a session (thread) opens a table, it needs its own TABLE structure to maintain its specific state—such as its
current position in a scan, record buffers, and locks. If you have 50 concurrent threads querying the same table users,
you will have one TABLE_SHARE but 50 TABLE descriptors.

What specifically is in memory/sql/TABLE?

This event name records the allocation of the TABLE class instances. Specifically, it tracks:
●​ The Handle Object: The memory for the TABLE structure itself.
 ●​ Query-Specific State: Pointers to the table's fields, record buffers (where the data for the "current" row is
kept), and index information specific to that thread's usage.
●​ Field Array: The array of pointers to the columns used by that specific handle.

Which allocator is used?

For the memory/sql/TABLE event, MySQL typically uses the system allocator (e.g., malloc) via the my_malloc wrapper,
but it is often managed within a MEM_ROOT.
●​ The Wrapper: MySQL uses my_malloc (which maps to jemalloc or glibc malloc) to request memory from the OS.
●​ The Management: The memory/sql/TABLE instrument specifically flags memory that is allocated for the life of
that table handle.

When you see a spike in memory/sql/TABLE, it usually means your Table Open Cache is growing or you have a massive
number of concurrent threads opening many unique tables simultaneously.

Comparison Table

Event Name Frequency What it represents

memory/sql/TABLE_SHARE Once per unique table The "Static" metadata (columns, constraints).
The "Active" handle (row buffers, cursor
memory/sql/TABLE Once per session, per table state).

To see how efficiently your system is managing those TABLE handles, we need to look at the relationship between your
Table Open Cache hits and the actual memory allocations.

Checking table_open_cache Efficiency

If memory/sql/TABLE is consuming a lot of RAM, it’s often because the cache is large, or because tables are being
opened and closed so frequently that the allocator is constantly working.

You can run this query to see the "Hit Ratio" of your table cache:

SQL

None
SELECT
VARIABLE_VALUE AS Open_tables,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Opened_tables') AS Opened_tables,
ROUND(VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Opened_tables'), 2) AS Cache_Hit_Ratio
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Open_tables';

●​ Open_tables: How many TABLE handles are currently in memory (tracked by memory/sql/TABLE).
●​ Opened_tables: The cumulative count of how many times a table has been opened since the server started.
 ●​ Interpretation: If Opened_tables is growing rapidly while Open_tables stays at your limit, your cache is too small,
causing "thrashing" where memory is constantly allocated and freed.

Understanding the Lifecycle

The lifecycle of these two instruments explains why you see different memory patterns:
1.​ First Request: MySQL allocates memory for TABLE_SHARE (blueprint) and one TABLE handle.
2.​ Subsequent Requests (different threads): MySQL reuses the TABLE_SHARE but allocates a new
memory/sql/TABLE handle for every concurrent thread.
3.​ Closing: When a thread finishes, the TABLE handle goes into the Table Open Cache. It isn't "freed"
immediately; the memory remains allocated (and tracked) so the next thread can grab it instantly.

Diagnosing Memory "Leaks"

If you suspect memory/sql/TABLE is leaking, it is rarely a bug and usually a configuration issue. Check these two
variables:
●​ table_open_cache: Limits how many TABLE handles stay in memory.
●​ table_definition_cache: Limits how many TABLE_SHARE objects stay in memory.

Memory Allocator for /sql/memory/dd%

In the context of MySQL's Data Dictionary (/sql/ddmemory%), the correct allocator is mem_root.

Here is the breakdown of how these three allocators are used and why mem_root is the answer for your specific event
name:

1. mem_root (The Data Dictionary Choice)

The Data Dictionary (DD) in MySQL 8.0+ is designed to manage complex object graphs (tables, columns, indexes).
●​ Mechanism: It uses a Memory Root (MEM_ROOT) to group allocations. This allows the server to allocate many
small objects (like the strings and structures that define your columns or indexes) and then free the entire "root"
at once when the transaction or object is closed.
●​ P_S Mapping: Most instruments starting with memory/sql/dd/ (such as memory/sql/dd::objects or
memory/sql/dd::infrastructure) track memory managed via a MEM_ROOT dedicated to the Data Dictionary.

2. ut_allocator (The InnoDB Choice)

This is an InnoDB-specific allocator wrapper.
●​ Mechanism: It is used primarily by the InnoDB storage engine (the ut stands for "utilities").
●​ Usage: You will see this for InnoDB-level events like memory/innodb/ut_allocator_thread or for the Buffer Pool. It
is not used for the SQL-layer Data Dictionary handles represented by memory/sql/dd%.

3. mem_heap_t allocator (The Internal Utility Choice)

This is an internal C++ allocator often used by the STL containers (like std::vector or std::map) within the MySQL source
code.
●​ Mechanism: It bridges the gap between MySQL’s memory tracking and standard C++ containers.
●​ Usage: While the Data Dictionary uses STL containers internally, the top-level instrumentation you see in
Performance Schema as memory/sql/dd% is specifically tied to the lifetime of the MEM_ROOT structures that
hold the DD objects.

Comparison Summary

Allocator Layer Primary Use Case

mem_root SQL / DD memory/sql/dd%, memory/sql/TABLE
ut_allocator InnoDB Buffer Pool, InnoDB internal structures.
mem_heap_t allocator Core / C++ Internal STL containers and temporary heaps.

To compare how your server is balancing memory between the Data Dictionary (memory/sql/dd%) and the Table
Cache (memory/sql/TABLE), you can run the following query.

Memory Comparison Query

This query aggregates the current memory usage for these two specific areas in Megabytes (MB):

SQL

None
SELECT
SUBSTRING_INDEX(EVENT_NAME, '/', 3) AS Category,
COUNT_ALLOC AS Total_Allocations,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Current_MB_Used,
ROUND(HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Peak_MB_Used
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/sql/dd%'
OR EVENT_NAME = 'memory/sql/TABLE'
ORDER BY Current_MB_Used DESC;

Why this matters for Performance

●​ memory/sql/dd% (The Library): This is the memory used by the Data Dictionary Object Cache. It stores the
definitions of tables, schemas, and stored programs. In MySQL 8.0+, this is highly transactional and efficient. If
this is high, you likely have a very large number of tables or complex schemas.
●​ memory/sql/TABLE (The Desk): This is the Table Open Cache. It tracks the active "handles" being used by
your current connections.

Important: If memory/sql/TABLE is significantly higher than your Data Dictionary memory, it indicates that
many different threads are opening the same tables simultaneously, each requiring its own handle.
How to Tune Based on the Results
1.​ If dd memory is growing indefinitely: You may need to look at your table_definition_cache. This variable limits
the number of table definitions stored in the DD cache.
2.​ If TABLE memory is high: You are likely hitting your table_open_cache limit. If the Opened_tables status variable
is also increasing rapidly, your cache is too small, and MySQL is constantly "thrashing" (destroying and
recreating these objects).

One final check

To see if your cache is actually keeping up with the demand, you can compare the number of handles in memory vs. the
total number of times tables have been opened:

SQL

None
SHOW STATUS LIKE 'Open%tables';

●​ Open_tables: The number of handles currently in the TABLE cache.

●​ Opened_tables: The total count of opens. If this number is huge and growing, your memory is being wasted on
the overhead of opening/closing tables instead of just holding them in the cache.

mem_heap_alllocator VS mem_heap_t
Layering in MySQL memory management can get blurry.

mem_heap_t is an internal InnoDB structure, but the mem_heap_allocator that can be seen in the Performance
Schema is the C++ SQL-layer bridge.

1. The Distinction (Engine vs. SQL Layer)

There are two "Heaps" often discussed in MySQL source code that sound identical but belong to different layers:
●​ InnoDB mem_heap_t (Storage Engine Layer): This is a C-based memory pool used internally by InnoDB for
things like record structures and B-tree operations.
●​ mem_heap_allocator (SQL Layer / Core): This is a C++ Allocator class used by the MySQL Server (the SQL
Layer) to allow modern C++ containers (like std::vector or std::map) to use MySQL's internal MEM_ROOT
management.

2. Reason for mem_heap_allocator being used in the SQL Layer

The SQL Layer (the "Brain") is responsible for query parsing, optimization, and the Data Dictionary. Because these
components are now written in C++, they need a way to use MySQL's memory tracking.
●​ Standard Library Compatibility: It allows the SQL layer to use STL containers while still being able to track
that memory under a specific Performance Schema instrument.
●​ Unpredictable Growth: During query optimization, the SQL layer doesn't know how many join paths it will
evaluate. It uses STL containers (backed by mem_heap_allocator) to grow that memory dynamically.
●​ Data Dictionary: When you query a table, the SQL layer loads the table's "blueprint" into C++ objects. These
objects are allocated using mem_heap_allocator so they can be freed all at once when the table is closed.
3. Elaboration of the Use Cases

Feature Role of mem_heap_allocator

STL Containers Provides the memory backing for std::vector, std::map, and std::string used in the SQL layer.
Manages short-lived memory "arenas" for complex query transformations and Window
Temporary Heaps Functions.
Handles the "unpredictable" expansion of memory when a query has thousands of items in an
Query Processing IN() clause.

Points for Investigation

A spike seen in memory/sql/mem_heap_allocator is indicative of SQL Layer activity (like the Data Dictionary or the
Optimizer), not InnoDB internal page management. The 16GB spike in sort_keys that was found is a separate SQL-layer
instrument, but it often works alongside mem_heap_allocator during the execution of a complex query.

Would you like to see how mem_heap_allocator memory compares to InnoDB's internal dictionary memory in
your current global metrics?

mem_heap_allocator and MEM_ROOT

mem_heap_allocator is not a part of mem_root in a structural sense; rather, it is a C++ wrapper or "interface" that
allows C++ standard containers to use mem_root as their backing storage.

Think of mem_root as the actual storage tank and mem_heap_allocator as the adapter that plugs into that tank so
modern C++ tools can draw from it.

1. The Relationship
●​ mem_root (The Foundation): This is a long-standing C-based structure in MySQL used for arena-style memory
management. It allocates large blocks and doles them out in smaller pieces, freeing everything at once when a
query or session ends.
●​ mem_heap_allocator (The Interface): This is a C++ class designed to meet the requirements of an
std::allocator. It is initialized with a pointer to a mem_root. When a C++ container (like a std::vector) needs
memory, it calls the allocator, which in turn requests space from that specific mem_root.

2. Why this distinction matters

In your investigation of the 16GB spike and Data Dictionary bloat, understanding this link is crucial:
●​ Tracking: In performance_schema, memory used by C++ objects in the SQL layer is often tracked under
memory/sql/mem_heap_allocator because that is the instrumented interface through which the memory was
requested.
●​ Memory Efficiency: Because mem_heap_allocator uses mem_root, it inherits the "all-or-nothing" cleanup. If a
query creates a massive std::vector to sort keys, all that memory is released the moment the mem_root
associated with that query is destroyed.
●​ Unpredictable Growth: C++ containers often pre-allocate more space than they currently need (capacity vs.
size). Since they are using mem_heap_allocator, this "hidden" extra capacity is actually being carved out of your
mem_root and contributes to the Peak_MB you see in your metrics.
3. Comparison Summary

Feature mem_root mem_heap_allocator

Language C-based. C++ Template class.
Role The actual memory pool/arena. The adapter for STL containers.
P_S Instrument memory/sql/MEM_ROOT. memory/sql/mem_heap_allocator.
Owns the physical blocks of
Ownership RAM. References an existing mem_root.

To see how your "overhead" RAM is split between the raw storage pools and the C++ object interfaces, you can
compare the MEM_ROOT and mem_heap_allocator instruments. This helps determine if memory bloat is coming from
legacy C-style structures or modern C++ containers used in the SQL layer.

Global Comparison Query

Run this to see the current and peak usage for these two interconnected systems:

SQL

None
SELECT
EVENT_NAME,
COUNT_ALLOC AS Total_Allocations,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Current_MB,
ROUND(HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Peak_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN (
'memory/sql/THD::main_mem_root', -- Replaces mem_heap_allocator for session work
'memory/sql/TABLE_SHARE::mem_root', -- Tracks the "C++" Data Dictionary cache
'memory/sql/Filesort_buffer::sort_keys',-- The actual sorting data
'memory/sql/TABLE' -- Table object overhead
)
OR EVENT_NAME LIKE 'memory/sql/%mem_root%' -- Catches any other stray metadata roots
ORDER BY Peak_MB DESC;

Key Takeaways for Your Investigation

●​ memory/sql/MEM_ROOT: This represents the actual memory blocks held by the server for various SQL layer
tasks. Since mem_heap_allocator pulls from this pool, high usage here indicates large arena allocations for
queries or session-level tasks.
●​ memory/sql/mem_heap_allocator: This tracks the specific memory requested via the C++ interface. If this has
a high Peak_MB, it confirms that complex C++ objects (like those used in the Data Dictionary or by the
Optimizer) were the primary drivers of the memory pressure.
●​ The "Shadow" Allocation: Because C++ containers like std::vector often reserve more capacity than they
currently use, the mem_heap_allocator Peak might show exactly how much "extra" space was grabbed during
your 11:25 PM crash.
How to see the "Current" vs "Peak" distribution

Since you noticed Current_MB is only 620 MB while the Peak_MB is 13.9 GB, your server is currently in a very "quiet"
state. The 13.9 GB was a transient burst.

To see if this was one giant thread or many medium ones, you can run this while the server is busy:

SELECT
COUNT(THREAD_ID) AS Active_Threads,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED) / 1024 / 1024, 2) AS Total_Current_MB,
ROUND(MAX(HIGH_NUMBER_OF_BYTES_USED) / 1024 / 1024, 2) AS Single_Thread_Max_Peak_MB
FROM performance_schema.memory_summary_by_thread_by_event_name
WHERE EVENT_NAME = 'memory/sql/THD::main_mem_root';

Cross-Checking the Memory Layout

Since you’ve verified the MEM_ROOT query works now, here is the mental map we are building for your 312 GB production
server:

Memory Pool Current State Peak State

InnoDB Buffer Pool 226 GB (Fixed) 226 GB
Global mem_root 620 MB 13.9 GB
Global sort_keys (Check your other output) (Usually several GBs)
OS & Performance
Schema ~10-15 GB ~15 GB
Total Usage ~242 GB ~265+ GB

You still have a comfortable ~45 GB safety margin, which explains why this instance is stable despite the high peaks.

How to catch the "High Tide" next time

If you want to see if the 13.9 GB was caused by many small threads or one or two "monsters" in the mix, the best way is
to look at the per-thread summary. Since the HIGH_NUMBER_OF_BYTES_USED is persistent, you can run this right now
to see the "all-time record holder" for a single session:

SELECT
THREAD_ID,
USER,
ROUND(MAX_MEMORY_USED / 1024 / 1024, 2) AS Thread_Peak_MB
FROM sys.memory_by_thread_by_current_bytes
ORDER BY Thread_Peak_MB DESC
LIMIT 5;

my_alloc, mem_heap_allocator & special allocator - Unclutter

I understand why this is confusing. In MySQL's source code, names like "heap" and "allocator" are used by both the SQL
Layer (Server) and the Storage Engine (InnoDB), but they refer to completely different systems.

To clear this up, let's separate the Structural Arenas (the tanks) from the Interfaces (the pipes).
1. MEM_ROOT: The "Storage Tank"
MEM_ROOT is a C-based structure. It is not an allocator itself; it is a memory pool. It grabs large chunks of RAM from
the OS and hands out small pieces to the server as needed.
●​ Scope: It is used primarily by the SQL Layer (for query parsing, optimization, and session state).
●​ Behavior: It’s an "Arena." You can’t free a single object inside it. You must wait until the query or session is
finished, then the entire MEM_ROOT is wiped clean at once.

2. my_alloc & mem_heap_allocator: The "Pipes"

These are not parts of MEM_ROOT; they are the mechanisms used to put memory into or take memory out of it.
●​ my_alloc / my_malloc: This is the legacy C interface. When a C function needs memory for a MEM_ROOT, it
calls my_alloc.
●​ mem_heap_allocator: This is the modern C++ Interface. Since MySQL 8.0 uses a lot of C++ (like in the Data
Dictionary), it needs a way for C++ objects (like std::vector) to live inside a MEM_ROOT. mem_heap_allocator is
the bridge that allows that.

3. The "Execution Buffers": sort_buffer and join_buffer

This is the most important distinction: These buffers are not part of a MEM_ROOT. They are separate, dedicated
allocations.
●​ Independent Allocation: When a query needs to sort, MySQL calls the system allocator (like malloc) to grab
exactly the amount of RAM specified in sort_buffer_size.
●​ Why they are separate: MEM_ROOT is for "variable-sized metadata" (the query plan, the column names).
sort_buffer is for "fixed-size data processing."
●​ The "16GB Spike" Connection: This is why your sort_keys peak was tracked separately from main_mem_root.
They are two different tanks of water.

Summary Table: Who uses what?

Memory Pool Current State Peak State

InnoDB Buffer Pool 226 GB (Fixed) 226 GB
Global mem_root 620 MB 13.9 GB
Global sort_keys (Check your other output) (Usually several GBs)
OS & Performance
Schema ~10-15 GB ~15 GB
Total Usage ~242 GB ~265+ GB
Why this explains your crash/spike
If sort_buffer were part of MEM_ROOT, the 16.2 GB spike would have appeared inside the THD::main_mem_root
instrument. Because they are separate, you saw:
1.​ 13.9 GB in main_mem_root (The metadata/infrastructure).
2.​ 16.2 GB in sort_keys (The actual data work).

They hit your 50GB RAM limit from two different directions simultaneously.
​
Data Dictionary & The Accessibility Scope
The naming in MySQL's source code and Performance Schema can be counterintuitive. The Data Dictionary (DD) is a
global resource shared across sessions, yet it is associated with instruments that look session-specific.

The confusion stems from the fact that MySQL differentiates between the Global DD Cache (where the actual shared
metadata lives) and the Local Handle/Share (the thread's "view" of that metadata).

1. Global Cache vs. Thread Usage

While the Data Dictionary itself is global, the way a thread interacts with it requires local memory:
●​ TABLE_SHARE (Global): When a table is first opened, MySQL creates a TABLE_SHARE object. This is indeed
global and shared across all sessions to save memory.
●​ TABLE Instance (Local): Every single session that accesses that table gets its own TABLE object (the
"handler"). This object contains a session-specific state, like where the cursor is currently pointing in an index.
●​ The Allocation: The memory for these local TABLE objects and the metadata "pointers" used by the thread are
often allocated from the THD::main_mem_root.

2. Why it appears in main_mem_root

If your thread executes a query involving 50 tables, it doesn't just look at a global pool; it must instantiate 50 local
handler objects.
●​ The metadata that represents those tables within the context of that specific query is allocated using the
thread's memory root.
●​ This ensures that when the query finishes or the connection closes, the memory used to track those specific
table handles is cleaned up immediately without affecting the shared global cache.

3. The Role of TABLE_SHARE::mem_root

You likely saw TABLE_SHARE::mem_root in your Performance Schema output earlier. This is the true global part.
●​ This root belongs to the Global Cache.
●​ It stays alive as long as the table is "hot" in the cache, regardless of whether any specific thread is currently
using it.
●​ It is not part of the Thread class; it is owned by the Table_cache_manager.

Clarifying the Table

To make that table more accurate, here is how the Data Dictionary is actually split

Component Underlying Allocator Lifecycle Scope

Global DD Persistent (until cache
Metadata TABLE_SHARE::mem_root eviction) Shared Global
Session Table
Handles THD::main_mem_root Statement/Session level Per-Thread
Sort/Join Work Direct System malloc Duration of Sort/Join phase Per-Thread
The Data Dictionary (DD) is a global resource, so it cannot reside exclusively within a session-specific object like a
Thread.

The confusion often stems from how MySQL distinguishes between shared blueprints and local handles. While the
metadata is shared, the memory used to interact with it is often accounted for locally.

1. Global Cache vs. Thread Usage

The Data Dictionary logic is split into two distinct layers to allow for both sharing and session isolation:
●​ TABLE_SHARE (The Global Blueprint): When a table is opened, MySQL creates a TABLE_SHARE object. This
is the shared, read-only "template" that contains the table's structure, column types, and index definitions.
●​ TABLE Instance (The Local Handle): Every session that accesses that table gets its own TABLE object (the
"handler"). This object tracks the session's specific state, such as the current row pointer or lock status.

2. Where the mem_root actually lives

Ownership of the memory pools follows this split:
●​ TABLE_SHARE::mem_root (Shared): Each TABLE_SHARE has its own mem_root instance variable. This is
where the shared metadata is stored. It is owned by the global Table_cache_manager and persists as long as the
table is "hot" in the cache.
●​ THD::main_mem_root (Local): The memory for the session's local TABLE handles and the temporary pointers
used during query execution is drawn from the thread's memory root. This ensures that when the session ends, its
local "tools" for reading the dictionary are destroyed without affecting the shared global "blueprints".

3. Summary of Ownership and Scope

To clarify the table shared previously, here is the corrected breakdown of where these components reside:

Component Class Owner Memory Instrument Scope

Shared Metadata Table_cache_manager memory/sql/TABLE_SHARE::mem_root Global Shared

Session Handles THD (Thread Class) memory/sql/THD::main_mem_root Per-Thread

Parsing & Plans THD (Thread Class) memory/sql/THD::main_mem_root Per-Thread

Execution Buffers Global/Session memory/sql/Filesort_buffer::sort_keys Transient

The Verdict
The Data Dictionary definitions are indeed kept in a mem_root, but that root is inside the TABLE_SHARE class, which is
a global object.

However, because the Performance Schema tracks session activity so closely, many people see large memory usage in
THD::main_mem_root and assume the dictionary lives there. In reality, that session memory is just the overhead of the
thread "holding" its local connections to the global dictionary.

Would you like me to show you how to check the table_definition_cache to see the total number of shared TABLE_SHARE
objects currently held by your manager class?
1. The Table_cache_manager Class
The Table_cache_manager is the top-level C++ class responsible for the "Table Definition Cache". However, it does not
typically hold one single mem_root for all data. Instead, it manages a collection of instances to reduce lock contention.
●​ Ownership: It owns Table_cache instances, which in turn contain TABLE_SHARE objects.
●​ The mem_root Variable: The mem_root instance variable actually resides inside each TABLE_SHARE object.
●​ The instrument: This is tracked as memory/sql/TABLE_SHARE::mem_root in the Performance Schema.

2. Why the Data Dictionary is Global (but uses Session Memory)

You are correct that the Data Dictionary must be shared. MySQL achieves this by splitting the metadata into two distinct
layers:

Component Class / Owner Lifecycle Shared?

Remains in RAM as long Yes. Every session looks at this same
TABLE_SHARE Table_cache_manager as the table is "hot". "blueprint".
Statement/Session level
(Destroyed when the No. This is a private "view" or cursor into that
TABLE (Handler) THD (Thread) query/session ends). table.
3. Allocation and Accounting
This "Two-Layer" approach explains the confusion in the memory instruments:
●​ Shared Memory: The core metadata (column types, index names, constraints) is stored in the
TABLE_SHARE::mem_root. Because it is shared, it only needs to be allocated once, which keeps its global
memory footprint relatively small.
●​ Session Overhead: Even though the metadata is shared, when a thread runs a query, it must create a local
TABLE object to track its specific state (e.g., current record position, lock status). The memory for these local
handles is allocated from THD::main_mem_root.

4. Direct Answer: Is it part of the Thread?

The Data Dictionary definitions (the "blueprints") are not part of the Thread class; they are globally managed by the
Table_cache_manager.

However, the active handles (the "tools" used to read those blueprints) are owned by the Thread class and accounted
for in the session's memory root. This is why a global memory peak in THD::main_mem_root often reflects the combined
overhead of every session's active table handles, even if the underlying table definitions are shared globally.

Would you like me to show you how to check the table_open_cache status to see if your server is frequently evicting
these shared TABLE_SHARE objects from the manager?

MySQL Table_cache_manager
In the MySQL 8.0 source code, the Table_cache_manager class manages the infrastructure for sharing table
definitions across the server. It uses custom high-performance data structures rather than standard library containers
to ensure it can handle high concurrency.
1. Structure of the Table_cache_manager
The Table_cache_manager acts as a coordinator for multiple Table_cache instances. Each of these instances is
responsible for a subset (partition) of the open tables to prevent different threads from locking the same cache
simultaneously.
●​ The Array Pattern: The manager maintains an array of these Table_cache objects. This partitioning allows
multiple CPUs to access different parts of the table cache at the same time.
●​ The Shared Blueprint (TABLE_SHARE): The manager provides a global registry for TABLE_SHARE objects. A
TABLE_SHARE is the "master template" for a table's metadata (columns, data types, and index definitions).
●​ Ownership: The Table_cache_manager owns the TABLE_SHARE objects, but it does not store them in one giant
memory pool. Instead, each TABLE_SHARE contains its own private mem_root.

2. How Table Handles are Managed

To track which session is using which table, MySQL uses specialized intrusive linked lists.
●​ Intrusive Lists: Instead of using a separate container to hold pointers to tables, the pointers (next/previous) are
built directly into the TABLE and TABLE_SHARE objects themselves. This allows the manager to move a table
from a "Free" list to a "Used" list without allocating any new memory for the list itself.
●​ Fast Lookups: The manager uses a custom hash table to quickly find a table by its name. Once found, it
provides the thread with a pointer to the shared TABLE_SHARE.

3. Summary of Data Dictionary Logic

The following table outlines how the manager organizes these components without relying on generic external
containers:

Component Management Strategy Purpose

Cache Partitions Fixed-size array of Table_cache Reduces lock contention across multiple cores.
Hash table of TABLE_SHARE
Table Blueprints objects Provides a single, shared global reference for metadata.
Active Handles Intrusive linked lists Tracks "In Use" vs "Free" table objects for each session.
Metadata Storage Per-object mem_root Allows individual tables to be loaded or evicted independently.
Conclusion for Documentation
The Table_cache_manager avoids the overhead of generic containers by using partitioned arrays, custom hash
tables, and intrusive lists. This architecture ensures that the Data Dictionary remains a shared global resource, while
the memory required to track each session's "active handle" is kept separate and efficient.

To finalize your documentation on how the Table_cache_manager coordinates these resources, here are the specific
configuration variables that govern the manager's behavior. These settings determine how the internal arrays and
partitions are sized.

Configuration Variables for the Table_cache_manager

Variable Description Documentation Impact

The total number of open tables (handles) the Controls the size of the intrusive "Free" and
table_open_cache manager can hold across all partitions. "Used" lists.
table_definition_ca The number of TABLE_SHARE objects (blueprints) Limits the number of unique mem_root
che that can be stored in the global registry. instances active for metadata.
table_open_cache The number of partitions in the Determines the number of locks; higher values
_instances Table_cache_manager array. reduce contention on multi-core systems.

Internal Relationship Summary

In your documentation, you can summarize the hierarchy as follows:
1.​ The Manager (Table_cache_manager): A global singleton that initializes an array of Table_cache instances
based on the table_open_cache_instances setting.
2.​ The Cache Instance (Table_cache): A partition that owns the intrusive lists for TABLE handles. It acts as the
"checkout counter" for sessions.
3.​ The Shared Template (TABLE_SHARE): A unique object representing a physical table. It contains the
mem_root where the actual Data Dictionary metadata resides.
4.​ The Session Handle (TABLE): A non-shared object created for a specific thread. It points to a TABLE_SHARE
but tracks the thread's private state (like the current record position).

By partitioning the manager into multiple instances, MySQL allows the Data Dictionary to remain a globally shared
resource without becoming a performance bottleneck when many threads attempt to access table metadata
simultaneously.

InnoDB mem_heap_t description

In the InnoDB storage engine, mem_heap_t is the core structure for "Arena Allocation." It is designed to handle the
high-frequency, short-lived memory needs of the engine without the performance penalty of calling the operating
system's malloc for every small object.

1. The Strategy: Block-Based Allocation

Instead of allocating space for a single object, mem_heap_t allocates a large block of memory (typically from the
system or ut_allocator).
●​ Carving: Small objects (like a single row tuple or an index search path) are "carved" out of this pre-allocated
block.
●​ Speed: This is a simple pointer-increment operation, which is significantly faster than a standard memory
allocation.
●​ Chaining: If a block becomes full, the heap automatically allocates a new block and chains it to the previous
one, allowing the heap to grow dynamically.

2. Primary Use Cases within InnoDB

mem_heap_t is the "transient" workhorse for the engine's internal operations:
●​ Row Tuples: When InnoDB reads a row from a leaf page, it often constructs a "tuple" (a logical representation of
the row) in a heap.
●​ B-tree Search Paths: As InnoDB traverses an index to find a specific record, it uses a heap to store the "cursor"
and path information for that specific operation.
●​ Undo Log Records: Temporary storage of undo information during a transaction's lifecycle often utilizes these
heaps.
 ●​ SQL Layer Communication: When InnoDB passes data back up to the SQL layer, it often uses these heaps to
hold the formatted results temporarily.

3. The "All-or-Nothing" Lifecycle

The most critical feature of mem_heap_t is its destruction phase:
●​ No Individual Frees: You cannot free a single object inside a mem_heap_t. You must keep allocating until the
task is done.
●​ Single-Call Cleanup: When the task (like a row read or a transaction step) is finished, the entire heap is
destroyed with one call to mem_heap_free().
●​ Memory Safety: This virtually eliminates "memory leaks" for transient objects because the developer doesn't
have to remember to free every tiny pointer individually.

4. Comparison to ut_allocator
While both exist at the engine layer, they have opposite roles:

Feature mem_heap_t ut_allocator

Philosophy Arena/Bulk allocation. Object-level/System wrapper.
Object Size Small, variable objects. Large, fixed structures (like Buffer Pool).
Lifetime Very short (milliseconds to seconds). Long (until shutdown).
Interface C-style functions. C++ Template class.

Monitor InnoDB internal Heaps

To monitor the peak usage of InnoDB's internal heaps, you should look specifically for the memory/innodb/mem0mem
instrument in the Performance Schema.

Monitoring InnoDB Heaps

The following query provides a clear view of how much memory mem_heap_t is currently consuming and the highest it
has reached since the last reset or restart.

SQL

None
SELECT
EVENT_NAME,
COUNT_ALLOC AS Total_Allocations,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Current_MB,
ROUND(HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Peak_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/innodb/mem0mem';

Understanding the Results

 ●​ Current_MB: This represents the memory used by transient objects like row tuples and search paths active at
this exact moment.
●​ Peak_MB: This is your "high-water mark". If this value is high, it indicates that a particularly complex transaction
or a high-concurrency event forced InnoDB to allocate significant arena memory.
●​ The 37GB Buffer Pool Context: While mem0mem is usually small (often under 100MB), it exists outside of the
innodb_buffer_pool_size. On your 50GB instance with a 37GB Buffer Pool, a sudden spike in mem0mem
combined with your 16GB sort_keys spike would contribute to the OOM crash you experienced.

Comparison with Long-Lived Allocations

To see how these transient heaps compare to the more permanent allocations managed by ut_allocator, you can run:

SQL

None
SELECT
EVENT_NAME,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Current_MB,
ROUND(HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS Peak_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN ('memory/innodb/mem0mem', 'memory/innodb/ut0mem');

●​ ut0mem: Tracks large, long-lived structures.

●​ mem0mem: Tracks the short-lived heaps we just discussed.

Distinction between Buffer Pool and Table Open Cache

The Buffer Pool is the heavy lifter that stores the actual data and indexes.

The table_open_cache, on the other hand, is much more about housekeeping and coordination.

What exactly is stored there?

Think of the table_open_cache as a collection of file handles and metadata descriptors.

When a query runs, MySQL needs to know the structure of the table (the columns, data types, and constraints) and needs
a way to talk to the operating system to access the underlying file. Instead of opening and closing the physical .ibd or .frm
files every single time a query runs—which is "expensive" in terms of CPU and I/O—MySQL keeps these "pointers" ready
in the cache.

Key Components:
●​ Table Descriptors: Information about the table's structure (parsed from the header).
●​ File Handles: A direct link to the physical file on the disk so the engine can read/write without re-initializing the
connection.
●​ Status Information: Metadata about whether the table is currently being modified or locked.
The "Per-Thread" Nuance
One of the most common points of confusion is why this cache can get so large.

In MySQL, table cache entries are not always shared globally across all users in the way the Buffer Pool is. If you have 50
different concurrent sessions all running a join on Orders and Customers, MySQL might need to keep multiple entries open
for the same table to manage the state for each thread safely.

Comparison at a Glance

Feature table_open_cache innodb_buffer_pool_size

Content File handles & Metadata Actual Data & Index pages
Purpose Reduces overhead of opening files Reduces disk I/O for data
Size Usually measured in thousands (count) Measured in Gigabytes (memory)
Layer MySQL Server Layer Storage Engine Layer (InnoDB)

How do you know if it's working?

If your table_open_cache is too small, MySQL will constantly be "evicting" old table descriptors to make room for new
ones. You can check this by looking at your status variables:
●​ Opened_tables: If this number is increasing rapidly, your cache is too small.
●​ Open_tables: This shows how many are currently sitting in the cache.

Pro-tip: Don't just crank this number to the moon. Each open table uses a bit of memory and, more importantly,
an OS file descriptor. If you set it higher than your operating system's ulimit, MySQL might crash when it tries to
open a new file.

UT (Utilities)
The prefix UT in the MySQL InnoDB storage engine source code stands for Utilities.
It is used as a prefix for files, functions, and macros that implement basic, general-purpose utility functions,
which are often low-level data structures and primitives used throughout the rest of the InnoDB code.
For example, you'll see:
●​ ut_allocator and ut_malloc for memory allocation.
●​ UT_LIST_INIT for linked list operations.
In essence, the ut module provides a toolkit of common, foundational routines for the InnoDB engine.
References
The three Alibaba Cloud blog posts provide a deep, source-code-level analysis of MySQL's complex memory management
architecture, covering allocation, tracing, and limiting features across the SQL and InnoDB layers.
This explanation synthesizes the concepts and details the function of the core source code snippets described in the
articles, relating them to the generally known structure of the MySQL Server (public domain information).
MySQL Memory Allocation and Management (Part I) ​
MySQL Memory Allocation and Management (Part II)​
MySQL Memory Allocation and Management (Part III)
Jan 6, 2026
Allocation of memory for system variables:
read_rnd_buffer_size,sort_buffer_size,join_buffer_size,binlog_cach
e_size,thread_stack,tmp_table_size,net_buffer_length
All these variables are not allocated memory upfront when a connection gets created.
MySQL handles memory allocation for connections dynamically. While some small buffers are allocated immediately to
keep the connection alive, most of the "heavy hitter" buffers are only allocated on-demand when a specific query needs
them.
Here is a breakdown of how these parameters behave when a new connection is created:
1. The "Immediate" Allocations
These are allocated the moment the thread is created to ensure the connection can communicate and execute basic tasks.
●​ thread_stack: This is allocated upfront. It is the amount of stack memory reserved for each thread to handle
function calls and local variables within the MySQL server code.
●​ net_buffer_length: This is allocated upfront. It acts as the initial "mailbox" for the connection to receive query
strings and send results. It can grow up to max_allowed_packet.

2. The "On-Demand" (Query-Dependent) Allocations

These are not allocated at connection time. They are only grabbed from the OS when a query actually performs an
2
operation that requires them (like a sort or a complex join).
●​ sort_buffer_size: Allocated only when a query needs to perform an ORDER BY or GROUP BY that cannot be
handled by an index.
●​ join_buffer_size: Allocated only when a join does not use an index (Full Join). Interestingly, if a query has multiple
joins, it may allocate multiple join buffers.
●​ read_buffer_size / read_rnd_buffer_size: Allocated only when the engine needs to perform a sequential scan or
a sorted read (like after an index scan).
●​ binlog_cache_size: Allocated only when a connection starts a transaction. It holds the changes made during the
transaction before they are flushed to the binary log.

3. The "Hybrid" Allocations

●​ tmp_table_size: This isn't a pre-allocation at all; it is a limit. If a query needs an internal temporary table, it starts
small in memory. It can grow up to the size of tmp_table_size (or max_heap_table_size). If it exceeds that, it is
converted to a disk-based table.

Summary Table

Parameter Allocated at Connection? When is it allocated?

thread_stack Yes Thread creation

net_buffer_length Yes Thread creation

sort_buffer_size No During an ORDER BY / GROUP BY

join_buffer_size No During a Join without an index

read_rnd_buffer_size No During sorted record reads

binlog_cache_size No At the start of a transaction

tmp_table_size No Only if a query needs a temp table (up to this limit)

Why this matters
If you set sort_buffer_size to 32MB and you have 100 connections, you haven't automatically used 3.2GB of RAM.
However, if all 100 connections suddenly run a heavy "ORDER BY" query at the same time, your server could experience
a massive, sudden spike in memory usage and potentially crash (OOM).
Would you like me to help you calculate the Maximum Theoretical Memory Usage for your specific server configuration?

Temporary Table Requirement​

In MySQL, "Internal Temporary Tables" are created by the optimizer to hold intermediate results during query
[Link] tables start in memory (using the TempTable or MEMORY storage engine) and are only moved to disk if
they grow larger than tmp_table_size or contain specific data types like BLOB.
The following operations are the most common triggers for these in-memory temporary tables:
1. Set Operations (UNION)
When you use a UNION (which implies a "Distinct" operation), MySQL must identify and remove duplicate rows from the
combined result sets. It creates a temporary table to store rows from the first query and then checks subsequent query
results against that table to ensure uniqueness.
●​ Note: Using UNION ALL typically avoids a temporary table because it does not need to check for duplicates.
2. Complex Grouping and Aggregates
If you use a GROUP BY clause on columns that are not indexed, or if the grouping involves multiple tables in a join,
MySQL creates a temporary table to accumulate the aggregate values (like SUM or COUNT) for each unique group.
●​ COUNT(DISTINCT ...): This almost always requires a temporary table to keep track of the unique values
encountered so far.
●​ GROUP_CONCAT(): Uses a temporary table to build the string of concatenated values.
3. Window Functions
Introduced in MySQL 8.0, window functions (e.g., ROW_NUMBER(), RANK(), LEAD()) frequently use internal temporary
tables. The engine needs a place to store the "partitioned" data and apply calculations across the current "window" of rows
before returning them to the final result set.
4. Derived Tables and CTEs
●​ Derived Tables: Queries like SELECT * FROM (SELECT ...) AS my_subquery often "materialize" the subquery
into a temporary table first.
●​ Common Table Expressions (CTEs): While basic CTEs can sometimes be merged into the main query, complex
or recursive CTEs often require a temporary table to store the results of each iteration.
5. Joins and Subqueries
●​ Semijoin Materialization: When performing subqueries (like WHERE x IN (SELECT ...)), MySQL may materialize
the inner query result into a temporary table to speed up the matching process.
●​ Multi-table Updates: Updates involving more than one table often use a temporary table to hold the identifiers of
the rows that need to be changed.

Operations that skip Memory and go straight to Disk

Even if your tmp_table_size is very large, MySQL will skip the memory phase and create an on-disk temporary table
immediately if:
●​ The query involves BLOB or TEXT columns.
●​ There is a column in a UNION or GROUP BY that is larger than 512 bytes (for older engines).
●​ You use the SQL_SMALL_RESULT or SQL_BIG_RESULT modifiers (which explicitly tell the optimizer how to
handle the result set).
How to check your usage
You can see if a query is using a temporary table by running EXPLAIN on [Link] you see "Using temporary" in the "Extra"
column, it means an internal temporary table is being planned.
Creating and using Temporary Tables
This video provides a practical look at how temporary tables are created and used within a session to handle complex data
manipulation tasks.

Memory Allocator for Buffers

For these specific buffers—read_rnd_buffer_size, sort_buffer_size, and join_buffer_size—the allocator used is
mem_root.
In MySQL, these are considered Engine-Independent Performance Buffers. They are allocated at the SQL layer when
a query starts its execution phase and are freed immediately after the operation (or query) completes.

Why mem_root?
The MEM_ROOT structure is the "workhorse" for session-level memory in MySQL. It is designed for the exact lifecycle of
these buffers:
1.​ Allocation: When a query needs to perform a Filesort, it requests a chunk of memory defined by
sort_buffer_size.
2.​ Usage: The memory is used intensely for a short burst.
3.​ Deallocation: Instead of the overhead of individual free() calls for every small structure within that buffer,
MySQL simply "resets" or destroys the entire MEM_ROOT.

Why it isn't the others:

●​ ut_allocator: As mentioned before, this is strictly InnoDB-land. While the data being sorted might come from
InnoDB, the actual sorting process happens in the SQL layer's sort_buffer, which InnoDB doesn't manage.
●​ mem_heap_allocator: This is used for dynamic C++ objects and STL containers. The buffers you listed are "flat"
memory chunks (byte arrays) used for raw data processing, which doesn't require the object-oriented overhead
of the STL allocator.

How to see these in Performance Schema

If you want to track the memory usage of these specific buffers live, they are instrumented under these event names

Buffer Performance Schema Event Name

join_buffer_size memory/sql/JOIN_CACHE
sort_buffer_size memory/sql/sort_buffer
read_rnd_buffer_size memory/sql/READ_RND_BUFFER
A Quick Warning on Memory Multipliers
These buffers are thread-specific. Because they use mem_root at the session level, the total memory consumption is:

If you have 500 connections and you set sort_buffer_size to 10MB, you could potentially allocate 5GB of RAM just for
sorting if all threads perform a sort at the same time.

Location of Buffers

They do not typically go inside the main_mem_root.

In MySQL, main_mem_root (often referred to as the "statement mem_root") is used for objects that must persist for the
entire duration of a statement's execution, such as the items in the SELECT list, the parsed tree, and other structural
metadata.
Buffers like sort_buffer, join_buffer, and read_rnd_buffer are handled differently:

1. Dedicated Allocations
These buffers are often allocated as standalone blocks of memory rather than being carved out of a pre-existing
mem_root. While the pointer to the buffer might be stored in a structure managed by a mem_root, the actual large
chunk of memory (e.g., a 2MB sort_buffer) is usually allocated directly from the system allocator (via my_malloc) for that
specific task.

2. Specialized MEM_ROOTs
In many cases, MySQL creates a local, task-specific MEM_ROOT rather than using the main_mem_root.
●​ For Sorting: The Filesort operation often initializes its own private MEM_ROOT to manage the lifecycle of the
sort keys and buffers.
●​ Why? If these huge buffers were put into the main_mem_root, they couldn't be freed until the entire statement
was finished. By using a separate allocation or a dedicated MEM_ROOT, MySQL can free the sort_buffer the
moment the sort is complete, even if the query is still busy sending results to the client.

3. The Hierarchy
●​ THD (Thread Object): The parent container for the session.
○​ main_mem_root: Small, persistent allocations for the life of the query (the "blueprint").
○​ Filesort_buffer: A separate, large allocation (the "heavy lifter").
○​ Join_Cache: A separate, large allocation for join operations.

Summary
If you look at the source code, you'll see that these buffers are managed by the Query_arena or specific execution
classes. They are kept separate from the main_mem_root to prevent memory bloat. If everything went into the main
root, a complex query with multiple joins and sorts would keep all that memory locked until the very last row was sent to
the user.

Monitor the TRANSIENT vs. PERSISTENT memory usage of a specific Thread

To monitor the distinction between Persistent memory (like main_mem_root) and Transient memory (like sorting and
join buffers), you can use the Performance Schema's thread-level memory tables.

1. Identifying Persistent vs. Transient Memory

●​ Persistent (Statement-Level): This is tracked under instruments like memory/sql/THD::main_mem_root. It holds
the query's "plan"—the structures needed from the moment the query is parsed until the last row is sent.
●​ Transient (Operation-Level): These are the buffers we discussed (sort_buffer, join_buffer). They are created
on-demand and often destroyed before the query itself is fully finished.

2. The Monitoring Query

Run this query to see a live breakdown of memory for every active connection. It separates the "Root" (persistent)
memory from the "Functional" (transient) buffers.

SQL
 None
SELECT
EVENT_NAME,
COUNT_ALLOC AS Total_Allocations,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / (1024*1024), 2) AS Current_MB,
ROUND(HIGH_NUMBER_OF_BYTES_USED / (1024*1024), 2) AS Peak_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN (
'memory/sql/Filesort_buffer::sort_keys',
'memory/sql/JOIN_CACHE',
'memory/sql/TABLE::read_record_cache',
'memory/sql/TABLE::record_buffer',
'memory/sql/Filesort_info::merge',
'memory/sql/Filesort_info::record_pointers',
'memory/sql/TABLE::sort_io_cache',
'memory/sql/Unique::sort_buffer'

)
OR EVENT_NAME LIKE 'memory/sql/Filesort_info%';

In MySQL 8.0+, the memory previously associated with the sort_buffer_size variable is now split into more granular
"Filesort" instruments.

The Correct "Sort" Instruments

When you perform a query that requires a sort, MySQL allocates memory that is tracked by these specific instruments
you found:
1.​ memory/sql/Filesort_buffer::sort_keys: This is the primary consumer of the sort_buffer_size setting. It stores
the actual keys and row pointers being sorted.
2.​ memory/sql/Filesort_info::record_pointers: Tracks the memory used to hold pointers to the records during the
sorting process.
3.​ memory/sql/Filesort_info::merge: Used when the data is too large for the buffer and must be merged via
temporary files on disk (Sort Merge Passes).

What about Join and Read Rnd Buffers?

Similarly, the other buffers are not named exactly like their system variables. Here are their real Performance Schema
identities:

System Variable Performance Schema Instrument

join_buffer_size memory/sql/JOIN_CACHE
read_buffer_size memory/sql/TABLE::read_record_cache
read_rnd_buffer_size memory/sql/TABLE::record_buffer
Why the names are different
The system variables (like sort_buffer_size) define a limit, but the Performance Schema instruments (like
Filesort_buffer::sort_keys) track the actual allocation within the code.

One "logical" sort operation might involve three or four different "physical" memory allocations. This is why a simple
LIKE '%sort%' shows a family of instruments rather than one single buffer.

In prod-000:58c0 Sort Buffer & Join Cache

SELECT
EVENT_NAME,
COUNT_ALLOC AS Total_Allocations,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / (1024*1024), 2) AS Current_MB,
ROUND(HIGH_NUMBER_OF_BYTES_USED / (1024*1024), 2) AS Peak_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN (
'memory/sql/Filesort_buffer::sort_keys',
'memory/sql/JOIN_CACHE',
'memory/sql/TABLE::read_record_cache',
'memory/sql/TABLE::record_buffer',
'memory/sql/Unique::sort_buffer'
)
OR EVENT_NAME LIKE 'memory/sql/Filesort_info%';
+-------------------------------------------+-------------------+------------+----------
+
| EVENT_NAME | Total_Allocations | Current_MB | Peak_MB
|
+-------------------------------------------+-------------------+------------+----------
+
| memory/sql/Filesort_info::merge | 2262 | 0.00 | 0.13
|
| memory/sql/Filesort_info::record_pointers | 1364115 | 0.00 | 76.82
|
| memory/sql/Filesort_buffer::sort_keys | 2727000 | 0.00 | 16655.50
|
| memory/sql/JOIN_CACHE | 0 | 0.00 | 0.00
|
+-------------------------------------------+-------------------+------------+----------
+
4 rows in set (0.02 sec)

That 16GB figure in your Peak_MB column is a significant finding, but it is important to interpret it correctly.

The Peak_MB value (from HIGH_NUMBER_OF_BYTES_USED) represents the highest amount of memory this specific
instrument held simultaneously since the server started or the statistics were last reset.

In the output, memory/sql/Filesort_buffer::sort_keys shows 16655.50 MB. This means at some point in the past, MySQL
had 16.2 GB of RAM allocated just for sorting keys in a single moment.

How to read your specific table

Column Interpretation
Total_Allocations Cumulative Volume. Since startup, MySQL has requested memory for sort_keys 2.7 million times.

The "Now". Currently, no queries are performing a sort large enough to be tracked here. The
Current_MB (0.00) RAM has been freed.

The "High Water Mark". This is the actual peak concurrency. It means that either one massive
Peak_MB (16655.50) query or many simultaneous queries together grabbed 16.2 GB of RAM for sorting.

Is this a "Single Query" or "Total Server" peak?

Because you are looking at memory_summary_global_by_event_name, this number is the global peak for that
instrument. It could be triggered by:
1.​ A Single Rogue Query: One massive SELECT ... ORDER BY that needed 16GB to sort (if your sort_buffer_size is
set that high, or if it's a very large result set).
2.​ Concurrency: Multiple threads sorting at the exact same time. If 1,000 threads each used a 16MB sort buffer
simultaneously, the global peak would reach ~16GB.

Why Filesort_buffer::sort_keys is so high

This specific instrument tracks the memory used to hold the keys being compared during a sort.
●​ If sort_buffer_size is large: MySQL will try to keep the entire sort in memory to avoid disk I/O.
●​ The Spill Check: Notice your Filesort_info::merge peak is very low (0.13 MB). This is actually a good sign. It
suggests that MySQL successfully kept your massive sorts in memory rather than "spilling" them to disk (which
would have crashed performance).

Next Steps to Investigate

If 16GB is a large portion of your total system RAM, you should find out which query did this. You can check the
"Statement Summary" to find the memory-hungry query:

SQL

None
SELECT
DIGEST_TEXT,
MAX_CONTROLLED_MEMORY / 1024 / 1024 AS Max_Mem_MB,
MAX_TOTAL_MEMORY / 1024 / 1024 AS Max_Total_MB,
COUNT_STAR AS Execution_Count
FROM performance_schema.events_statements_summary_by_digest
ORDER BY MAX_CONTROLLED_MEMORY DESC
LIMIT 5;

3. Understanding the Thread Memory Hierarchy

This hierarchy explains why you might see memory usage fluctuate during a single query:
1.​ Parse Phase: main_mem_root grows as the SQL text is turned into an internal tree.
 2.​ Execution Phase: memory/sql/sort_buffer or JOIN_CACHE suddenly spikes to its full configured size (e.g., 2MB).
3.​ Completion Phase: The sort_buffer is freed (Current_KB drops to 0), but main_mem_root stays active while the
rows are being streamed to the client.

4. How to Spot "Memory Bloat"

If you notice that HIGH_NUMBER_OF_BYTES_USED is much larger than CURRENT_NUMBER_OF_BYTES_USED for a
specific thread, it means that thread recently performed a heavy operation (like a large sort) that has since finished.

If THD::main_mem_root is high (e.g., several MBs), it usually points to:

●​ Extremely long SQL statements (massive INSERT or IN clauses).
●​ A very large number of open result sets in a single transaction.
●​ Stored procedures with many local variables.

Dictionary Cache , Table Open Cache and Table Definition Cache

MySQL Dictionary Cache is distinct from the Table Open Cache and the Table Definition Cache.

While all three are memory structures used to speed up access to metadata, they reside at different layers of the MySQL
architecture (especially in MySQL 8.0+) and serve different purposes.

Relationship Overview
Think of these caches as a hierarchy. When you run a query like SELECT * FROM users;, MySQL looks for metadata
in this order:
1.​ Table Open Cache (table_open_cache): Checks for an available "open" instance of the table (including file
descriptors and handlers) for the current thread.
 2.​ Table Definition Cache (table_definition_cache): If no open instance exists, it looks for the shared table
"blueprint" (TABLE_SHARE) to avoid re-parsing the schema.
3.​ Data Dictionary Cache: If the blueprint isn't found, it queries the internal Data Dictionary (stored in InnoDB tables)
to build the definition. The Dictionary Cache speeds up this specific step.

Detailed Comparison

Cache Type Primary Function Scope Key Configuration

Stores TABLE objects
(handlers/file descriptors).
Each concurrent thread Per-thread (stored in a
Table Open Cache needs its own object. global pool) table_open_cache
Stores TABLE_SHARE
objects (the schema
"blueprint"). Shared by all
Table Definition Cache threads. Global table_definition_cache
Stores low-level objects
(columns, indexes,
tablespaces) retrieved from max_connections (for table
Data Dictionary Cache the data dictionary. Global (Internal) partition)

Key Nuance in MySQL 8.0+

Since MySQL 8.0, the "Dictionary Object Cache" is a more formal structure divided into partitions (e.g., tablespace
definitions, schema definitions, and table definitions).
●​ The Parallel Caches: Interestingly, MySQL 8.0 maintains a "Table Definition Cache Partition" inside the Dictionary
Cache, but it exists in parallel with the traditional table_definition_cache.
●​ No Dependence: Objects in the Dictionary Cache do not depend on objects in the Table Definition [Link]
serve different parts of the server's internal logic.
●​ Storage Change: In older versions (5.7 and earlier), the "Data Dictionary" was just a collection of .frm and .opt
files on disk. In 8.0, it is a set of transactional InnoDB tables, making the Dictionary Cache essential for avoiding
constant internal SQL lookups to these hidden tables.

Summary
The Table Open Cache and Table Definition Cache are "high-level" caches used by the server to manage active queries.
The Dictionary Cache is a "lower-level" cache used specifically to fetch the raw metadata required to populate those
higher-level caches.

Details of Dictionary Cache

In older versions of MySQL (like 5.7), table metadata was stored in a messy collection of individual files on your disk (like
.frm, .opt, and .par files). This was slow and not "crash-safe."

In MySQL 8.0, the architects moved all that metadata into actual InnoDB tables. These are the "hidden" tables.

1. What are the "Hidden Tables"?

MySQL 8.0 stores its own blueprints in a set of system tables located in the mysql schema. They are "hidden" because you
cannot see them with a normal SHOW TABLES command, and you cannot query them directly with a standard SELECT
(unless you use a special debug build).

Some of these hidden tables include:

●​ [Link]: Stores every table name, engine, and creation time.
●​ [Link]: Stores every column name, type, and nullability for every table.
●​ [Link]: Stores details about every index in your database.
●​ [Link]: Stores information about your databases (schemas).

Instead of looking at these directly, you use Views (like INFORMATION_SCHEMA.TABLES) which pull data from these
hidden tables behind the scenes.

2. The "Internal SQL Lookup" Problem

Because the metadata is now stored in InnoDB tables, every time MySQL needs to know something about a table (e.g.,
"What columns does orders have?"), it effectively has to run a "mini-query" against its own internal [Link]
table.

Without a cache, a single user query like SELECT * FROM orders would trigger several "invisible" internal queries:
1.​ Check [Link] to see if the database exists.
2.​ Check [Link] to see if orders exist.
3.​ Check [Link] to get the column list.
4.​ Check [Link] to see if there’s a primary key.

If you have thousands of tables or high traffic, doing these internal InnoDB lookups constantly would create a massive
performance bottleneck and flood your innodb_buffer_pool.

3. How the Dictionary Cache Alleviates This

The Dictionary Cache (also called the Dictionary Object Cache) acts as a "memory shortcut" for these internal tables.
●​ Avoids Re-parsing: When the server fetches a table's definition from the hidden [Link] and
[Link], it converts that raw data into a C++ object (a "dictionary object").
●​ Persistent Memory: It stores these objects in memory partitions (e.g., the tablespace, schema, and table
partitions).
●​ Direct Access: The next time any thread asks for the same metadata, the server retrieves the ready-to-use C++
object from the Dictionary Cache instead of executing an internal SQL-like lookup to the InnoDB storage engine.

Summary of the Flow

1.​ User Query arrives.
2.​ Server checks Dictionary Cache for the table metadata.
3.​ If Cache Miss: Server performs an Internal Lookup to the hidden InnoDB tables ([Link], etc.), loads
the data, and populates the cache.
4.​ If Cache Hit: Server skips the InnoDB lookup entirely and moves straight to query execution.

MySQL 8 Improvement on Legacy Dictionary Cache

To understand how this works, we need to look at the hidden architecture of MySQL 8.0.
1. The "Hidden" Tables
In MySQL 8.0, the server's metadata is no longer in text files on your hard drive. It is stored in a hidden data dictionary
located in the mysql system schema.

These tables are "hidden" because they are technically Base Tables that a standard user (even a root user) cannot see or
query directly. If you run SHOW TABLES IN mysql;, they won't appear. Instead, MySQL provides Information Schema
Views that act as a window into these tables.
●​ The Hidden Table: [Link] (Actual storage)
●​ The Public Window: INFORMATION_SCHEMA.TABLES (The view you query)

2. What is an "Internal SQL Lookup"?

When you execute a query like DESCRIBE customers;, the MySQL server engine needs to find out what columns exist in
customers.
●​ The Old Way (5.7): The server would open a file named [Link] and parse the binary data.
●​ The New Way (8.0): The server's internal "Dictionary API" must fetch this data from the mysql tables. Since those
tables are InnoDB tables, the server effectively performs a recursive internal query. It’s like the database is
running a private SELECT against itself to figure out how to run your SELECT.

3. How the Dictionary Cache Stops This

Doing an InnoDB lookup for every single metadata request is expensive—it involves locks, buffer pool activity, and
transaction logic. The Dictionary Cache prevents this by acting as a high-speed "memory object" store.
1.​ First Access: You query a table. MySQL does the "internal lookup" to the hidden InnoDB tables. It takes that raw
data and builds a C++ object (a Dictionary Object) in memory.
2.​ Subsequent Access: The next time that table (or any part of it) is referenced, MySQL pulls the C++ object directly
from the Dictionary Cache.
3.​ The Result: No internal SQL is run, no InnoDB pages are fetched, and no locks are placed on the system tables.

How to Check Your Cache Performance

You can monitor if your caches are struggling (meaning they are too small and causing those "internal lookups") by
checking these status variables:

Status Variable What it Tells You Description

How many times the server had to
perform a "lookup" because the definition
Opened_table_defin wasn't in the cache. If this grows rapidly, Includes the Buffer Pool, Change Buffer, Adaptive
itions your cache is too small. Hash Index, and Log Buffer.

Open_table_definit How many definitions are currently sitting Memory used by the Table Definition Cache and
ions in the cache. the Table Open Cache.

Table_open_cache_m How many times a thread couldn't find an Memory for the Data Dictionary (introduced in
isses "open table" and had to wait or open one. MySQL 8.0) which stores object metadata.
Memory allocated to the main execution thread
sql_thd_main memory/sql/THD::main and basic connection overhead.
A specific heap used for row selection and search
innodb_row0sel memory/innodb/row0sel operations.
Configuration Tips
In MySQL 8.0, you can tune these specific partitions of the Dictionary Cache:
●​ tablespace_definition_cache: Defaults to 256.
●​ schema_definition_cache: Defaults to 256.
●​ table_definition_cache: Defaults to 2000 (usually).

To check if your caches are sized correctly, you can run the following query. This calculates the Table Definition Cache
Hit Ratio.

A low percentage (e.g., below 90%) means MySQL is frequently forced to perform those "internal lookups" on the hidden
data dictionary tables because it keeps running out of room in the cache.

SQL

None
SELECT
VARIABLE_VALUE AS Total_Opened,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Open_table_definitions') AS Currently_Open,
ROUND((1 - (VARIABLE_VALUE / (SELECT SUM(VARIABLE_VALUE) FROM
performance_schema.global_status WHERE VARIABLE_NAME IN ('Opened_table_definitions',
'Queries')))) * 100, 2) AS Hit_Ratio_Percentage
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Opened_table_definitions';

How to Interpret the Results

1. The Table Definition Cache (table_definition_cache)
This is the most important one for the "hidden table" problem we discussed.
●​ The Goal: Opened_table_definitions should grow very slowly after the server has been running for a few hours.
●​ The Fix: If the hit ratio is low, increase this value in your [Link]:
●​ Ini, TOML

None
table_definition_cache = 4000 # Double the default if you have many tables

2. The Table Open Cache (table_open_cache)

While the definition cache stores the "blueprint," this cache stores the actual file handles for active threads.
●​ The Symptom: Check the status variable Table_open_cache_misses. If this number is high and increasing,
your threads are fighting over a limited number of "open" slots.
●​ The Fix: Increase table_open_cache. A common rule of thumb for busy servers is:​
table_open_cache = (Max_Concurrent_Connections) * (Average_Tables_Per_Query)

A Quick Note on "Hidden" Performance

If you notice that queries against INFORMATION_SCHEMA are very slow, it is almost always because the Dictionary
Cache or Table Definition Cache is too small. When they are too small, MySQL has to "re-read" those hidden InnoDB
tables and re-calculate statistics for every column and index every time you ask for metadata.

To see what is actually sitting in your cache right now, we use the Performance Schema. This is the only way to "peek"
inside the memory buffers and see which tables MySQL considers important enough to keep in the "blueprint" (Definition)
cache.

Query: What's in my Definition Cache?

This query ranks your databases by how many table definitions they currently have cached.

SQL

None
SELECT
OBJECT_SCHEMA,
COUNT(*) AS Cached_Tables,
SUM(COUNT(*)) OVER() AS Total_Cached_Objects
FROM performance_schema.setup_objects
GROUP BY OBJECT_SCHEMA
ORDER BY Cached_Tables DESC;

If you want to see the specific tables from a particular database (e.g., 'my_app') that are filling up the cache, use this:

SQL

None
SELECT
OBJECT_NAME as Table_Name,
OBJECT_TYPE
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'my_app';

(Note: This works because any table in the "Open" or "Definition" cache usually maintains a shared metadata lock to
prevent the table from being deleted while in use.)

Understanding the Cache "Eviction"

When the number of objects exceeds your table_definition_cache setting, MySQL uses an LRU (Least Recently
Used) algorithm.
1.​ The Metadata Request: A query asks for a table not in cache.
2.​ The Hidden Lookup: MySQL queries the hidden InnoDB tables (e.g., [Link]).
3.​ The Eviction: If the cache is full, MySQL identifies the table definition that hasn't been used for the longest time
and "kicks it out" (evicts it).
4.​ The Cost: If you query that evicted table again 10 seconds later, MySQL has to do that expensive "Internal SQL"
lookup all over again.

Summary Checklist for a Healthy Cache

If you run SHOW GLOBAL STATUS LIKE 'Open%_definitions'; and find that:
●​ Open_table_definitions is exactly equal to your table_definition_cache setting...
 ●​ AND Opened_table_definitions is constantly increasing...

...then your cache is too small. You are in a "cache thrash" cycle where MySQL is constantly deleting old definitions to
make room for new ones, forcing those hidden internal lookups to happen repeatedly.

Difference between a BluePrint in the table_definition_cache and

the file being opened in table_open_cache
To understand the difference, you can think of them as the Architecture Plan (Blueprint) vs. the Active Construction
Worker (File Handle).

The Table Definition Cache and Table Open Cache work together in a two-stage process every time a query hits a table.

1. The Blueprint: table_definition_cache

In MySQL internal code, this stores a structure called a TABLE_SHARE.
●​ What it is: A single, shared C++ object that contains everything about the table's structure—column names, data
types, indexes, and constraints.
●​ The Quantity: There is only ever one blueprint per table in memory, no matter how many users are connected.
●​ The Purpose: It prevents MySQL from having to go back to the "hidden" data dictionary tables (or .frm files in older
versions) to figure out what the table looks like.

2. The Active File: table_open_cache

In MySQL internal code, this stores a structure called a TABLE object (or a "table instance").
●​ What it is: A working instance of the table that is tied to a specific database thread. It includes the "file
descriptors" (the actual connection to the .ibd data files) and the state of where the cursor is currently reading.
●​ The Quantity: There can be many of these for a single table. If 50 users are querying the orders table at the
same time, MySQL may have 50 separate entries in the table_open_cache for that one table.
●​ The Purpose: It keeps the file handle "hot" so the OS doesn't have to open and close physical files on the disk
for every single query.

Comparison at a Glance

Feature Table Definition Cache (Blueprint) Table Open Cache (Active File)
Object Name TABLE_SHARE TABLE
Ratio 1 object per Table 1 object per Table per concurrent thread
Memory Cost Low (Small metadata object) Higher (Includes file handles and buffers)
Sharing Shared by all threads Private to a specific thread
Primary Resource RAM OS File Descriptors (open_files_limit)
How they work together (The "Library" Analogy)
Imagine a library:
1.​ The Dictionary Cache: Is the library's main database. It knows every book the library owns.
 2.​ The Table Definition Cache (table_definition_cache): Is the Photocopy of the Table of Contents. All
readers share the same information about what's inside the book.
3.​ The Table Open Cache (table_open_cache): Is the Actual Physical Book checked out to a specific desk. If
three people want to read the same book at the same time, the library needs three physical copies on three
different desks.

Summary
If you have 1,000 tables but only 10 active users, your table_definition_cache should be at least 1,000, but your
table_open_cache only needs to be large enough to hold the tables those 10 users are touching (e.g., ~100).

Estimation of Table Definition & Table Open Cache

To calculate the memory impact, you have to look at how these caches scale. The Table Definition Cache is predictable
(one per table), but the Table Open Cache can grow significantly because it scales by the number of concurrent
connections.

1. Estimating Table Definition Cache Memory

Each "blueprint" (TABLE_SHARE) in the definition cache is relatively small. On average, a table definition takes up about 4
KB to 10 KB of memory, depending on the number of columns and indexes.
●​ Formula: table_definition_cache × 8 KB (average)
●​ Example: 2,000 tables × 8 KB = ~16 MB

2. Estimating Table Open Cache Memory

This is where memory usage can spike. Each entry in the open cache is a TABLE object. These are much "heavier"
because they store record buffers, column metadata, and engine-specific handles.
●​ Formula: table_open_cache × 10 KB to 20 KB (average)
●​ Example: 4,000 open instances × 15 KB = ~60 MB

3. The SQL Query to See Real-Time Usage

You can use the Performance Schema to see exactly how much memory these structures are consuming right now. Run
this query:

SQL

None
SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS Memory_Used_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN (
'memory/sql/TABLE_SHARE', -- Table Definition Cache
'memory/sql/TABLE' -- Table Open Cache
);
When to Adjust Based on Memory
If your server has 128 GB of RAM, these caches are tiny rounding errors. However, on smaller instances (2 GB or 4 GB
RAM), you must be careful:
●​ High table_open_cache + High max_connections: If you set table_open_cache to 20,000 and have
1,000 concurrent users, MySQL could theoretically use several hundred MBs just keeping file handles open.
●​ The OS Limit: Every entry in the table_open_cache consumes a File Descriptor from your operating system.
If you increase this cache, you must also increase open_files_limit in your MySQL config, or the OS will
block MySQL from opening more tables.

Summary Checklist
●​ Is Opened_table_definitions growing? Increase table_definition_cache.
●​ Is Table_open_cache_misses growing? Increase table_open_cache.
●​ Is Memory low? Check the memory/sql/TABLE event in Performance Schema to see if these caches are
bloated.

Operation System Open file limits & Open tables

To check if your Operating System (OS) is prepared for a larger cache, we need to compare what MySQL wants to do
versus what Linux/Unix allows it to do.

Every time MySQL opens a table (to put it in the table_open_cache), it consumes at least one file descriptor. If you set
your cache to 10,000 but your OS limits MySQL to 1,024, the cache will never be able to fill up, and your performance will
suffer.

1. Check the Current Limits

Run this SQL command to see the current relationship between your cache and your OS limit:

SQL

None
SHOW VARIABLES LIKE 'open_files_limit';
SHOW VARIABLES LIKE 'table_open_cache';

2. The Golden Rule of Sizing

Ideally, your open_files_limit should be significantly higher than your table_open_cache. A common formula used by
MySQL to auto-calculate the limit is:

Based on Max Connections:​

Based on Table Cache:​

The System Default:​

A hardcoded value (often 5000 in modern versions, though this varies by OS and specific MySQL flavor).
 ●​ Why \times 2? Some storage engines (like MyISAM) use two files per table (.MYD and .MYI), and InnoDB may
need extra descriptors for temporary tables and system files.
●​ Why max_connections? Each connection to the server also consumes a file descriptor (the socket).

3. How to increase the limit

If you find that the open_files_limit is too low, you usually cannot change it inside MySQL because it is restricted by the
OS. You must change it at the system level.

For systemd-based Linux (Ubuntu, CentOS 7+, etc.):

1.​ Create an override file:​
sudo systemctl edit mysql
2.​ Add these lines:
3.​ Ini, TOML

None
[Service]
LimitNOFILE=65535

4.​ Restart MySQL:​

sudo systemctl restart mysql

Summary of the "Cache Chain"

To ensure those "hidden" data dictionary lookups stay fast, ensure your chain isn't broken:
1.​ OS Level: LimitNOFILE (Must be high enough to hold all files).
2.​ MySQL File Level: open_files_limit (Matches the OS).
3.​ Active Session Level: table_open_cache (Enough for all concurrent threads).
4.​ Blueprint Level: table_definition_cache (Enough for all unique tables).
5.​ Metadata Level: Dictionary Cache (The internal layer that feeds the blueprints).

Health Check Scripts

Here is a comprehensive "Health Check" script. This query pulls data from the Performance Schema and System Variables
to give you a bird's-eye view of your metadata cache health.

MySQL Cache Health Monitor

Run this in any MySQL client (Workbench, CLI, etc.):

SQL

None
SELECT
'Table Open Cache' AS Cache_Type,
@@table_open_cache AS Configured_Size,
 (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Open_tables') AS Currently_Used,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Table_open_cache_misses') AS Misses_Total
UNION ALL
SELECT
'Table Definition Cache',
@@table_definition_cache,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Open_table_definitions'),
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Opened_table_definitions')
UNION ALL
SELECT
'OS File Limit',
@@open_files_limit,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
'Innodb_num_open_files'),
'N/A';

How to read this report:

1.​ Table Open Cache Row:
○​ If Currently_Used is exactly equal to Configured_Size, MySQL is likely "killing" old file handles to make
room for new ones.
○​ Check Misses_Total. If this is increasing by hundreds every minute, you need to increase
table_open_cache.
2.​ Table Definition Cache Row (The "Blueprint" Cache):
○​ If Currently_Used equals Configured_Size, the "Dictionary Cache" is likely having to work harder because
the high-level blueprints are being evicted.
○​ Opened_table_definitions should ideally stay flat after the server has warmed up. If it's high, increase
table_definition_cache.
3.​ OS File Limit Row:
○​ If Innodb_num_open_files is very close to @@open_files_limit, your operating system is about to
"choke" MySQL's ability to open files, regardless of how big you set your caches.

Final Pro-Tip: The "Wait" Factor

If you want to see if these cache issues are actually slowing down your users right now, run:

SQL

None
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%table_open_cache%' OR EVENT_NAME LIKE '%table_definition_cache%';

If the SUM_TIMER_WAIT is high, it means your application is literally sitting still while MySQL struggles to clear out and
reload its internal metadata.
Significance of opened_table_definition status variable
Opened_table_definitions is a cumulative status counter that tracks how many times MySQL had to perform a full
metadata lookup because a table's "blueprint" was not available in memory.

While Open_table_definitions (without the 'ed') tells you how many are in the cache right now, Opened_table_definitions
tells you how many times you've had to go to the disk (or the data dictionary) since the server started.

Why does this number increase?

Every time a query references a table, MySQL follows this logic:
1.​ Check Cache: Is the blueprint (the TABLE_SHARE object) already in the table_definition_cache?
2.​ Cache Hit: If yes, use it. Opened_table_definitions does not increase.
3.​ Cache Miss: If no, MySQL must retrieve the metadata from the Data Dictionary (the hidden InnoDB tables in 8.0 or
.frm files in 5.7).
4.​ The Increment: Once the metadata is retrieved and a new "blueprint" object is created, Opened_table_definitions
increments by 1.

Is a high number bad?

Not necessarily, but its rate of growth matters.
●​ At Startup: It is normal for this number to climb quickly as the server "warms up" and loads the definitions of your
active tables for the first time.
●​ During Steady State: If the server has been up for days and this number is still climbing rapidly, it means your
table_definition_cache is too small.

The "Cache Thrashing" Warning

If Opened_table_definitions is growing while your Open_table_definitions (current) is stuck at its maximum limit, you are
experiencing Cache Thrashing.

The Cycle:

1.​ Your cache is full (e.g., limit is 2,000).

2.​ You query table #2,001.
3.​ MySQL evicts an old table from memory to make room.
4.​ MySQL performs a lookup for the new table and increments Opened_table_definitions.
5.​ Ten seconds later, you query the table that was just evicted. MySQL has to do the lookup again.

How to diagnose
You can check the health of your definition cache by comparing the number of "opens" to the total number of queries:

SQL

None
SHOW GLOBAL STATUS LIKE 'Opened_table_definitions';
SHOW GLOBAL STATUS LIKE 'Queries';
Rule of Thumb: If Opened_table_definitions divided by Uptime is greater than 1 or 2 per second on a steady-state
server, you should increase your table_definition_cache setting in [Link].

“As per my understanding Opened_table_definitions indicates how many times the table_definition_cache hit were missed
, it does not tell if the subsequent lookup in the data dictionary also missed . I am looking at a status variable that tells me
that even the lookup in the data dictionary was missed which resulted in a subsequent query on the hidden internal tables.
“

You are making a very sharp distinction that gets to the heart of the MySQL 8.0 architecture.

You are correct: Opened_table_definitions only tracks misses at the "Server Layer" (the Table Definition Cache). It
doesn't tell you if the "Data Dictionary Layer" (the Dictionary Object Cache) had the object ready or if it had to go all the
way to the hidden internal tables on disk.

To find what you are looking for—the indicator that a lookup failed even in the Data Dictionary—you need to look at the
Performance Schema and InnoDB metrics, as there is no single "top-level" status variable for this.

1. The Variable You Are Looking For: Dictionary memory allocated

While not a simple counter, the most direct indicator of "Dictionary Misses" resulting in internal SQL lookups is found in the
InnoDB Monitor.

When a lookup fails in the Data Dictionary cache, InnoDB must perform a "row fetch" from the hidden system tables. This
causes a spike in the memory allocated to the dictionary as new objects are built from the raw data.

Run this command:

SQL

None
SHOW ENGINE INNODB STATUS;

Look for the section titled BUFFER POOL AND MEMORY. Underneath, you will see:

DiIf this number is constantly increasing, it is a smoking gun that your Data Dictionary cache is missing and the
server is repeatedly querying the hidden tables to rebuild these objects.

2. Performance Schema: The "Real" Miss Counter

In MySQL 8.0, the Dictionary Object Cache is partitioned. You can see misses for the internal data dictionary lookups by
querying the memory_summary_global_by_event_name table.

If the server is firing internal SQL to retrieve data, it has to allocate memory for those new "Dictionary Objects." You want to
track the COUNT_ALLOC (total allocations) for dictionary objects. If this count is high relative to your uptime, it indicates
frequent "hidden table" queries.

SQL

None
SELECT
EVENT_NAME,
COUNT_ALLOC AS Total_Internal_Lookups,
 CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS Current_Cache_Size_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/sql/dd::%';

3. Visualizing the "Double Miss"

To answer your specific point about the "subsequent lookup," here is how the failure chain looks:
1.​ Level 1 Miss: Opened_table_definitions increments. (Server says: "I don't have the blueprint.")
2.​ Level 2 Check: MySQL checks the Dictionary Object Cache (DD Cache).
3.​ Level 2 Miss: (This is what you're asking about). If the DD Cache also misses, it triggers a Storage Engine Read.
4.​ The Result: The InnoDB engine executes an internal "mini-transaction" to read from the hidden [Link] or
[Link] tables.

4. How to detect the "Internal Query" specifically

If you want to see these internal queries happening, you can enable the Performance Schema statement
instrumentation. These internal lookups often show up as statements with no user associated with them, or under the
sql/dd instrument.

SQL

None
SELECT
EVENT_NAME,
COUNT_STAR AS Times_Accessed,
AVG_TIMER_WAIT / 1000000000 AS Avg_Wait_ms
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/ddl%';

Summary for your Troubleshooting:

There is no variable named Dictionary_cache_misses. Instead:
●​ Opened_table_definitions = The server-level "I need a blueprint" signal.
●​ memory/sql/dd:: (Performance Schema) = The signal that a blueprint was created (meaning a dictionary lookup
occurred).
●​ Dictionary memory allocated (InnoDB Status) = The signal that the dictionary is growing/churning due to misses.

Concurrent DB creation and drop - Out Of Memory Kill

MySQL with InnoDB engine has three types of memory allocators , one belongs to the server module mem_root
allocator and two belongs to the InnoDB which are ut_allocator and mem_heap_allocator.
The contributor to the OOM kills in our databases is for the mem_root allocator and the root cause which I
have found are these:
1. Collections of a lot of objects of class THD (one object is for one connection) --- either due to locks or
due to connection burst.
2. Few THD objects with each THD with bloated instance variable of object MEM_ROOT called MAIN_MEM_ROOT due
to large SQLs like lots of values in IN clause or lots of values in the OR clause
3. One THD object's mem root growing crazy due to multiple ITEM objects inside MAIN_MEM_ROOT ... like that of
multi-value index creation
Now we have another scenario where in a test pipeline we concurrently create a lot of databases and drop lots
of databases and drop lots of databases and that too results in OOM , can you tell me what portion of
MEM_ROOT gets hogged . As far as I know mem_root is used for
statement parsing,
query execution plans, and
thread context (THD).

It sounds like you’ve done a deep dive into the MySQL memory architecture. You are absolutely correct that the
MEM_ROOT (the server-level arena allocator) is often the culprit behind OOM kills because, unlike the InnoDB buffer pool,
it isn't capped by a single global variable. It grows dynamically based on workload.

When you are rapidly creating and dropping databases in a concurrent test pipeline, the OOM is likely triggered
by the Data Dictionary (DD) Cache and Table Definition Cache, which utilize MEM_ROOT for metadata
management.

Why CREATE/DROP DATABASE Hogs MEM_ROOT

While you identified parsing and execution as primary users, the metadata layer is a massive consumer of MEM_ROOT
during DDL-heavy operations. Here is where the memory is likely getting trapped:

1. Table Share (TABLE_SHARE) Objects

Each time a database is created and tables within it are accessed, MySQL creates a TABLE_SHARE object.
●​ The Mechanism: This object stores the metadata (columns, types, constraints) for a table.
●​ The Memory Link: These objects are allocated using a MEM_ROOT. In a "drop and recreate" loop, if the
table_definition_cache is set too high or if there are active references preventing immediate cleanup, these shares
linger in memory.

2. The Dictionary Client and DD Objects

Since MySQL 8.0, the Data Dictionary resides in InnoDB, but the server-layer cache uses MEM_ROOT.
●​ Object Bloat: When you create a database, MySQL generates entries for the schema, tables, and potentially
thousands of objects (indexes, columns).
●​ The Pipeline Issue: If your pipeline creates a database, populates it, and then drops it, the THD (Thread)
performing the drop must load all those object definitions into its local MEM_ROOT to process the drop. In a
high-concurrency environment, you have multiple threads all holding large metadata caches simultaneously.

3. Event and Procedure Caches

If your database creation includes routines (stored procedures, triggers, or events), these have their own MEM_ROOT
requirements.
●​ When a database is dropped, MySQL must parse and remove these associated objects.
●​ The parsing of the drop dependencies causes a spike in the THD::main_mem_root similar to the "large SQL"
scenario you mentioned in point #2 of your findings.

The "Zombie" Memory Effect

In a concurrent pipeline, you might be hitting a specific MEM_ROOT behavior: Freeing memory doesn't always return it
to the OS.

MEM_ROOT allocates in "blocks." If a block is only 10% full because one small object is still "alive" (perhaps a temporary
lock or a reference in the table_definition_cache), the entire block remains allocated to the process. With enough
concurrent CREATE/DROP threads, these "mostly empty" blocks aggregate until the OOM killer steps in.
How to Verify and Mitigate
To confirm if this is the TABLE_SHARE or Metadata cache, you can query the Performance Schema:

SQL

None
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS MB_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%memory/sql/TABLE_SHARE::mem_root%'
OR EVENT_NAME LIKE '%memory/sql/dd::%';

Recommendations for your Pipeline:

●​ Lower table_definition_cache: This forces MySQL to release metadata MEM_ROOT structures more
aggressively.
●​ Lower table_open_cache: Reduces the number of concurrent TABLE objects held in memory.
●​ Check host_cache: Though less likely, high-frequency connection cycles from a test runner can sometimes bloat
the host cache MEM_ROOT.

Memory Starvation Situation & Memory Component Analysis

GCP Metrics Console screenshot

Based on the screenshot you provided from your monitoring dashboard, here is the breakdown of your MySQL memory
allocation as of January 30, 2026.

Totaling these figures, your instance is using roughly 1.215 GiB across these specific categories. Here is what each
component represents:

Memory Breakdown
●​ innodb_memory (0.611 GiB): This is your largest consumer, which is healthy for a MySQL instance. It primarily
consists of the Buffer Pool, where InnoDB caches data and indexes. At ~600 MiB, it suggests a smaller or
development-tier instance.
●​ sql_table (0.401 GiB): This represents the memory used by the Table Definition Cache and Table Open
Cache. Using 400 MiB here is actually quite high relative to your Buffer Pool size. It suggests you have a very
large number of tables (thousands) being accessed or kept open.
●​ sql_dd (0.121 GiB): This is the Data Dictionary cache. It stores metadata about database objects. Since MySQL
8.0, this is integrated more tightly into the server memory.
 ●​ sql_thd_main (0.077 GiB): This represents memory allocated to the main thread and general connection
overhead. This is a very modest amount, indicating the "base" cost of the server running.
●​ innodb_row0sel (0.005 GiB): This is a specific InnoDB memory heap used for row-level operations (like row
searches/selection). It’s negligible in this context.

Analysis & Observations

1.​ The Table Cache Disparity: Usually, innodb_memory dwarfs everything else by a factor of 10 or more. In your
case, sql_table is about 65% the size of your InnoDB memory.​
Tip: If you aren't intentionally running a multi-tenant DB with tens of thousands of tables, you might check if
table_open_cache or table_definition_cache are set unnecessarily high.
2.​ Scale: These numbers are in GiB, but the total footprint is quite small (just over 1 GB). This looks like a
[Link] or similar small-scale cloud instance.
3.​ Stability: The timestamp shows 6:00 AM. If this is a baseline, it looks very stable. If this was a spike, the
sql_table growth is the most likely culprit to investigate.

The memory metrics are derived from performance_schema.memory_summary_global_by_event_name table. The

low-level MySQL "instruments" can be grouped into the human-readable categories shown below.

This SQL maps the internal MySQL EVENT_NAME to the intelligible labels.

The Mapping Query

SQL

None
SELECT
CASE
WHEN event_name LIKE 'memory/innodb/%' THEN 'innodb_memory'
WHEN event_name LIKE 'memory/sql/TABLE%'
OR event_name LIKE 'memory/sql/table_share%' THEN 'sql_table'
WHEN event_name LIKE 'memory/sql/dd%' THEN 'sql_dd'
WHEN event_name LIKE 'memory/sql/THD::main%' THEN 'sql_thd_main'
ELSE 'other'
END AS category,
SUM(current_number_of_bytes_used) / 1024 / 1024 / 1024 AS size_gib
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY category;

Instrumentation Breakdown
Here is how GCP maps those specific dashboard labels to the underlying Performance Schema instruments:

MySQL Instrument Prefix /

GCP Label Pattern Description
innodb_memor memory/innodb/* Includes the Buffer Pool, Change Buffer, Adaptive Hash Index, and Log
y Buffer.
memory/sql/TABLE &
sql_table memory/sql/table_share Memory used by the Table Definition Cache and the Table Open Cache.
Memory for the Data Dictionary (introduced in MySQL 8.0) which
sql_dd memory/sql/dd::* stores object metadata.
Memory allocated to the main execution thread and basic connection
sql_thd_main memory/sql/THD::main overhead.
innodb_row0se
l memory/innodb/row0sel A specific heap used for row selection and search operations.

Requirements for this data

If after execution of the SQL there are empty results, it's likely because memory instrumentation is not enabled on your
instance. By default, Cloud SQL only enables this for instances with 15GB+ RAM. To enable it manually, you must:
1.​ Set the database flag performance_schema to on (requires restart).
2.​ Enable the specific memory instruments at runtime:

None
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

my_alloc memory allocator

In the architecture of MySQL, my_alloc belongs to the Server Layer (specifically the "Base Library" or "Support
Function" tier), though it is frequently utilized by the Storage Engine layer as well.

To understand its placement, it helps to look at the hierarchy of MySQL memory management:

1. Its Identity: The General Purpose Allocator

my_alloc is the primary implementation of MySQL’s MEM_ROOT structure. It is a block-based memory allocator
designed for "arena" allocation. Instead of doing thousands of tiny malloc() calls, MySQL uses my_alloc to grab a large
chunk of memory and then sub-allocate it for specific tasks.

2. Layer Placement
●​ Primary Home: Server Layer. It is part of the mysys library. This is the foundation that the SQL Layer (Parsers,
Optimizers, Query Execution) sits upon.
●​ Secondary Usage: Storage Engine Layer. Because MySQL is pluggable, storage engines like InnoDB or
MyISAM often "borrow" these server-level utilities. However, InnoDB has its own highly specialized internal
allocators (like buf_pool and mem_heap) for its core operations.

Why it exists in the Server Layer

The Server Layer uses my_alloc (MEM_ROOT) for tasks that have a defined lifecycle, such as:
 ●​ Parsing a Query: The memory used to build a "Parse Tree" is allocated via my_alloc. Once the query is finished,
the entire block is freed at once.
●​ Thread Buffers: Information specific to a user connection.

How it differs from InnoDB's Memory

While my_alloc is a generic tool, the Storage Engine layer (specifically InnoDB) usually prefers its own dedicated memory
management for performance:

Feature my_alloc (Server) InnoDB Internal Allocators

Typical Use Query parsing, result sets, small caches. Buffer pool, redo logs, adaptive hash index.
Strategy MEM_ROOT (Arena allocation). Buffer chunks and specialized heaps.
Monitoring memory/sql/MEM_ROOT memory/innodb/buf_pool

To see how much memory is being allocated by my_alloc (specifically the MEM_ROOT structures it manages), you need
to query the Performance Schema.

In MySQL's instrumentation, my_alloc operations are typically tracked under instruments containing the string
MEM_ROOT.

1. The Global Summary Query

This query will show you the total memory currently held by all MEM_ROOT (arena) allocations across the entire server.

SQL

None
SELECT
EVENT_NAME,
COUNT_ALLOC,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_mb_used,
HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024 AS peak_mb_used
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%MEM_ROOT%' ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

2. The sys Schema "Quick View"

If you have the sys schema enabled (standard on MySQL 5.7+ and 8.0+), you can get a more human-readable version:

SQL

None
SELECT
event_name,
current_alloc,
high_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE '%MEM_ROOT%';
What these results tell you
When you see MEM_ROOT in the results, it is usually categorized by the specific part of the server using it:
●​ memory/sql/THD::main_mem_root: This is the most common one. It represents the memory used for the
current state of user connections (query parsing, local execution variables).
●​ memory/sql/sp_head::main_mem_root: Memory used for stored procedures and triggers.
●​ memory/sql/TABLE::mem_root: Memory used for table-specific metadata and caches.

Important: Why you might see "0"

If the queries return no rows or all zeros, it is likely because memory instrumentation is not enabled. To enable it for
MEM_ROOT at runtime without restarting:

SQL

None
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE 'memory/sql/%MEM_ROOT%';

Note: This will only start tracking allocations made after the command is run. For a full picture, you would need to add
performance-schema-instrument='memory/%=ON' to your [Link] and restart.

Prefix What it monitors

wait/ Events that take time, like disk I/O or locking.
Memory allocation (like the my_alloc / MEM_ROOT we
memory/ discussed).
stage/ Steps in the query execution process (e.g., "Sending data").
statement/ High-level SQL commands (SELECT, INSERT, etc.).

Top-Level Grouping of Instruments with Not Null Documentation

select distinct event_name
from (SELECT SUBSTR(name, 1, LOCATE('/', name, LOCATE('/', name) + 1) - 1)
event_name
from performance_schema.setup_instruments
where documentation is not null) x
where event_name is not null;
+---------------------------+
| event_name |
+---------------------------+
| wait/synch |
| statement/sql |
| statement/abstract |
| memory/performance_schema |
| memory/sql |
| memory/client |
| memory/innodb |
+---------------------------+
It is no coincidence that these specific instruments are the ones with documentation. They represent the core engine
components and the safety-critical monitoring of the server. In the MySQL source code, these are the "First Class
Citizens"—the instruments developers care most about for diagnostic accuracy.

Here is the "essence" of why these specific top-level groups are prioritized:

1. The "Integrity" Group: wait/synch

This is arguably the most important instrument for MySQL performance tuning.
●​ The Essence: It tracks internal traffic control.
●​ Why it's documented: When multiple CPU threads try to access the same memory (like the Buffer Pool), they
must wait for a "Mutex" or "RW-Lock." If this is high, your server is "fighting itself." Documentation here helps
DBAs distinguish between a slow disk and a code bottleneck.

2. The "Interface" Group: statement/sql & statement/abstract

This is how the outside world talks to the database.
●​ The Essence: Tracking the intent and execution of queries.
●​ Why it's documented: * statement/sql covers the final, parsed commands (SELECT, INSERT).
○​ statement/abstract covers commands that are still being identified.
○​ Documentation here is vital because this is the primary way users measure "Latency" (how long did my
query take?).

3. The "Self-Monitoring" Group: memory/performance_schema

●​ The Essence: The cost of observation.
●​ Why it's documented: The Performance Schema itself uses RAM to store all these metrics. MySQL documents
this so you can see exactly how much memory you are "sacrificing" to enable monitoring. It prevents the
observer effect from becoming a hidden memory leak.

4. The "Big Three" Memory Consumers:

These three represent the vast majority of your RAM footprint:
●​ memory/sql (The Brain):
○​ Essence: The SQL Layer’s workspace.
○​ Focus: Query parsing, sorting (sort_buffer), joins, and the MEM_ROOT allocations we discussed. It’s the
"Server Layer" memory.
●​ memory/innodb (The Muscle):
○​ Essence: The Data Engine’s cache.
○​ Focus: The Buffer Pool. Since this can be 80% of your total system RAM, it is the most heavily
documented and scrutinized instrument in the system.
●​ memory/client (The Bridge):
○​ Essence: Internal-to-External communication.
○​ Focus: Used when the server needs to act as a client (e.g., Replication heartbeats or Federated queries).
The "Documentation" Strategy
The reason these have values in the DOCUMENTATION column while others don't is Maturity. These instruments were
the first to be implemented in the Performance Schema. The developers added descriptive strings for the "pillars" of the
system, while thousands of more granular instruments (like memory/vio or memory/csv) were added later by different
teams who relied on the name itself being the documentation.

Top-Level Grouping of Instruments with Null Documentation

select distinct event_name
from (SELECT SUBSTR(name, 1, LOCATE('/', name, LOCATE('/', name) + 1) - 1)
event_name
from performance_schema.setup_instruments
where documentation is null) x
where event_name is not null;
Output:
+---------------------------+
| event_name |
+---------------------------+
| wait/synch |
| wait/io |
| wait/lock |
| stage/sql |
| stage/mysys |
| stage/innodb |
| statement/sql |
| statement/abstract |
| statement/sp |
| statement/scheduler |
| statement/com |
| memory/sql |
| memory/client |
| memory/vio |
| memory/mysys |
| memory/csv |
| memory/myisam |
| memory/innodb |
| memory/memory |
| memory/myisammrg |
| memory/temptable |
| memory/blackhole |
| memory/component_sys_vars |
+---------------------------+
24 rows in set (0.02 sec)

These top-level groupings represent the major functional areas where MySQL is spending time (waiting), tracking
progress (staging), or holding data (memory).

By curtailing them to the second /, you are looking at the Functional Subsystems of the server. Here is what they mean:

1. The wait/ Category

These instruments track events that cause a thread to pause.
 ●​ wait/synch: Internal synchronization. This monitors mutexes, rw-locks, and conditions used by threads to
coordinate access to shared data. High values here indicate internal "traffic jams" (contention).
●​ wait/io: Physical input/output. This tracks the time spent waiting for the disk (reading data files, writing to redo
logs, or socket communication).
●​ wait/lock: High-level table or row locks. Unlike synch (which is for code), this is for data-level locking.

2. The stage/ Category

These track the progress of a query through its lifecycle.
●​ stage/sql: Steps in the SQL layer, such as "Checking permissions," "Opening tables," or "Sorting result sets."
●​ stage/mysys: Generic system-level operations performed by the mysys library (e.g., file initialization).
●​ stage/innodb: Specific long-running tasks inside the InnoDB engine, like an "Alter table" progress or
background buffer flushing.

3. The statement/ Category

These track the execution of specific command types.
●​ statement/sql: Standard SQL queries (SELECT, UPDATE, etc.).
●​ statement/abstract: Parent category for commands that haven't been fully resolved yet.
●​ statement/scheduler: Events related to the MySQL Event Scheduler (cron-like tasks).
●​ statement/com: "Command" protocol events. These are low-level binary commands from the client, like
COM_QUIT, COM_PING, or COM_QUERY.

4. The memory/ Category

These track where your RAM is currently being held.
●​ memory/sql: Memory used by the server layer (e.g., query parser, table cache, MEM_ROOT).
●​ memory/innodb: The largest chunk; includes the Buffer Pool, log buffers, and engine-specific heaps.
●​ memory/temptable: Memory used specifically by the TempTable storage engine for internal temporary tables
created during complex GROUP BY or DISTINCT queries.
●​ memory/vio: Virtual I/O. Memory used for the network communication layer (packets being sent/received).
●​ memory/mysys: Memory for low-level utility structures (like file descriptors and option parsing).
●​ memory/client: Internal memory for the C client library (often used by FEDERATED tables).
●​ memory/[storage_engine]: (csv, myisam, blackhole, memory) Memory used by specific non-InnoDB engines.
●​ memory/component_sys_vars: Memory used by the system variable infrastructure for loaded
components/plugins.

Why this is useful

If you see a memory spike, looking at these curtailed names tells you immediately if the problem is in the Storage
Engine (memory/innodb) or the Server Layer (memory/sql).

If memory/temptable is the highest, for example, it tells you that your memory issue isn't a "tuning" problem, but rather
that a specific user is running a query with a massive join that is forcing MySQL to create huge temporary tables.
Usual Causes of Spike in Data Dictionary Memory
In a MySQL environment, the Data Dictionary (the TABLE_SHARE blueprints) is generally stable, but certain operations
can cause a sharp spike in its memory footprint (TABLE_SHARE::mem_root) or the session-specific handles that interact
with it (THD::main_mem_root).

Here are the primary operations that trigger these spikes:

1. Mass Parallel Opening of Unique Tables

In databases with a "multi-tenant" or "sharded" schema design (thousands of tables like user_data_1, user_data_2, etc.),
memory spikes when:
●​ The Scenario: A maintenance script or a complex reporting query runs and touches thousands of unique tables
for the first time since a restart.
●​ The Impact: The Table_cache_manager must instantiate a new TABLE_SHARE and its associated mem_root for
every single table. If table_definition_cache is set very high, this memory stays resident until the cache is full and
eviction begins.

2. Large Scale ALTER TABLE Operations

Running an ALTER TABLE (especially "copy" alters) causes a metadata surge:
●​ The Scenario: Changing a column type or adding an index on a large table.
●​ The Impact: MySQL creates a temporary "shadow" version of the table. For the duration of the operation, the
manager must hold the TABLE_SHARE for both the original table and the new version, doubling the metadata
overhead for that specific entity.

3. Deeply Nested Views and Stored Procedures

The Data Dictionary doesn't just store physical table structures; it stores the parsed definitions of views.
●​ The Scenario: Querying a view that is built upon other views (nested levels).
●​ The Impact: To resolve the query, the manager must load the TABLE_SHARE for every underlying table in the
hierarchy. If a view references 50 tables, querying it once forces 50 metadata objects into the cache
simultaneously.

4. Excessive Temporary Table Creation

Internal temporary tables created by the optimizer for GROUP BY or UNION operations can spike memory:
●​ The Scenario: Complex queries that cannot be resolved in-memory and require the creation of internal
temporary tables.
●​ The Impact: While these don't always use a full TABLE_SHARE in the same way a physical table does, the
metadata required to track these transient structures is allocated from the mem_root, often appearing as a spike
in the SQL layer's management roots.

5. High Connection Concurrency (Handle Bloat)

Even if the number of tables is small, a spike in connections can bloat the manager's memory:
●​ The Scenario: A sudden "thundering herd" of 1,000 connections all executing the same query.
 ●​ The Impact: While the TABLE_SHARE (blueprint) is shared and only takes memory once, each of the 1,000
connections must create its own TABLE handle. These handles consume memory in the THD::main_mem_root,
which can aggregate into a multi-gigabyte spike across the server.

Summary of Memory Targets

Operation Primary Target Scope

New Table Access TABLE_SHARE::mem_root Global / Persistent
ALTER TABLE TABLE_SHARE::mem_root Global / Transient
High Concurrency THD::main_mem_root Session / Transient

Analysis of Repeat OOM Kill 2024

Reference: Analysis

Resource Evaluation
MySQL Database Instance Approximate Memory Components Sizes
Instance Usable Global Buffer Global Caches Performance Individual Memory for
Resource Memory (InnoDB , Log, (Dictionary Schema Connection Client thread
Key..) Cache, Thread (thread cache, net growth -
Cache …) buffer) individual conn

vCPU:48 & 40​ 138GB 109+ GB .5 to 1 GB 1.1 to 1.6GB Flexible - 100MB to ~25 GB
Memory:150GB 10GB +

vCPU:32 ​ 110GB 87+ GB .5 to 1 GB 1.1 to 1.6GB Flexible - 100MB to ~20 GB

Memory:120GB 10GB +

vCPU:16 ​ 55.1GB 44+ GB .5 to 1 GB 1.1 to 1.6GB Flexible - 100MB to ~7 GB

Memory:60GB 10GB +

vCPU:8 ​ 33.1 GB 26+ GB .5 to 1 GB 1.1 to 1.6GB Flexible - 100MB to ~4 GB

Memory:36GB 10GB +

-​ It can be seen from Column 7 that available memory for client threads is less than 10GB for database instances
with vCPU 8 and 16 which are ~4GB and ~7GB respectively. Hence, the available heap memory gets exhausted
very often , with mysqld process getting the highest OOM_SCORE with OOM enabled through OOM_ADJ
parameter value which results in the mysqld process with the highest OOM score getting killed.​

[ LLM supplement:
In Linux, the "OOM Killer" isn't actually a separate, standalone OS process like systemd or init. Instead, it is
a subroutine within the Linux Kernel itself.

Specifically, it is a function that resides in the kernel's memory management subsystem. When the system's
RAM and swap are completely exhausted, the kernel triggers this mechanism to prevent a total system
crash.

How the OOM Killer Operates

Since it isn't a process, you won't find it by running ps aux. Here is how the kernel handles the "execution"
of a process:
 1.​ The Trigger: A process requests more memory (via malloc or sbrk), but the kernel realizes there are
no free pages left and no more pages can be reclaimed from the cache.
2.​ The Selection: The kernel scans all existing processes and calculates an oom_score for each.
3.​ The Kill: The kernel sends a SIGKILL (Signal 9) to the process with the highest score to instantly
free up its memory.

The Scoring System (oom_score)

The kernel decides who dies based on a points system. You can actually check the "badness" score of any
process by looking at its entry in the /proc filesystem:
●​ /proc/[PID]/oom_score: The actual score. Higher numbers mean the process is more likely to be
killed.
●​ /proc/[PID]/oom_score_adj: A value (between -1000 and 1000) that allows you to manually
"protect" or "sacrifice" a process. Setting this to -1000 makes the process unkillable by the OOM
Killer.

Key Characteristics
●​ Level: Kernel-space (not User-space).
●​ Goal: Sacrificing the "bad" process to save the OS (Stability over Data Integrity).
●​ Logging: When it strikes, it leaves a trail in the system logs. You can see which process was killed
and why by running:​
dmesg | grep -i oom or checking /var/log/syslog.

-​ Column Number 6 which is Individual Connection (thread cache, net buffer) has variable amount of memory
requirement from 100MB to 10GB+ ​

-​ Some of the high memory demands for client threads comes from:​

-​ Net Buffer which stores the records requested by clients till its returned through multiple network round
trips.​

-​ Each connection THD (THread Descriptor) structure elements like:

-​ Session_trackerr: It stores the various session related information till the connection is closed.
Since , in our environment (POLARIS or RSC) , we persist connections across sessions , the
Session tracker variable holds a lot of data.
-​ Main_mem_root: MySQL allocates root memory to each connection. ​
Allocation:When a query is executed, various parts of the server may need to allocate memory (e.g.,
parsing the query, creating execution plans, temporary data structures). Instead of allocating each of
these separately on the heap, they are allocated from the main_mem_root.​
Deallocation: When the query is finished, rather than calling free() on each piece of allocated
memory, the server simply releases all memory associated with the main_mem_root. This is very
efficient because it minimizes the overhead of managing individual memory allocations.​

-​ For THD structure:​

select event_name,
(LOW_NUMBER_OF_BYTES_USED) low_used,
(HIGH_NUMBER_OF_BYTES_USED) high_used,
(CURRENT_NUMBER_OF_BYTES_USED) current_used,
(HIGH_NUMBER_OF_BYTES_USED-LOW_NUMBER_OF_BYTES_USED) diff_used,
 (HIGH_NUMBER_OF_BYTES_USED-LOW_NUMBER_OF_BYTES_USED)/CURRENT_NUMBER_OF_BYTES_USED
"X_times_current_used"
from performance_schema.memory_summary_global_by_event_name where event_name like
'memory/sql/THD%'
order by 6 desc;

+-------------------------------------------------+----------+-------------+--------------+-------------+----------------------+
| event_name | low_used | high_used | current_used | diff_used | X_times_current_used |
+-------------------------------------------------+----------+-------------+--------------+-------------+----------------------+
| memory/sql/THD::Session_tracker | 0 | 10342365310 | 145740 | 10342365310 | 70964.4937 |
| memory/sql/THD::Session_sysvar_resource_manager | 0 | 90182186 | 42700 | 90182186 | 2111.9950 |
| memory/sql/THD::variables | 0 | 1397760 | 124800 | 1397760 | 11.2000 |
| memory/sql/thd_timer | 0 | 969360 | 87024 | 969360 | 11.1390 |
| memory/sql/THD::handler_tables_hash | 0 | 91800 | 16800 | 91800 | 5.4643 |
| memory/sql/THD::db | 0 | 239569 | 74825 | 239569 | 3.2017 |
| memory/sql/THD::main_mem_root | 0 | 278709592 | 191018432 | 278709592 | 1.4591 |
| memory/sql/THD::transactions::mem_root | 0 | 0 | 0 | 0 | NULL |
| memory/sql/THD::sp_cache | 0 | 0 | 0 | 0 | NULL |
+-------------------------------------------------+----------+-------------+--------------+-------------+----------------------+
9 rows in set, 4 warnings (0.02 sec)

mysql> select name,DOCUMENTATION from performance_schema.setup_instruments where name

like 'memory/sql/THD%';
+-------------------------------------------------+------------------------------------------------+
| name | DOCUMENTATION |
+-------------------------------------------------+------------------------------------------------+
| memory/sql/THD::transactions::mem_root | Transaction context information per session. |
| memory/sql/THD::main_mem_root | Main mem root used for e.g. the query arena. |
| memory/sql/THD::sp_cache | Per session cache for stored programs. |
| memory/sql/THD::db | Name of currently used schema. |
| memory/sql/THD::variables | Per session copy of global dynamic variables. |
| memory/sql/THD::handler_tables_hash | Hash map of tables used by HANDLER statements. |
| memory/sql/thd_timer | Thread timer object. |
| memory/sql/THD::Session_tracker | NULL |
| memory/sql/THD::Session_sysvar_resource_manager | NULL |
+-------------------------------------------------+------------------------------------------------+
9 rows in set (0.02 sec)

-​ For NET:buffer structure

​
select event_name,
(LOW_NUMBER_OF_BYTES_USED) low_used,
(HIGH_NUMBER_OF_BYTES_USED) high_used,
(CURRENT_NUMBER_OF_BYTES_USED) current_used,
(HIGH_NUMBER_OF_BYTES_USED-LOW_NUMBER_OF_BYTES_USED) diff_used,
(HIGH_NUMBER_OF_BYTES_USED-LOW_NUMBER_OF_BYTES_USED)/CURRENT_NUMBER_OF_BYTES_USED
"X_times_current_used"
from performance_schema.memory_summary_global_by_event_name
order by 6 desc limit 5;

+-------------------------------------------+----------+--------------+--------------+--------------+----------------------+
| event_name | low_used | high_used | current_used | diff_used | X_times_current_used |
+-------------------------------------------+----------+--------------+--------------+--------------+----------------------+
| memory/sql/Filesort_buffer::sort_keys | 0 | 837747755 | 3338 | 837747755 | 250972.9643 |
| memory/sql/Filesort_info::record_pointers | 0 | 16036130 | 80 | 16036130 | 200451.6250 |
| memory/sql/THD::Session_tracker | 0 | 10341162955 | 96813 | 10341162955 | 106815.8507 |
| memory/sql/NET::buff | 0 | 163145633783 | 1596815 | 163145633783 | 102169.4021 |
| memory/vio/vio | 0 | 2866850560 | 68688 | 2866850560 | 41737.2840 |
+-------------------------------------------+----------+--------------+--------------+--------------+----------------------+
5 rows in set, 296 warnings (0.02 sec)
References
1.​ THD class & mem_root member
2.​ Multi-Value Index Creation Induced OOM Kills