MySQL 8.0.28引入新功能

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

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

 global_connection_memory_tracking = 1，之后可以通過系統(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)選項 global_connection_memory_tracking 可以全局開啟，也可以在單個會話中獨(dú)立開啟。如果是全局開啟，則會針對所有連接統(tǒng)計內(nèi)存消耗情況，包括系統(tǒng)內(nèi)部線程，以及root用戶創(chuàng)建的連接；

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

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

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

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

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

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

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字節(jié) 的內(nèi)存。當(dāng)然了，實際上這條SQL需要消耗的內(nèi)存不止 7079568字節(jié)，隨著我們細(xì)粒度逐步上調(diào) connection_memory_limit 選項值，最后會發(fā)現(xiàn)這條SQL需要消耗的內(nèi)存約為 13087952字節(jié)。

當(dāng)執(zhí)行完這條SQL后，我們再次查詢狀態(tài)變量 Global_connection_memory，會發(fā)現(xiàn)它的值并沒這么大，說明這條SQL執(zhí)行完畢后，相應(yīng)的內(nèi)存也立即釋放，只保留維持會話連接所需的基本內(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才會受到內(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。

另外，選項 connection_memory_chunk_size 如果設(shè)置太小，則會頻繁更新內(nèi)存統(tǒng)計，對系統(tǒng)性能也會有影響；但也不建議設(shè)置太大，否則可能因為更新不及時而引發(fā)OOM問題，大部分情況下采用默認(rèn)值即可。

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

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

延伸閱讀

• 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://www.fzitv.net/article/208197.htm

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

相關(guān)鏈接：

 GreatSQL社區(qū) 

 Gitee

 GitHub

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