MySQL 8.0 不再担心被垃圾 SQL 搞爆内存

MySQL 8.0.28 引入的新功能

MySQL 8.0.28 开始，新增一个特性，支持监控统计并限制各个连接（会话）的内存消耗，避免大量用户连接因为执行垃圾 SQL 消耗过多内存，造成可能被 OOM kill 的风险。

首先，需要先设置系统选项 global_connection_memory_tracking = 1，之后可以通过系统状态变量 Global_connection_memory 查看当前所有连接消耗的内存总量：

mysql> show global status like 'Global_connection_memory';+--------------------------+---------+| Variable_name            | Value   |+--------------------------+---------+| Global_connection_memory | 1122912 |+--------------------------+---------+

系统选项 global_connection_memory_tracking 可以全局开启，也可以在单个会话中独立开启。如果是全局开启，则会针对所有连接统计内存消耗情况，包括系统内部线程，以及 root 用户创建的连接；如果是单个会话中独立开启，则只会统计当前会话连接的内存消耗。此外，InnoDB buffer pool 不在统计范围内。

可以通过设置选项 connection_memory_chunk_size 来控制内存统计更新频率，该选项默认值为 8KB，也就是当内存使用变化超过 8KB 时，才会更新统计结果。

可以调整每个会话连接可使用内存上限，由选项 connection_memory_limit 定义其限制，默认值及最大值都是 18446744073709551615，这个默认值太大了，等同于没有限制。如果线上经常运行垃圾 SQL 导致 MySQL 内存消耗过大的话，可以适当调低这个选项。

如何在评估一条 SQL 可能要消耗多少内存呢？可以先调整选项值 connection_memory_limit = 2097152，即调低到 2MB。然后以普通用户身份（没有 SUPER、SYSTEM_VARIABLES_ADMIN、SESSION_VARIABLES_ADMIN 等权限）执行相应的 SQL，如果预估需要消耗的内存超过 2MB，则会发出类似下面的报错，并且这个连接会被杀掉断开：

mysql> select @@global.connection_memory_limit;+----------------------------------+| @@global.connection_memory_limit |+----------------------------------+|                          2097152 |+----------------------------------+
mysql> select count(c) from t group by c;ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 7079568 bytes.

可以看到上述报错信息中提示这条 SQL 需要消耗约 7079568 字节 的内存。当然了，实际上这条 SQL 需要消耗的内存不止 7079568 字节，随着我们细粒度逐步上调 connection_memory_limit 选项值，最后会发现这条 SQL 需要消耗的内存约为 13087952 字节。

当执行完这条 SQL 后，我们再次查询状态变量 Global_connection_memory，会发现它的值并没这么大，说明这条 SQL 执行完毕后，相应的内存也立即释放，只保留维持会话连接所需的基本内存：

mysql> select count(c) from t group by c; show global status like 'Global_connection_memory'; show session status like 'Global_connection_memory';+----------+| count(c) |+----------+|        2 |+----------+1 row in set (0.04 sec)
+--------------------------+---------+| Variable_name            | Value   |+--------------------------+---------+| Global_connection_memory | 2193153 |+--------------------------+---------+1 row in set (0.00 sec)

前面提到一点，只有普通用户执行 SQL 才会受到内存使用上限约束，如果是用 root 用户执行同一条 SQL，则不受限制：

mysql> select user();+----------------+| user()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)
mysql> select @@global.connection_memory_limit;+----------------------------------+| @@global.connection_memory_limit |+----------------------------------+|                          2097152 |+----------------------------------+1 row in set (0.00 sec)
mysql> select count(c) from t group by c;+----------+| count(c) |+----------+|        2 |+----------+1 row in set (0.05 sec)

所以不能频繁用 root 等具备 SUPER 权限的用户执行需要大内存的 SQL，避免被 OOM kill。

另外，选项 connection_memory_chunk_size 如果设置太小，则会频繁更新内存统计，对系统性能也会有影响；但也不建议设置太大，否则可能因为更新不及时而引发 OOM 问题，大部分情况下采用默认值即可。

综上，假设有个服务器物理内存是 96GB，建议考虑做如下分配：

在上述规划中，设置了每个会话中，普通用户执行的 SQL 消耗内存不能超过 96MB，所有会话消耗的内存总量不超过 12GB，约可最高支撑 128 个并发连接；此外，innodb buffer pool + 各会话内存的和是 76G，约为物理内存的 80%，已给系统预留出基本充足的剩余内存，降低发生 SWAP 的风险。

延伸阅读

• Changes in MySQL 8.0.28, https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

• sys var: global_connection_memory_limit, https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

• Status Variables: Global_connection_memory, https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html

• 【走进 RDS】之 MySQL 内存分配与管理（下篇）, https://mp.weixin.qq.com/s/CCbbmdV-stMogtby6M4DqA