MySQL?8.0新功能監(jiān)控統(tǒng)計限制連接不再擔心被垃圾SQL搞爆內存

更新時間：2023年05月18日 09:36:51 作者：GreatSQL社區(qū)

這篇文章主要介紹了MySQL?8.0新功能監(jiān)控統(tǒng)計限制連接不再擔心被垃圾SQL搞爆內存詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

MySQL 8.0.28引入新功能

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

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

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

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

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

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

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

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

可以調整每個會話連接可使用內存上限，由選項 connection_memory_limit 定義其限制，默認值及最大值都是 18446744073709551615，這個默認值太大了，等同于沒有限制。如果線上經常運行垃圾SQL導致MySQL內存消耗過大的話，可以適當調低這個選項。

如何在評估一條SQL可能要消耗多少內存呢？

可以先調整選項值 connection_memory_limit = 2097152，即調低到2MB。然后以普通用戶身份（沒有SUPER、SYSTEM_VARIABLES_ADMIN、SESSION_VARIABLES_ADMIN等權限）執(zhí)行相應的SQL，如果預估需要消耗的內存超過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é) 的內存。當然了，實際上這條SQL需要消耗的內存不止 7079568字節(jié)，隨著我們細粒度逐步上調 connection_memory_limit 選項值，最后會發(fā)現(xiàn)這條SQL需要消耗的內存約為 13087952字節(jié)。

當執(zhí)行完這條SQL后，我們再次查詢狀態(tài)變量 Global_connection_memory，會發(fā)現(xiàn)它的值并沒這么大，說明這條SQL執(zhí)行完畢后，相應的內存也立即釋放，只保留維持會話連接所需的基本內存：

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)

前面提到一點，只有普通用戶執(zhí)行SQL才會受到內存使用上限約束，如果是用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權限的用戶執(zhí)行需要大內存的SQL，避免被OOM kill。

另外，選項 connection_memory_chunk_size 如果設置太小，則會頻繁更新內存統(tǒng)計，對系統(tǒng)性能也會有影響；但也不建議設置太大，否則可能因為更新不及時而引發(fā)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

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

延伸閱讀