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

Enjoy GreatSQL ??

關于 GreatSQL

GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，

相關鏈接： GreatSQL社區 Gitee GitHub Bilibili

GreatSQL社區：

社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html

技術交流群：

微信：掃碼添加GreatSQL社區助手微信好友，發送驗證資訊加群，

標籤：MySQL

上一篇：06~12-Esp8266物聯網芯片的使用（一）-part02/03-ESP8266開發環境、編程舉例

下一篇：返回列表