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 的危险。
延长浏览
- 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-variabl…
- Status Variables: Global_connection_memory, https://dev.mysql.com/doc/refman/8.0/en/server-status-variabl…
- 【走进 RDS】之 MySQL 内存调配与治理(下篇), https://mp.weixin.qq.com/s/CCbbmdV-stMogtby6M4DqA
Enjoy GreatSQL :)
## 对于 GreatSQL
GreatSQL 是由万里数据库保护的 MySQL 分支,专一于晋升 MGR 可靠性及性能,反对 InnoDB 并行查问个性,是实用于金融级利用的 MySQL 分支版本。
相干链接:GreatSQL 社区 Gitee GitHub Bilibili
GreatSQL 社区:
社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html
技术交换群:
微信:扫码增加
GreatSQL 社区助手
微信好友,发送验证信息加群
。