MySQL 8.0.28引入新功能

MySQL 8.0.28開(kāi)始，新增一個(gè)特性，支持監(jiān)控統(tǒng)計(jì)并限制各個(gè)連接（會(huì)話）的內(nèi)存消耗，避免大量用戶連接因?yàn)閳?zhí)行垃圾SQL消耗過(guò)多內(nèi)存，造成可能被OOM kill的風(fēng)險(xiǎn)。

首先，需要先設(shè)置系統(tǒng)選項(xiàng)

 global_connection_memory_tracking = 1，之后可以通過(guò)系統(tǒng)狀態(tài)變量 Global_connection_memory 查看當(dāng)前所有連接消耗的內(nèi)存總量：

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

系統(tǒng)選項(xiàng) global_connection_memory_tracking 可以全局開(kāi)啟，也可以在單個(gè)會(huì)話中獨(dú)立開(kāi)啟。如果是全局開(kāi)啟，則會(huì)針對(duì)所有連接統(tǒng)計(jì)內(nèi)存消耗情況，包括系統(tǒng)內(nèi)部線程，以及root用戶創(chuàng)建的連接；

如果是單個(gè)會(huì)話中獨(dú)立開(kāi)啟，則只會(huì)統(tǒng)計(jì)當(dāng)前會(huì)話連接的內(nèi)存消耗。此外，InnoDB buffer pool不在統(tǒng)計(jì)范圍內(nèi)。

控制內(nèi)存統(tǒng)計(jì)更新頻率

可以通過(guò)設(shè)置選項(xiàng) connection_memory_chunk_size 來(lái)控制內(nèi)存統(tǒng)計(jì)更新頻率，該選項(xiàng)默認(rèn)值為8KB，也就是當(dāng)內(nèi)存使用變化超過(guò)8KB時(shí)，才會(huì)更新統(tǒng)計(jì)結(jié)果。

可以調(diào)整每個(gè)會(huì)話連接可使用內(nèi)存上限，由選項(xiàng) connection_memory_limit 定義其限制，默認(rèn)值及最大值都是 18446744073709551615，這個(gè)默認(rèn)值太大了，等同于沒(méi)有限制。如果線上經(jīng)常運(yùn)行垃圾SQL導(dǎo)致MySQL內(nèi)存消耗過(guò)大的話，可以適當(dāng)調(diào)低這個(gè)選項(xiàng)。

如何在評(píng)估一條SQL可能要消耗多少內(nèi)存呢？

可以先調(diào)整選項(xiàng)值 connection_memory_limit = 2097152，即調(diào)低到2MB。然后以普通用戶身份（沒(méi)有SUPER、SYSTEM_VARIABLES_ADMIN、SESSION_VARIABLES_ADMIN等權(quán)限）執(zhí)行相應(yīng)的SQL，如果預(yù)估需要消耗的內(nèi)存超過(guò)2MB，則會(huì)發(fā)出類似下面的報(bào)錯(cuò)，并且這個(gè)連接會(huì)被殺掉斷開(kāi)：

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.

可以看到上述報(bào)錯(cuò)信息中提示這條SQL需要消耗約 7079568字節(jié) 的內(nèi)存。當(dāng)然了，實(shí)際上這條SQL需要消耗的內(nèi)存不止 7079568字節(jié)，隨著我們細(xì)粒度逐步上調(diào) connection_memory_limit 選項(xiàng)值，最后會(huì)發(fā)現(xiàn)這條SQL需要消耗的內(nèi)存約為 13087952字節(jié)。

當(dāng)執(zhí)行完這條SQL后，我們?cè)俅尾樵儬顟B(tài)變量 Global_connection_memory，會(huì)發(fā)現(xiàn)它的值并沒(méi)這么大，說(shuō)明這條SQL執(zhí)行完畢后，相應(yīng)的內(nèi)存也立即釋放，只保留維持會(huì)話連接所需的基本內(nèi)存：

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)

前面提到一點(diǎn)，只有普通用戶執(zhí)行SQL才會(huì)受到內(nèi)存使用上限約束，如果是用root用戶執(zhí)行同一條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)

避免被OOM kill

所以不能頻繁用root等具備SUPER權(quán)限的用戶執(zhí)行需要大內(nèi)存的SQL，避免被OOM kill。

另外，選項(xiàng) connection_memory_chunk_size 如果設(shè)置太小，則會(huì)頻繁更新內(nèi)存統(tǒng)計(jì)，對(duì)系統(tǒng)性能也會(huì)有影響；但也不建議設(shè)置太大，否則可能因?yàn)楦虏患皶r(shí)而引發(fā)OOM問(wèn)題，大部分情況下采用默認(rèn)值即可。

綜上，假設(shè)有個(gè)服務(wù)器物理內(nèi)存是96GB，建議考慮做如下分配：

在上述規(guī)劃中，設(shè)置了每個(gè)會(huì)話中，普通用戶執(zhí)行的SQL消耗內(nèi)存不能超過(guò)96MB，所有會(huì)話消耗的內(nèi)存總量不超過(guò)12GB，約可最高支撐128個(gè)并發(fā)連接；此外，innodb buffer pool + 各會(huì)話內(nèi)存的和是 76G，約為物理內(nèi)存的80%，已給系統(tǒng)預(yù)留出基本充足的剩余內(nèi)存，降低發(fā)生SWAP的風(fēng)險(xiǎn)。

延伸閱讀

• 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...
• 【走進(jìn)RDS】之MySQL內(nèi)存分配與管理  http://chabaoo.cn/article/208197.htm

GreatSQL是由萬(wàn)里數(shù)據(jù)庫(kù)維護(hù)的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級(jí)應(yīng)用的MySQL分支版本。

相關(guān)鏈接：

 GreatSQL社區(qū) 

 Gitee

 GitHub

以上就是MySQL 8.0新功能監(jiān)控統(tǒng)計(jì)限制連接不再擔(dān)心被垃圾SQL搞爆內(nèi)存的詳細(xì)內(nèi)容，更多關(guān)于MySQL監(jiān)控統(tǒng)計(jì)限制連接的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論