MySQL 8.0.28引入的新功能 MySQL 8.0.28開始，新增一個特性，支持監控統計並限制各個連接（會話）的記憶體消耗，避免大量用戶連接因為執行垃圾SQL消耗過多記憶體，造成可能被OOM kill的風險。 首先，需要先設置系統選項 global_connection_memory_tracki ...
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-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
