GreatSQL內(nèi)存消耗異常排查攻略：從系統(tǒng)到應用層面的深入分析
作者：YeJinrong/葉金榮
本文和大家試著使用 GreatSQL 中的 sys schema 和 performance_schema 進行深入分析，找出內(nèi)存消耗大戶的源頭，并盡可能解決問題。
當 GreatSQL 數(shù)據(jù)庫處于高并發(fā)高負載時，可能會發(fā)現(xiàn) mysqld 進程的內(nèi)存消耗遠遠超出設置的 innodb_buffer_pool_size 時，有時候甚至會高達甚至超過系統(tǒng)內(nèi)存的90%，遇到這種問題時，心里經(jīng)常會發(fā)慌，擔心下一秒內(nèi)存就會爆了發(fā)生 OOM，或者數(shù)據(jù)庫hang死不響應。
本文和大家試著使用 GreatSQL 中的 sys schema 和 performance_schema 進行深入分析，找出內(nèi)存消耗大戶的源頭，并盡可能解決問題。
下面是詳細的排查方法和步驟。
1. 確認實際內(nèi)存消耗
1.1 操作系統(tǒng)層面分析
先檢查確認 mysqld 進程的內(nèi)存具體消耗占用情況，做到心里有數(shù)，避免真的下一秒就發(fā)生 OOM 的問題：
$ free -ht
free -ht
total used free shared buff/cache available
Mem: 30Gi 28Gi 240Mi 33Mi 2.0Gi 1.7Gi
Swap: 0B 0B 0B
Total: 30Gi 28Gi 240Mi
$ ps aux | grep mysqld
mysql 51931 23.0 89.8 32100800 29008060 ? Ssl Nov22 949:41 /data/apps/GreatSQL-8.0.32-26-Linux-glibc2.28-x86_64/bin/mysqld
$ top -p $(pidof mysqld) -n 1
top - 05:36:37 up 4 days, 4:06, 1 user, load average: 5.56, 8.70, 10.87
Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie
%Cpu(s): 8.4 us, 1.7 sy, 0.0 ni, 86.6 id, 3.4 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem : 31553.3 total, 265.6 free, 29148.6 used, 2139.2 buff/cache
MiB Swap: 0.0 total, 0.0 free, 0.0 used. 1903.3 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
51931 mysql 20 0 30.6g 27.7g 4656 S 80.0 89.8 949:51.99 mysqld在上述結(jié)果中重點關(guān)注幾個指標：
- RES（Resident Memory）：物理內(nèi)存占用，約 27.7G。
- VIRT（Virtual Memory）：虛擬地址空間大小，約 30.6G。
看到 mysqld 進程當前內(nèi)存消耗占比約 90%，還算可控，沒到火燒眉毛的境地。
繼續(xù)使用 pmap 查看 mysqld 進程中的內(nèi)存分布情況：
$ pmap -x $(pidof mysqld) | sort -k3 -rn | head -n 20
total kB 32100804 29029796 29016940
00007f8a484d8000 5368992 5361664 5361664 rw--- [ anon ]
00007f892533d000 4698892 4691952 4691952 rw--- [ anon ]
00007f87e961e000 4564872 4556784 4556784 rw--- [ anon ]
00007f86adbe0000 4296832 4290544 4290544 rw--- [ anon ]
00007f86298bb000 1023252 1015792 1015792 rw--- [ anon ]
00007f866c350000 979648 979632 979632 rw--- [ anon ]
00007f87b8112000 719800 712688 712688 rw--- [ anon ]
00007f89046d4000 451760 444400 444400 rw--- [ anon ]
0000000005afc000 286800 282640 282640 rw--- [ anon ]
00007f8ba7715000 200300 200276 200276 rw--- [ anon ]
00007f8578000000 131072 131072 131072 rw--- [ anon ]
00007f8570000000 131072 131072 131072 rw--- [ anon ]
00007f8568000000 131072 131072 131072 rw--- [ anon ]
00007f8560000000 131072 131072 131072 rw--- [ anon ]
00007f8558000000 131072 131072 131072 rw--- [ anon ]
00007f8550000000 131048 131048 131048 rw--- [ anon ]
00007f8438000000 130668 130668 130668 rw--- [ anon ]
00007f8b98000000 65536 65536 65536 rw--- [ anon ]
00007f8b94000000 65536 65536 65536 rw--- [ anon ]看到大量的匿名內(nèi)存（anon）消耗較多內(nèi)存，這可能是由以下幾個原因引起的：
- 動態(tài)分配的內(nèi)存：
GreatSQL 在運行過程中會頻繁地進行內(nèi)存分配和釋放，這些內(nèi)存通常是以匿名映射的形式存在于進程的虛擬地址空間中。
例如，GreatSQL 的線程池、緩存、臨時表等都會動態(tài)分配內(nèi)存。
- 緩沖區(qū)和緩存：
GreatSQL 使用大量的緩沖區(qū)和緩存來提高性能，例如InnoDB Buffer Pool（以下簡稱 IBP）、InnoDB Log Buffer等。
這些緩沖區(qū)和緩存通常會占用大量的匿名內(nèi)存。
線程堆棧：
每個線程都有自己的堆?？臻g，這些堆?？臻g也是匿名內(nèi)存的一部分。
為了響應用戶請求而創(chuàng)建了大量線程，那么這些線程的堆?？臻g會占用不少內(nèi)存。
臨時文件：
GreatSQL 在處理大查詢或排序操作時，可能會使用臨時表、臨時文件，其內(nèi)存映射也會占用匿名內(nèi)存。
內(nèi)存泄漏：
如果 GreatSQL 存在內(nèi)存泄漏問題，也會導致匿名內(nèi)存不斷增加。
可以針對上述各個模塊/維度做進一步排查分析。
1.2 檢查 IBP 內(nèi)存相關(guān)配置
- 使用以下 SQL 命令查詢 IBP 等內(nèi)存消耗較多的相關(guān)模塊內(nèi)存配置
greatsql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 21474836480 |
+-------------------------+-------------+
greatsql> SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 33554432 |
+------------------------+----------+從上面可見 IBP 設置為 20G，但是 mysqld 進程的內(nèi)存占用為 27.7G，超過 IBP 較多，這可能是由于用戶的 SQL 請求（比如效率較低的慢查詢 SQL）其他模塊或線程引起。還需要繼續(xù)排查。
2. 利用 Performance Schema 排查內(nèi)存消耗來源
從 5.6.6 版本開始，Performance Schema 默認啟用，是一個內(nèi)置的性能診斷工具，用于實時監(jiān)控和分析 GreatSQL 服務器的運行狀態(tài)。它提供了詳細的性能數(shù)據(jù)，包括 內(nèi)存分配的全局視圖、SQL 語句的執(zhí)行時間、線程活動、鎖等待等詳細信息，幫助開發(fā)者和 DBA 識別和解決性能瓶頸。
2.1 按內(nèi)存模塊查看占用
使用 memory_summary_global_by_event_name 按模塊查看內(nèi)存分配情況：
greatsql> USE performance_schema;
greatsql> SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED AS memory_bytes,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS memory_mb
FROM
performance_schema.memory_summary_global_by_event_name
WHERE
CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY
CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;
+--------------------------------------------------------------------+--------------+----------------+
| EVENT_NAME | memory_bytes | memory_mb |
+--------------------------------------------------------------------+--------------+----------------+
| memory/innodb/buf_buf_pool | 21957836800 | 20940.62500000 |
| memory/group_rpl/GCS_XCom::xcom_cache | 1070853221 | 1021.24521351 |
| memory/mysys/IO_CACHE | 84149952 | 80.25164795 |
| memory/performance_schema/events_statements_summary_by_digest | 42240000 | 40.28320313 |
| memory/innodb/log_buffer_memory | 33555440 | 32.00096130 |
| memory/innodb/ut0link_buf | 25165888 | 24.00006104 |
| memory/innodb/lock0lock | 22440096 | 21.40054321 |
| memory/sql/TABLE | 15646883 | 14.92203045 |
| memory/performance_schema/events_statements_history_long | 15040000 | 14.34326172 |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 14561280 | 13.88671875 |
+--------------------------------------------------------------------+--------------+----------------+
10 rows in set (0.00 sec)- EVENT_NAME：具體內(nèi)存分配的模塊名稱，如 memory/innodb/buffer_pool、memory/sql/temporary_table 等。
- CURRENT_NUMBER_OF_BYTES_USED：當前分配的內(nèi)存總量。
例如：
- 如果 memory/innodb/buf_buf_pool 值較高，說明 InnoDB buffer pool 占用較高。其他幾個包含 memory/innodb 關(guān)鍵字的，也都是和 InnoDB 存儲引擎相關(guān)的內(nèi)存模塊。
- 其中 memory/mysys/MY_BITMAP::bitmap 主要用于管理位圖數(shù)據(jù)結(jié)構(gòu) (bitmap) 的內(nèi)存使用。它的設計初衷是為了實現(xiàn)高效的位存儲與處理，主要用于存儲和操作需要標志位（bit flag）來跟蹤或控制的數(shù)據(jù)。其具體存儲的數(shù)據(jù)包括但不限于：
索引或表分區(qū)的狀態(tài)：位圖被用來記錄索引或分區(qū)的使用狀態(tài)。例如，在分區(qū)表掃描時，通過位圖可以高效管理哪些分區(qū)需要掃描或已經(jīng)掃描。
事務或鎖狀態(tài)：記錄事務的特定標志位或鎖的使用狀態(tài)，比如資源鎖（resource locks）的分配狀態(tài)。
InnoDB 的內(nèi)部操作：位圖被用于跟蹤一些內(nèi)部存儲引擎的優(yōu)化過程，例如自適應哈希索引、頁的臟位（dirty bit）標記等。
線程管理：管理線程池中線程的分配和使用狀態(tài)。
性能統(tǒng)計：在某些性能分析的場景下，位圖用于記錄啟用或禁用的統(tǒng)計模塊。
- 其中 memory/group_rpl/GCS_XCom::xcom_cache 是 MGR Xcom cache，在 GreatSQL 8.0.32-26 中初始默認值即為 1GB，詳情參考 Xcom cache分配靜態(tài)化。其他幾個包含 group_rpl 關(guān)鍵字特征的，也是和 MGR 相關(guān)的模塊。
- 其中 memory/mysys/IO_CACHE 是一個重要的內(nèi)存管理模塊，主要用于管理和優(yōu)化文件I/O操作。IO_CACHE 提供了一個高效的緩存機制，可以顯著提高文件讀寫操作的性能。主要存儲的數(shù)據(jù)有：
臨時文件數(shù)據(jù)：排序、分組、聯(lián)接等操作過程中生成的中間結(jié)果。
二進制日志：binlog 的寫入和讀取操作中使用緩存。
文件塊：GreatSQL 訪問文件時，將數(shù)據(jù)塊加載到緩存中，避免重復讀取。
表數(shù)據(jù)：表掃描或索引掃描時，用于緩存表或索引的數(shù)據(jù)。
如果 memory/sql/temporary_table 值較高，說明內(nèi)存被臨時表消耗。
如果 memory/innodb/hash_index 值較高，可能是 InnoDB 的自適應哈希索引占用內(nèi)存。
上面的查詢結(jié)果表明，memory/innodb/buf_buf_pool(IBP) 占用內(nèi)存約 20G，memory/group_rpl/GCS_XCom::xcom_cache(MGR XCom Cache) 占用內(nèi)存約 1G，都是符合預期的。但是 memory/mysys/IO_CACHE 占用的內(nèi)存較高，需要重點排查。
2.2 跟蹤各模塊內(nèi)存使用變化
可以每間隔一段時間重復執(zhí)行下面的 SQL 請求，觀察各個模塊的內(nèi)存消耗變化，找出內(nèi)存消耗增長較快的模塊，它們可能就是導致 mysqld 進程消耗較大內(nèi)存的“元兇”。
greatsql> USE performance_schema;
greatsql> SELECT
EVENT_NAME,
SUM(SUM_NUMBER_OF_BYTES_ALLOC) / 1024 / 1024 AS total_memory_mb
FROM
performance_schema.memory_summary_global_by_event_name
GROUP BY
EVENT_NAME
ORDER BY
SUM_NUMBER_OF_BYTES_ALLOC DESC
LIMIT 10;
+---------------------------------------------+------------------+
| EVENT_NAME | total_memory_mb |
+---------------------------------------------+------------------+
| memory/innodb/memory | 3688428.98232269 |
| memory/mysys/MY_BITMAP::bitmap | 289065.08729172 |
| memory/group_rpl/transaction_data | 219301.70309544 |
| memory/group_rpl/Gcs_message_data::m_buffer | 219176.21560478 |
| memory/mysys/IO_CACHE | 102064.87601471 |
| memory/group_rpl/GCS_XCom::xcom_cache | 57685.34130669 |
| memory/sql/Log_event | 47153.59659863 |
| memory/group_rpl/write_set_encoded | 35822.83545971 |
| memory/innodb/buf_buf_pool | 20940.62500000 |
| memory/group_rpl/certification_data | 11146.79415703 |
+---------------------------------------------+------------------+結(jié)合前面各模塊當前占用的內(nèi)存情況，從上述查詢結(jié)果綜合分析看，較大概率應該就是 memory/mysys/IO_CACHE 模塊消耗內(nèi)存過大。
2.3 按線程查看內(nèi)存占用
接著繼續(xù)查看各線程內(nèi)存占用情況，確認是否有個別線程（尤其是長連接線程）消耗了過多內(nèi)存資源。使用 memory_summary_by_thread_by_event_name 查看各線程的內(nèi)存分配，同時關(guān)聯(lián)查詢 threads 視圖，可以顯示各線程當前正在執(zhí)行的 SQL 請求及其執(zhí)行耗時：
-- 1. 查看各線程當前的內(nèi)存分配情況
greatsql> USE performance_schema;
greatsql> SELECT
m.EVENT_NAME,
m.COUNT_ALLOC,
m.CURRENT_NUMBER_OF_BYTES_USED AS mem_sum,
(m.CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024.0) AS mem_sum_mb,
t.NAME,
t.TYPE,
t.PROCESSLIST_ID,
LEFT(t.PROCESSLIST_INFO, 10)
FROM
memory_summary_by_thread_by_event_name m
JOIN threads t
USING (THREAD_ID)
WHERE
t.PROCESSLIST_ID != CONNECTION_ID()
ORDER BY
m.CURRENT_NUMBER_OF_BYTES_USED desc
LIMIT 20;
+-------------------------------+-------------+---------+------------+----------------------------------------------+------------+----------------+------------------------------+
| EVENT_NAME | COUNT_ALLOC | mem_sum | mem_sum_mb | NAME | TYPE | PROCESSLIST_ID | LEFT(t.PROCESSLIST_INFO, 10) |
+-------------------------------+-------------+---------+------------+----------------------------------------------+------------+----------------+------------------------------+
| memory/innodb/memory | 13 | 21888 | 0.02087402 | thread/group_rpl/THD_applier_module_receiver | FOREGROUND | 12 | Group repl |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39893 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39894 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39895 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39896 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39897 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39898 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39899 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39900 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39901 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39902 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39903 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39904 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39905 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39906 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39907 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39908 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39909 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39910 | load data |
| memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39911 | load data |
+-------------------------------+-------------+---------+------------+----------------------------------------------+------------+----------------+------------------------------+
-- 2. 查看各線程匯總的內(nèi)存分配情況
greatsql> SELECT
m.EVENT_NAME,
m.COUNT_ALLOC,
m.SUM_NUMBER_OF_BYTES_ALLOC AS mem_sum,
(m.SUM_NUMBER_OF_BYTES_ALLOC / 1024 / 1024.0) AS mem_sum_mb,
t.NAME,
t.TYPE,
t.PROCESSLIST_ID,
LEFT(t.PROCESSLIST_INFO, 10)
FROM
memory_summary_by_thread_by_event_name m
JOIN threads t
USING (THREAD_ID)
WHERE
t.PROCESSLIST_ID != CONNECTION_ID()
ORDER BY
m.SUM_NUMBER_OF_BYTES_ALLOC desc
LIMIT 20;
+----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+
| EVENT_NAME | COUNT_ALLOC | mem_sum | mem_sum_mb | NAME | TYPE | PROCESSLIST_ID | LEFT(t.PROCESSLIST_INFO, 10) |
+----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+
| memory/sql/Log_event | 818062681 | 36821553500 | 35115.76986313 | thread/group_rpl/THD_applier_module_receiver | FOREGROUND | 12 | Group repl |
| memory/innodb/memory | 258356 | 266640048 | 254.28776550 | thread/sql/one_connection | FOREGROUND | 40222 | load data |
| memory/innodb/memory | 255478 | 263811432 | 251.59018707 | thread/sql/one_connection | FOREGROUND | 40204 | load data |
| memory/innodb/memory | 217298 | 224575448 | 214.17183685 | thread/sql/one_connection | FOREGROUND | 40209 | load data |
| memory/innodb/memory | 212201 | 219160304 | 209.00755310 | thread/sql/one_connection | FOREGROUND | 40215 | load data |
| memory/innodb/memory | 209052 | 215978440 | 205.97309113 | thread/sql/one_connection | FOREGROUND | 40212 | load data |
| memory/innodb/memory | 203823 | 210364872 | 200.61957550 | thread/sql/one_connection | FOREGROUND | 40220 | load data |
| memory/innodb/memory | 201921 | 208627128 | 198.96233368 | thread/sql/one_connection | FOREGROUND | 40224 | load data |
| memory/innodb/memory | 195252 | 202055944 | 192.69556427 | thread/sql/one_connection | FOREGROUND | 40214 | load data |
| memory/innodb/memory | 193319 | 199526048 | 190.28286743 | thread/sql/one_connection | FOREGROUND | 40208 | load data |
| memory/innodb/memory | 192498 | 198820216 | 189.60973358 | thread/sql/one_connection | FOREGROUND | 40227 | load data |
| memory/innodb/memory | 191717 | 198099104 | 188.92202759 | thread/sql/one_connection | FOREGROUND | 40205 | load data |
| memory/innodb/memory | 191234 | 197764864 | 188.60327148 | thread/sql/one_connection | FOREGROUND | 40202 | load data |
| memory/innodb/memory | 190012 | 196401888 | 187.30343628 | thread/sql/one_connection | FOREGROUND | 40216 | load data |
| memory/innodb/memory | 189098 | 195217576 | 186.17398834 | thread/sql/one_connection | FOREGROUND | 40207 | load data |
| memory/innodb/memory | 188670 | 195084304 | 186.04689026 | thread/sql/one_connection | FOREGROUND | 40223 | load data |
| memory/innodb/memory | 187466 | 193563912 | 184.59693146 | thread/sql/one_connection | FOREGROUND | 40218 | load data |
| memory/innodb/memory | 187045 | 193354488 | 184.39720917 | thread/sql/one_connection | FOREGROUND | 40217 | load data |
| memory/innodb/memory | 186838 | 193196152 | 184.24620819 | thread/sql/one_connection | FOREGROUND | 40219 | load data |
| memory/innodb/memory | 186465 | 192576408 | 183.65517426 | thread/sql/one_connection | FOREGROUND | 40210 | load data |
+----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+從上面的查詢結(jié)果可見，當前有較多的 LOAD DATA 請求正在運行，有可能是它們導致的內(nèi)存占用較高的原因。
其中
- CURRENT_NUMBER_OF_BYTES_USED 表示當前分配但尚未釋放的內(nèi)存塊的聚合大小。CURRENT_NUMBER_OF_BYTES_USED = SUM_NUMBER_OF_BYTES_ALLOC ? SUM_NUMBER_OF_BYTES_FREE。
- SUM_NUMBER_OF_BYTES_ALLOC 表示已分配內(nèi)存塊的聚合大小。
- SUM_NUMBER_OF_BYTES_FREE 表示已釋放內(nèi)存塊的聚合大小。
排查分析道這里，基本上可以推斷是由于有大量并發(fā) LOAD DATA 導入數(shù)據(jù)請求導致 mysqld 內(nèi)存占用較高。
3. 利用 sys schema 簡化分析
相對于用 Performance Schema 排查分析，采用 sys schema 分析則更簡單省事。接下來介紹如何利用 sys schema 分析。
GreatSQL sys schema 是一組視圖、存儲過程和函數(shù)的集合，它基于 performance_schema 提供了更易讀和易用的性能數(shù)據(jù)匯總。sys schema 通過簡化復雜的性能指標，幫助數(shù)據(jù)庫管理員和開發(fā)人員快速診斷和優(yōu)化 GreatSQL 的性能問題。
3.1 查看全局及各模塊內(nèi)存分布
首先，查看當前全部內(nèi)存分配情況：
greatsql> USE sys;
greatsql> SELECT * FROM memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 22.08 GiB |
+-----------------+在 IBP 設置為 20G 的前提下，從 memory_global_total 查詢到的內(nèi)存分配總數(shù)并沒有超過太多，說明較大可能性是由于用戶的 SQL 請求（比如效率較低的慢查詢 SQL）或其他模塊引起。
繼續(xù)查詢內(nèi)存使用的全局分布情況：
greatsql> SELECT
*
FROM
sys.memory_global_by_current_bytes
LIMIT 20;
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool | 160 | 20.45 GiB | 130.88 MiB | 160 | 20.45 GiB | 130.88 MiB |
| memory/group_rpl/GCS_XCom::xcom_cache | 4295 | 1018.00 MiB | 242.71 KiB | 463303 | 1.13 GiB | 2.55 KiB |
| memory/mysys/IO_CACHE | 175 | 280.82 MiB | 1.60 MiB | 539 | 906.46 MiB | 1.68 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB |
| memory/innodb/log_buffer_memory | 1 | 32.00 MiB | 32.00 MiB | 1 | 32.00 MiB | 32.00 MiB |
| memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB |
| memory/innodb/lock0lock | 9893 | 21.40 MiB | 2.22 KiB | 9893 | 21.40 MiB | 2.22 KiB |
| memory/sql/TABLE | 5796 | 17.49 MiB | 3.09 KiB | 5798 | 17.50 MiB | 3.09 KiB |
| memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 13.89 MiB | 55.33 KiB | 257 | 13.89 MiB | 55.33 KiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 13.66 MiB | 13.66 MiB | 1 | 13.66 MiB | 13.66 MiB |
| memory/innodb/memory | 7583 | 12.28 MiB | 1.66 KiB | 8812 | 16.80 MiB | 1.95 KiB |
| memory/performance_schema/file_instances | 4 | 11.00 MiB | 2.75 MiB | 4 | 11.00 MiB | 2.75 MiB |
| memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 1 | 9.32 MiB | 9.32 MiB | 1 | 9.32 MiB | 9.32 MiB |
| memory/performance_schema/table_handles | 1 | 9.06 MiB | 9.06 MiB | 1 | 9.06 MiB | 9.06 MiB |
| memory/mysys/KEY_CACHE | 3 | 8.00 MiB | 2.67 MiB | 3 | 8.00 MiB | 2.67 MiB |
| memory/innodb/sync0arr | 3 | 7.03 MiB | 2.34 MiB | 3 | 7.03 MiB | 2.34 MiB |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+在 sys schema 中，大部分視圖都同時存儲原始數(shù)據(jù)以及格式化后可讀性更強的兩種視圖。所以上面的 SQL 查詢還可以改成查詢原始未格式化的視圖：
greatsql> SELECT
*
FROM
sys.x$memory_global_by_current_bytes
LIMIT 20;
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+
| memory/innodb/buf_buf_pool | 160 | 21957836800 | 137236480.0000 | 160 | 21957836800 | 137236480.0000 |
| memory/group_rpl/GCS_XCom::xcom_cache | 4068 | 1067435559 | 262398.1217 | 463303 | 1208663474 | 2608.7970 |
| memory/mysys/IO_CACHE | 126 | 206147792 | 1636093.5873 | 539 | 950487072 | 1763426.8497 |
| memory/performance_schema/events_statements_summary_by_digest | 1 | 42240000 | 42240000.0000 | 1 | 42240000 | 42240000.0000 |
| memory/innodb/log_buffer_memory | 1 | 33555440 | 33555440.0000 | 1 | 33555440 | 33555440.0000 |
| memory/innodb/ut0link_buf | 2 | 25165888 | 12582944.0000 | 2 | 25165888 | 12582944.0000 |
| memory/innodb/lock0lock | 9893 | 22440096 | 2268.2802 | 9893 | 22440096 | 2268.2802 |
| memory/sql/TABLE | 5796 | 18341476 | 3164.5059 | 5798 | 18351820 | 3165.1983 |
| memory/performance_schema/events_statements_history_long | 1 | 15040000 | 15040000.0000 | 1 | 15040000 | 15040000.0000 |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 14561280 | 56658.6770 | 257 | 14561280 | 56658.6770 |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 14321664 | 14321664.0000 | 1 | 14321664 | 14321664.0000 |
| memory/innodb/memory | 7562 | 12858512 | 1700.4115 | 8812 | 17615632 | 1999.0504 |
| memory/performance_schema/file_instances | 4 | 11534336 | 2883584.0000 | 4 | 11534336 | 2883584.0000 |
| memory/performance_schema/events_statements_history_long.digest_text | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 |
| memory/performance_schema/events_statements_history_long.sql_text | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 1 | 9768960 | 9768960.0000 | 1 | 9768960 | 9768960.0000 |
| memory/performance_schema/table_handles | 1 | 9502720 | 9502720.0000 | 1 | 9502720 | 9502720.0000 |
| memory/mysys/KEY_CACHE | 3 | 8390864 | 2796954.6667 | 3 | 8390864 | 2796954.6667 |
| memory/innodb/sync0arr | 3 | 7373032 | 2457677.3333 | 3 | 7373032 | 2457677.3333 |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+從上面兩個查詢結(jié)果可知，除了 IBP 和 MGR 之外，模塊 memory/mysys/IO_CACHE 占用的內(nèi)存最高，是重點分析排查對象。
查看 sys.memory_global_by_current_bytes 視圖定義，可知它的原始數(shù)據(jù)來自 performance_schema：
greatsql> SHOW CREATE VIEW sys.memory_global_by_current_bytes\G
*************************** 1. row ***************************
View: memory_global_by_current_bytes
Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW
`memory_global_by_current_bytes` (`event_name`,`current_count`,`current_alloc`,`current_avg_alloc`,
`high_count`,`high_alloc`,`high_avg_alloc`)
AS select `performance_schema`.`memory_summary_global_by_event_name`.`EVENT_NAME`
AS `event_name`,`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_COUNT_USED`
AS `current_count`,format_bytes(`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED`)
AS `current_alloc`,format_bytes(ifnull((`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` / nullif(`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_COUNT_USED`,0)),0))
AS `current_avg_alloc`,`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_COUNT_USED`
AS `high_count`,format_bytes(`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_NUMBER_OF_BYTES_USED`)
AS `high_alloc`,format_bytes(ifnull((`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_NUMBER_OF_BYTES_USED` / nullif(`performance_schema`.`memory_summary_global_by_event_name`.`HIGH_COUNT_USED`,0)),0))
AS `high_avg_alloc` from `performance_schema`.`memory_summary_global_by_event_name`
where (`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` > 0)
order by `performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED` desc
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci從 performance_schema 中讀取源數(shù)據(jù)，并進行格式化處理，大大提升了可讀性。同理，其他視圖也如此。
3.2 查看各線程內(nèi)存分布
查看各線程的內(nèi)存使用詳情：
-- 按歷史總消耗內(nèi)存排序
-- 這里因為要按 total_allocated 列排序，所以查詢原始視圖
greatsql> SELECT
*
FROM
sys.x$memory_by_thread_by_current_bytes
ORDER BY
total_allocated DESC
LIMIT 20;
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 57 | group_rpl/THD_applier_module_receiver | 87 | 62603 | 719.5747 | 21888 | 35248068439 |
| 33632 | root@localhost | 30 | 8592036 | 286401.2000 | 8388736 | 1450180050 |
| 45 | innodb/clone_gtid_thread | 5530 | 1916646 | 346.5906 | 1242184 | 328052882 |
| 34281 | root@localhost | 21 | 44051 | 2097.6667 | 20576 | 286781508 |
| 34273 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 274540679 |
| 34271 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 273058531 |
| 34282 | root@localhost | 21 | 44003 | 2095.3810 | 20576 | 272966254 |
| 34275 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 261564478 |
| 34274 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 240307573 |
| 34280 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 238306694 |
| 34284 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 235438640 |
| 34272 | root@localhost | 21 | 44051 | 2097.6667 | 20576 | 232405048 |
| 34283 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 226022807 |
| 34270 | root@localhost | 21 | 44051 | 2097.6667 | 20576 | 222124926 |
| 34277 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 216611682 |
| 34268 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 216088005 |
| 34269 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 215724518 |
| 34276 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 215354247 |
| 34286 | root@localhost | 20 | 43707 | 2185.3500 | 20576 | 214817414 |
| 34278 | root@localhost | 18 | 41387 | 2299.2778 | 20576 | 213726193 |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
-- 按當前內(nèi)存消耗排序
-- 已默認按 current_allocated 排序，所以無需查詢原始視圖
SELECT
*
FROM
sys.memory_by_thread_by_current_bytes
LIMIT 20;
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 44680 | root@localhost | 91 | 16.21 MiB | 182.42 KiB | 16.00 MiB | 408.03 MiB |
| 45 | innodb/clone_gtid_thread | 5932 | 1.96 MiB | 346 bytes | 1.32 MiB | 327.43 MiB |
| 1 | sql/main | 4938 | 1.30 MiB | 276 bytes | 427.63 KiB | 8.61 MiB |
| 22 | innodb/log_writer_thread | 2347 | 293.38 KiB | 128 bytes | 293.38 KiB | 293.38 KiB |
| 63 | group_rpl/THD_mysql_thread | 208 | 182.98 KiB | 900 bytes | 130.20 KiB | 378.95 KiB |
| 57 | group_rpl/THD_applier_module_receiver | 87 | 61.14 KiB | 719 bytes | 21.38 KiB | 36.14 GiB |
| 59 | sql/replica_sql | 68 | 59.56 KiB | 896 bytes | 16.04 KiB | 129.57 KiB |
| 60 | sql/replica_worker | 31 | 44.04 KiB | 1.42 KiB | 16.04 KiB | 53.38 KiB |
| 45888 | root@localhost | 22 | 43.31 KiB | 1.97 KiB | 20.09 KiB | 312.29 MiB |
| 45897 | root@localhost | 22 | 43.31 KiB | 1.97 KiB | 20.09 KiB | 369.21 MiB |
| 45899 | root@localhost | 22 | 43.31 KiB | 1.97 KiB | 20.09 KiB | 315.29 MiB |
| 45905 | root@localhost | 22 | 43.31 KiB | 1.97 KiB | 20.09 KiB | 317.19 MiB |
| 45908 | root@localhost | 22 | 43.31 KiB | 1.97 KiB | 20.09 KiB | 307.82 MiB |
| 45890 | root@localhost | 21 | 43.02 KiB | 2.05 KiB | 20.09 KiB | 400.17 MiB |
| 45891 | root@localhost | 21 | 43.02 KiB | 2.05 KiB | 20.09 KiB | 336.53 MiB |
| 45886 | root@localhost | 21 | 43.02 KiB | 2.05 KiB | 20.09 KiB | 324.93 MiB |
| 45889 | root@localhost | 21 | 43.02 KiB | 2.05 KiB | 20.09 KiB | 303.29 MiB |
| 45907 | root@localhost | 21 | 43.02 KiB | 2.05 KiB | 20.09 KiB | 309.84 MiB |
| 45911 | root@localhost | 21 | 43.02 KiB | 2.05 KiB | 20.09 KiB | 308.27 MiB |
| 45919 | root@localhost | 21 | 42.97 KiB | 2.05 KiB | 20.09 KiB | 306.36 MiB |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+同樣地，還可以和 performance_schema.threads 關(guān)聯(lián)查詢，就可以找到相應線程/會話中可能正在運行的 SQL 請求。
從查詢結(jié)果明顯可知，是由于當前有大量 root@localhost 連接會話執(zhí)行 LOAD DATA 導入數(shù)據(jù)，這些會話占用了較多內(nèi)存。
3.3 查看各用戶內(nèi)存分配
如果懷疑是某個用戶的查詢導致內(nèi)存消耗過高，還可按用戶分別統(tǒng)計：
greatsql> SELECT
*
FROM
sys.memory_by_user_by_current_bytes;
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| background | 13993 | 3.99 MiB | 298 bytes | 1.33 MiB | 40.07 GiB |
| root | 859 | 2.76 MiB | 3.29 KiB | 1.00 MiB | 3.95 TiB |
| event_scheduler | 3 | 16.27 KiB | 5.42 KiB | 16.04 KiB | 16.27 KiB |
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+看到 root 用戶歷史上總消耗了 3.95 TB 內(nèi)存，可見它的嫌疑最大。執(zhí)行 SHOW PROCESSLIST 可以看到當前 root 用戶在反復執(zhí)行并發(fā)導入大量數(shù)據(jù)，這個原因造成了內(nèi)存總消耗超過較大，等待導入完成后，自然就會回收釋放。
綜合以上兩種分析方法和過程，基本上可以排查定位是什么原因?qū)е?mysqld 進程占用過多內(nèi)存。
4. 檢查內(nèi)存分配的主要可能原因
4.1 InnoDB 內(nèi)存相關(guān)設置
InnoDB 模塊可能消耗大量內(nèi)存，以下參數(shù)需要關(guān)注：
- innodb_buffer_pool_size IBP緩沖池。
- innodb_log_buffer_size 事務日志緩沖區(qū)。
- innodb_adaptive_hash_index 自適應哈希索引，默認開啟，可能占用額外內(nèi)存。建議關(guān)閉。
- innodb_buffer_pool_instances 緩沖池分區(qū)數(shù)量，過多分區(qū)可能引起額外內(nèi)存開銷。
分別檢查確認這些參數(shù)設置情況：
greatsql> SHOW GLOBAL VARIABLES LIKE 'innodb%';4.2 臨時表內(nèi)存
如果查詢生成大量臨時表，可能會占用內(nèi)存。以下參數(shù)決定了臨時表的大小和行為：
- tmp_table_size 和 max_heap_table_size：
greatsql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
greatsql> SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';- 臨時表的創(chuàng)建數(shù)量：
greatsql> SHOW GLOBAL STATUS LIKE 'Created_tmp%';4.3 線程/會話內(nèi)存
高并發(fā)會導致內(nèi)存分配超標，尤其是以下參數(shù)：
- thread_stack 每個線程的棧大小，默認 256KB。
- read_buffer_size / read_rnd_buffer_size / join_buffer_size / sort_buffer_size 線程級分配的內(nèi)存緩沖區(qū)。
4.4 復雜查詢的內(nèi)存消耗
復雜的排序、聯(lián)接、子查詢等操作會額外分配內(nèi)存緩沖區(qū)，如果有較多的慢查詢也表明可能存在一些消耗較多內(nèi)存的查詢請求，可以通過查詢以下變量確認消耗：
greatsql> SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
greatsql> SHOW GLOBAL STATUS LIKE 'Select_full_join';
greatsql> SHOW GLOBAL STATUS LIKE 'Slow_queries';4.5 表緩存與元數(shù)據(jù)緩存
表和源數(shù)據(jù)緩存 table_open_cache 和 table_definition_cache 也可能占用較多內(nèi)存：
greatsql> SHOW VARIABLES LIKE 'table_open_cache';
greatsql> SHOW VARIABLES LIKE 'table_definition_cache';5. 分析排查方法總結(jié)
- 確認內(nèi)存使用是否超標：結(jié)合系統(tǒng)工具與 GreatSQL 內(nèi)部視圖分析。
- 確定具體內(nèi)存分配模塊：通過 performance schema 或 sys schema 系統(tǒng)視圖查詢。
- 檢查確認內(nèi)存、緩沖等相關(guān)參數(shù)是否設置合理：
如果臨時表消耗過高，降低 tmp_table_size 和 max_heap_table_size。
如果線程占用過多內(nèi)存，調(diào)整 read_buffer_size 和 join_buffer_size。
如果 IBP 占用過多內(nèi)存，則適當調(diào)低 innodb_buffer_pool_size，一般上限設置為物理內(nèi)存的 70% 左右。
- 優(yōu)化查詢和索引設計，避免復雜查詢和不必要的臨時表創(chuàng)建。
- 優(yōu)化慢查詢 SQL 請求，避免低效率的 SQL 請求消耗過多CPU、內(nèi)存及磁盤 I/O 資源，并對其他 SQL 請求造成間接關(guān)聯(lián)影響。
相信通過以上方法，基本上可以分析定位并解決 mysqld 進程內(nèi)存占用異常的問題。
6. 如何避免 GreatSQL 消耗過多內(nèi)存
從上面的分析排查過程及思路中，也就知道了有哪些方法可以避免讓 GreatSQL 在運行過程中消耗太多內(nèi)存，以下是幾條建議：
- 采用 jemalloc 代替 glibc 自帶的 malloc 庫，其優(yōu)勢在于減少內(nèi)存碎片和提升高并發(fā)場景下內(nèi)存的分配效率，提高內(nèi)存管理效率的同時還能降低數(shù)據(jù)庫運行時發(fā)生 OOM 的風險。在本案例中，原來 mysqld 進程最高跑到27.8G(占物理內(nèi)存90%)，改用 jemalloc 后最高只跑到24.2G(占物理內(nèi)存78.7%)，效果相當顯著。
- 根據(jù)數(shù)據(jù)庫負載以及業(yè)務特征，設置合適的 IBP 值，一般上限設置為物理內(nèi)存的 70% 左右，設置過大容易造成 OOM。同時也要根據(jù)實際情況，適當調(diào)整（或調(diào)低）會話級緩沖池，包括 tmp_table_size / max_heap_table_size / read_buffer_size / read_rnd_buffer_size / join_buffer_size / sort_buffer_size / thread_stack 等多個參數(shù)，同時也要適當控制最大連接數(shù)參數(shù) max_connection。
- 加強監(jiān)控，及時發(fā)現(xiàn)并處理一些消耗內(nèi)存較大的 SQL 操作，比如大事務（把大事務拆分成多個小事務）、長事務（長時間不提交的事務要做好監(jiān)控并發(fā)出告警，甚至主動終止這些事務），以及全表掃描、分組、排序、多表聯(lián)接（是否可以添加合適的索引）等類型，這種請求通常比較容易產(chǎn)生臨時表、臨時文件，通常也是慢查詢 SQL，需要重點關(guān)注。要定時巡查并優(yōu)化這些慢查詢 SQL。
重點做好上面這幾點，基本上就能避免大部分容易造成 mysqld 消耗內(nèi)存過多的情況，讓 GreatSQL 運行的更絲滑平穩(wěn)。
責任編輯：武曉燕
來源：
GreatSQL社區(qū)
