Profiling Memory Usage in MySQL Queries

Introduction : While query execution time is the usual performance metric, memory consumption can also be a critical factor. MySQL includes built‑in mechanisms that let you inspect how much memory a query uses and what it is used for.

Memory instruments : The performance_schema.setup_instruments table enumerates many memory‑related instruments. For example:

SELECT count(*) FROM performance_schema.setup_instruments;

You can filter the instruments to those whose name starts with memory/ :

SELECT name, documentation FROM performance_schema.setup_instruments WHERE name LIKE 'memory/%';

Further filtering to only those with non‑NULL documentation:

SELECT name, documentation FROM performance_schema.setup_instruments WHERE name LIKE 'memory/%' AND documentation IS NOT NULL;

The relevant summary tables include memory_summary_by_account_by_event_name , memory_summary_by_host_by_event_name , memory_summary_by_thread_by_event_name , memory_summary_by_user_by_event_name , and memory_summary_global_by_event_name . These tables allow you to sample memory usage at different granularities.

Finding the connection to monitor : Open two MySQL sessions – one runs the query you want to profile, the other is used for monitoring. In the first session obtain the connection and thread IDs:

SET @cid = (SELECT CONNECTION_ID());
SET @tid = (SELECT thread_id FROM performance_schema.threads WHERE PROCESSLIST_ID=@cid);
SELECT @cid, @tid;

Run a long‑running query (e.g., a full table scan on a large table) in the first session. While it runs, execute the following query in the monitoring session to see memory usage per event for that thread:

SELECT event_name, current_number_of_bytes_used
FROM performance_schema.memory_summary_by_thread_by_event_name
WHERE thread_id = YOUR_THREAD_ID
ORDER BY current_number_of_bytes_used DESC;

The result shows the memory categories (e.g., memory/sql/Filesort_buffer::sort_keys ) and the number of bytes each is using at that moment.

Collecting usage over time : For longer queries you can repeatedly sample memory usage. A simple Python script polls the memory summary every 250 ms and prints the top four categories:

#!/usr/bin/env python3
import time, MySQLdb, argparse
MEM_QUERY = '''
SELECT event_name, current_number_of_bytes_used
  FROM performance_schema.memory_summary_by_thread_by_event_name
  WHERE thread_id = %s
  ORDER BY current_number_of_bytes_used DESC LIMIT 4
'''
parser = argparse.ArgumentParser()
parser.add_argument('--thread-id', type=int, required=True)
args = parser.parse_args()
dbc = MySQLdb.connect(host='127.0.0.1', user='root', password='password')
c = dbc.cursor()
ms = 0
while True:
    c.execute(MEM_QUERY, (args.thread_id,))
    results = c.fetchall()
    print(f"\n## Memory usage at time {ms} ##")
    for r in results:
        print(f"{r[0][7:]} -> {round(r[1]/1024,2)}Kb")
    ms += 250
    time.sleep(0.25)

The script can be extended to write results to CSV/JSON or to plot them in real time using matplotlib . An enhanced version defines a MemoryProfiler class that stores timestamps, maintains a rolling window of 50 samples, updates legend labels, and draws a stacked area chart.

#!/usr/bin/env python3
import matplotlib.pyplot as plt, numpy as np, MySQLdb, argparse
# ... (class MemoryProfiler with methods update_xy_axis, update_labels, draw_plot, configure_plot, start_visualization) ...

Running the visualizer:

./monitor.py --connection-id YOUR_CONNECTION_ID --frequency 250

shows a live plot of memory usage per category, making it easy to identify which operations dominate memory consumption (e.g., Filesort buffers, InnoDB structures).

Conclusion : Although not always needed, detailed memory profiling can be invaluable for deep query optimization, revealing hidden memory pressure and guiding decisions about server sizing or query redesign. MySQL’s performance_schema provides the primitives to build such analysis tools.