MySQL 中内存使用的分析

发表于： 2025年9月14日 2025年9月14日
分类： mysql, 数据库
标签： mysql

在评估任何软件的性能时，一直存在时间和空间之间的经典权衡。在评估 MySQL 查询性能的过程中，我们通常将执行时间（或查询延迟）作为主要指标来衡量查询性能。这是一个非常好的指标，因为最终目标是希望尽可能快地获得查询结果。
我最近发布了一篇关于如何识别和分析问题 MySQL 查询的博客，其讨论围绕通过执行时间和行读取来衡量性能问题展开。然而，在讨论中，内存消耗问题却被很大程度上忽略了。
虽然内存分析的需求相对较少，但 MySQL 内置了一些机制，可以让我们深入了解查询的内存使用情况以及具体用途。本文将深入探讨这些功能，看看如何进行实时监控以分析 MySQL 连接的内存使用情况。

内存统计

在 MySQL 中，系统的许多组件都可以单独进行检测。performance_schema.setup_instruments 表列出了所有这些组件，而其数量相当可观：

SELECT COUNT(*) FROM performance_schema.setup_instruments;

+----------+
| count(*) |
+----------+
| 1255     |
+----------+

表中包含许多可用于内存分析的检测项。你可以通过选择该表并按 memory/ 过滤条件查看可用项：

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

你会得到几百条结果。每一条数据都代表一个内存类别，这些类别可以在 MySQL 中单独检测。有些类别附带了简短的文档，描述其表示的内存类别或用途。如果你只想查看文档值非空的类别，可以运行：

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

这些内存类别可以在多个不同粒度上进行采样。这些粒度存储在多个表中：

SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE '%memory_summary%'
AND table_schema = 'performance_schema';

+-----------------------------------------+
| TABLE_NAME                              |
+-----------------------------------------+
| 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    |
| memory_summary_global_by_event_name     |
+-----------------------------------------+

以下为各表的用途：

memory_summary_by_account_by_event_name：基于账户（用户和主机的组合）汇总内存事件；
memory_summary_by_host_by_event_name：以主机级别汇总内存事件；
memory_summary_by_thread_by_event_name：以 MySQL 线程级别汇总内存事件；
memory_summary_by_user_by_event_name：以用户级别汇总内存事件；
memory_summary_global_by_event_name：内存统计信息的全局汇总。

请注意，此功能无法直接以查询级别跟踪内存使用情况。不过，这并不意味着我们不能分析查询内存使用！我们可以监控执行相关查询时所在连接的内存使用情况。因此，我们的重点将放在 memory_summary_by_thread_by_event_name 表上，因为它使 MySQL 连接和线程之间建立了便利的关联。

确定某个连接的内存使用情况

此时，你需要在命令行中设置两个 MySQL 服务器连接。第一个连接用于执行你想监控内存使用的查询，第二个连接则用于监控目的。
在第一个连接中，运行以下查询以获取连接 ID 和线程 ID：

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

然后获取这些值。当然，你的值可能会与下述示例有所不同：

SELECT @cid, @tid;

+------+------+
| @cid | @tid |
+------+------+
|   49 |   89 |
+------+------+

接下来，执行你想分析内存使用的某些长时间运行的查询。例如，以下是从一个包含 1 亿行的大表中执行 SELECT 查询：

SELECT alias FROM chat.message ORDER BY alias DESC LIMIT 100000;

在查询执行期间，切换到另一个控制台连接并运行以下查询，并用你的线程 ID 替换 YOUR_THREAD_ID：

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;

你应该看到类似以下的结果，虽然具体详情会因查询和数据的不同而有所变化：

+---------------------------------------+------------------------------+
| event_name                            | current_number_of_bytes_used |
+---------------------------------------+------------------------------+
| memory/sql/Filesort_buffer::sort_keys | 203488                       |
| memory/innodb/memory                  | 169800                       |
| memory/sql/THD::main_mem_root         | 46176                        |
| memory/innodb/ha_innodb               | 35936                        |
...

此输出指示在执行查询的确切时间点每个内存类别的使用情况。内存使用在整个查询执行过程中并不是恒定的，因此如果想观察内存使用随时间的变化，你需要进行多次采样。

采集随时间变化的内存使用情况

下一步是能够随时间采样内存使用情况。对于耗时较短的查询，这种方法可能用处不大，因为在查询执行时，只能运行一次或少数几次采样。不过，对于运行时间较长（耗时数秒或数分钟）的查询，此方法非常有用，这类查询往往会占用大量内存。
你可以完全使用 SQL 并通过存储过程实现此功能。不过，在本案例中，我们将用一个 Python 脚本来进行监控：

#!/usr/bin/env python3

import time
import MySQLdb
import 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)

此脚本的功能包括：

从命令行获取线程 ID，并监控其内存使用；
建立到 MySQL 数据库的连接；
每 250 毫秒执行一次查询，获取当前内存使用的前四大类别并输出结果。

通过执行以上脚本，你可以获取类似以下的结果：

...
## Memory usage at time 4250 ##
innodb/row0sel -> 25.22Kb
sql/String::value -> 16.07Kb
sql/user_var_entry -> 0.41Kb
innodb/memory -> 0.23Kb
...

虽然实用，但仍有改进空间。例如，通过某些可视化工具更方便地查看内存使用的总体情况，而不是直接输出结果。

内存使用的可视化

为使工具更加实用，同时提供可视化功能，我们可以对上述脚本做以下改动：

从命令行获取连接 ID，由脚本负责查找关联线程；
监控频率可通过命令行配置；
使用 matplotlib 库生成内存使用的堆栈图，并显示过去 50 次采样结果。

以下是完整示例代码（省略部分内容）：

#!/usr/bin/env python3

import matplotlib.pyplot as plt
import numpy as np
import MySQLdb
import 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 event_name DESC
'''

# 可视化代码省略…

通过运行此脚本，你可以实时观察查询的内存使用增长，并查看每个内存分类对总体内存使用的贡献比例。

总结

尽管内存分析的需求可能不如查询延迟那样频繁，但当需要进行详细查询优化时，获取详细内存使用信息会非常有价值。这可以揭示 MySQL 在何时以及为何对系统造成内存压力，同时帮助评估是否需要为你的数据库服务器升级内存配置。通过 MySQL 提供的一系列基础功能，你可以构建更先进的工具来分析查询和工作负载中的内存使用。

MySQL 中内存使用的分析插图

关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台

除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接

本文链接：https://www.choupangxia.com/2025/09/14/profiling-memory-usage-in-mysql/

secbro2

程序新视界，一个软实力、硬知识同时提升的平台

1331