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

heatdog
发布于 2023-6-9 11:58
浏览
0收藏

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,建议考虑做如下分配:

选项

设置值

innodb_buffer_pool_size

64G

global_connection_memory_limit

12G

connection_memory_chunk_size

8192

connection_memory_limit

96M

global_connection_memory_tracking

ON

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

延伸阅读


文章转载自公众号:GreatSQL社区

分类
标签
已于2023-6-9 11:58:10修改
收藏
回复
举报
回复
    相关推荐