Use MySQL Performance Schema to Find Memory Leaks

MySQL can sometimes experience unexpected memory growth, which often points to memory leaks within the database server, its storage engines, or installed plugins. This article provides a straightforward, step-by-step guide on how to enable, configure, and query the MySQL Performance Schema to track memory allocations over time, analyze usage patterns, and successfully pinpoint the source of memory leaks.

Step 1: Enable the Performance Schema

The Performance Schema must be enabled at the global level. It is enabled by default in MySQL 5.7 and 8.0, but you should verify its status.

Run the following query to check if it is active:

SHOW VARIABLES LIKE 'performance_schema';

If the value is OFF, you must enable it in your MySQL configuration file (my.cnf or my.ini) under the [mysqld] section and restart the server:

performance_schema = ON

Step 2: Enable Memory Instrumentation

By default, MySQL does not enable all memory instruments to save resources. To track memory leaks, you must explicitly enable the memory instrumentation thread.

Run this SQL statement to enable all memory-related instruments:

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

To make sure these settings persist across restarts, you can add the following line to your my.cnf file:

performance-schema-instrument = 'memory/%=COUNTED'

Step 3: Query Memory Usage Tables

The Performance Schema stores memory allocation data in several summary tables. The most useful table for a quick overview is memory_summary_global_by_event_name.

To see which event names are currently consuming the most memory, run:

SELECT 
    EVENT_NAME,
    COUNT_ALLOC,
    COUNT_FREE,
    CURRENT_NUMBER_OF_BYTES_USED,
    HIGH_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

Key Columns to Analyze:

• EVENT_NAME: The specific internal MySQL instrument allocating the memory (e.g., memory/innodb/buf_buf_pool or memory/sql/TABLE).
• COUNT_ALLOC: The total number of memory allocation requests.
• COUNT_FREE: The total number of memory release requests.
• CURRENT_NUMBER_OF_BYTES_USED: The amount of memory currently allocated by this instrument.

Alternatively, if you have the sys schema installed, you can run a more readable query:

SELECT event_name, current_alloc, total_allocated
FROM sys.memory_global_by_current_bytes
LIMIT 10;

Step 4: Identify the Memory Leak

A memory leak is characterized by a continuous increase in memory consumption that is never released. To prove a leak exists and identify its source, you must monitor the metrics over time.

1. Take a Baseline: Run the query in Step 3 when the database is in a normal state or shortly after startup. Record the COUNT_ALLOC, COUNT_FREE, and CURRENT_NUMBER_OF_BYTES_USED values.
2. Monitor During Growth: Run the query again when you notice overall system memory rising.
3. Analyze the Difference: Look for instruments where:
   • COUNT_ALLOC is steadily increasing.
   • COUNT_FREE is lagging far behind COUNT_ALLOC.
   • CURRENT_NUMBER_OF_BYTES_USED continues to grow and never drops, even after client connections are closed or workload decreases.

Common culprits often include specific user-defined functions (UDFs), third-party plugins, or query-cache allocations under specific heavy workloads. Once you identify the leaking EVENT_NAME, you can search the MySQL bug database or documentation for known issues related to that specific subsystem.