MySQL 8.0.34 AWS RDS持久连接内存管控及参数疑问

更新记录 #

• 2024年1月26日更新（见下文）
• 2024年1月25日更新（见下文）
• 2024年1月24日更新（见下文）

服务器详情 #

（原链接已移除）

我们遇到的问题：后端老旧遗留代码会将MySQL连接保持打开状态达数天，且用于大量不同操作，导致系统可用内存耗尽。

请问：

1. 对于长期复用的连接（部分连接内存占用超200MB），能否控制其允许占用的内存量？
2. 是否存在无需断开连接即可释放连接占用内存的方法？

目前我们正在寻求临时解决方案，同时后端团队已在优化连接管理代码（代码不应无限期持有连接）。

连接内存占用情况 #

• 只读副本内存占用情况截图
• 第二份截图（显示部分线程内存占用超300MB，接近400MB）
• sys.processlist截图
• 服务器重启后内存占用132MB的线程截图

核心困惑 #

无法找到准确定义连接内存限制参数的权威信息，不同资料说法冲突：比如部分资料称@@tmp_table_size是连接级参数，另一部分则称其为全局参数，甚至无法明确哪些参数决定单连接的最大内存占用。

以下是被告知用于控制单连接内存限制的参数值：

@@max_allowed_packet: 64MB
@@sort_buffer_size: 3.81MB
@@read_buffer_size: 256KB
@@read_rnd_buffer_size: 512KB
@@join_buffer_size: 256KB
@@net_buffer_length: 16KB
@@tmp_table_size: 16777216 (107.50MB)
@@max_heap_table_size: 16777216 (16MB)

更困惑的是，最新截图显示部分线程内存占用超300MB，接近400MB，这是如何发生的？

排查用SQL #

用于查询线程和进程内存详情的SQL：

-- Memory used by the processes.
SELECT
    th.THREAD_ID,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_HOST,
    th.PROCESSLIST_DB,
    th.PROCESSLIST_COMMAND,
    th.PROCESSLIST_TIME,
    ev.EVENT_NAME,
    FORMAT_BYTES(ev.CURRENT_NUMBER_OF_BYTES_USED)
FROM
  performance_schema.threads th
  JOIN performance_schema.memory_summary_by_thread_by_event_name ev
  ON th.THREAD_ID = ev.THREAD_ID
WHERE
  ev.EVENT_NAME LIKE 'memory/%' and th.PROCESSLIST_USER IS NOT NULL AND th.PROCESSLIST_USER <> 'rdsrepladmin' AND th.PROCESSLIST_USER <> 'empoweradmin43'
ORDER BY
ev.CURRENT_NUMBER_OF_BYTES_USED DESC, th.PROCESSLIST_TIME DESC;

解决方案与权威说明 #

一、单连接内存限制的权威参数定义

基于MySQL官方文档，明确连接级与全局参数的核心区别：

• 连接级参数：每个连接单独分配对应内存，可通过SET SESSION修改当前连接的值，全局默认值由SET GLOBAL配置。
• 全局参数：影响整个实例，所有连接共享配置，仅能通过SET GLOBAL修改（需对应权限）。

你列出的参数权威分类如下：

注意：这些参数是按需分配，并非连接启动就占用所有参数总和，只有执行对应操作时才会分配内存。

二、单连接内存超300MB的原因

1. 临时表累积：长期复用的连接执行大量需创建内存临时表的查询，若临时表未及时释放（或频繁重复创建），会持续占用内存；复杂分组、排序或大结果集查询更易触发。
2. 多缓冲区叠加：一个连接同时执行多个需分配缓冲区的操作（如排序+无索引连接+大结果集读取），多个缓冲区内存会叠加。
3. 内存碎片：MySQL内存分配器（如jemalloc）可能产生碎片，长期运行的连接会积累碎片，导致显示的占用内存高于实际使用量。
4. 未读取的大结果集：若连接执行了返回超大结果集的查询，客户端未及时读取完时，MySQL会在内存中缓存部分结果。

三、临时解决方案

1. 无需断开的内存回收：
   执行以下语句触发当前连接释放闲置内存：

   SET SESSION tmp_table_size = [全局默认值];
   SET SESSION max_heap_table_size = [全局默认值];
   FLUSH LOCAL TABLES;
   

   可回收临时表、表缓存等闲置内存，但无法彻底解决内存碎片问题。

2. 自动断开闲置连接：
   通过wait_timeout和interactive_timeout设置连接闲置超时，让MySQL自动回收长期闲置的连接：

   SET GLOBAL wait_timeout = 3600; -- 非交互连接闲置1小时后断开
   SET GLOBAL interactive_timeout = 3600; -- 交互连接闲置1小时后断开
   

   需确保后端应用支持连接断开后的自动重连。

3. 降低连接级内存参数：
   在连接初始化时设置更小的缓冲区参数，减少单连接内存占用（需测试是否影响查询性能）：

   SET SESSION sort_buffer_size = 1M;
   SET SESSION join_buffer_size = 128K;
   

四、深入排查方向

1. 追踪高内存连接的SQL历史：
   查询高内存连接执行过的SQL，定位内存占用飙升的源头：

   SELECT * FROM performance_schema.events_statements_history_by_thread
   WHERE THREAD_ID = [高内存线程的THREAD_ID]
   ORDER BY EVENT_ID DESC;
   

2. 细化内存分配监控：
   基于你已有的查询，过滤具体内存模块的占用情况：

   SELECT EVENT_NAME, FORMAT_BYTES(CURRENT_NUMBER_OF_BYTES_USED)
   FROM performance_schema.memory_summary_by_thread_by_event_name
   WHERE THREAD_ID = [高内存线程的THREAD_ID]
   ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;
   

   重点关注memory/sql/TMP_TABLE（临时表）、memory/sql/Sort_buffer（排序缓冲区）等模块。
3. 检查内存碎片：
   若使用jemalloc，可通过mysqladmin extended-status | grep -i jemalloc查看碎片情况；或使用pt-mysql-summary工具做全面内存分析。

内容的提问来源于stack exchange，提问作者Floobinator