GreatSQL內存消耗異常排查攻略：從系統到應用層面的深入分析

作者：YeJinrong/葉金榮 2024-11-29 13:02:10

本文和大家試著使用 GreatSQL 中的 sys schema 和 performance_schema 進行深入分析，找出內存消耗大戶的源頭，并盡可能解決問題。

當 GreatSQL 數據庫處于高并發高負載時，可能會發現 mysqld 進程的內存消耗遠遠超出設置的 innodb_buffer_pool_size 時，有時候甚至會高達甚至超過系統內存的90%，遇到這種問題時，心里經常會發慌，擔心下一秒內存就會爆了發生 OOM，或者數據庫hang死不響應。

本文和大家試著使用 GreatSQL 中的 sys schema 和 performance_schema 進行深入分析，找出內存消耗大戶的源頭，并盡可能解決問題。

下面是詳細的排查方法和步驟。

1. 確認實際內存消耗

1.1 操作系統層面分析

先檢查確認 mysqld 進程的內存具體消耗占用情況，做到心里有數，避免真的下一秒就發生 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

在上述結果中重點關注幾個指標：

RES（Resident Memory）：物理內存占用，約 27.7G。
VIRT（Virtual Memory）：虛擬地址空間大小，約 30.6G。

看到 mysqld 進程當前內存消耗占比約 90%，還算可控，沒到火燒眉毛的境地。

繼續使用 pmap 查看 mysqld 進程中的內存分布情況：

$ 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 ]

看到大量的匿名內存（anon）消耗較多內存，這可能是由以下幾個原因引起的：

動態分配的內存：

GreatSQL 在運行過程中會頻繁地進行內存分配和釋放，這些內存通常是以匿名映射的形式存在于進程的虛擬地址空間中。

例如，GreatSQL 的線程池、緩存、臨時表等都會動態分配內存。

緩沖區和緩存：

GreatSQL 使用大量的緩沖區和緩存來提高性能，例如InnoDB Buffer Pool（以下簡稱 IBP）、InnoDB Log Buffer等。

這些緩沖區和緩存通常會占用大量的匿名內存。

線程堆棧：

每個線程都有自己的堆棧空間，這些堆棧空間也是匿名內存的一部分。

為了響應用戶請求而創建了大量線程，那么這些線程的堆棧空間會占用不少內存。

臨時文件：

GreatSQL 在處理大查詢或排序操作時，可能會使用臨時表、臨時文件，其內存映射也會占用匿名內存。

內存泄漏：

如果 GreatSQL 存在內存泄漏問題，也會導致匿名內存不斷增加。

可以針對上述各個模塊/維度做進一步排查分析。

1.2 檢查 IBP 內存相關配置

使用以下 SQL 命令查詢 IBP 等內存消耗較多的相關模塊內存配置

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 進程的內存占用為 27.7G，超過 IBP 較多，這可能是由于用戶的 SQL 請求（比如效率較低的慢查詢 SQL）其他模塊或線程引起。還需要繼續排查。

2. 利用 Performance Schema 排查內存消耗來源

從 5.6.6 版本開始，Performance Schema 默認啟用，是一個內置的性能診斷工具，用于實時監控和分析 GreatSQL 服務器的運行狀態。它提供了詳細的性能數據，包括內存分配的全局視圖、SQL 語句的執行時間、線程活動、鎖等待等詳細信息，幫助開發者和 DBA 識別和解決性能瓶頸。

2.1 按內存模塊查看占用

使用 memory_summary_global_by_event_name 按模塊查看內存分配情況：

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：具體內存分配的模塊名稱，如 memory/innodb/buffer_pool、memory/sql/temporary_table 等。
CURRENT_NUMBER_OF_BYTES_USED：當前分配的內存總量。

例如：

如果 memory/innodb/buf_buf_pool 值較高，說明 InnoDB buffer pool 占用較高。其他幾個包含 memory/innodb 關鍵字的，也都是和 InnoDB 存儲引擎相關的內存模塊。
其中 memory/mysys/MY_BITMAP::bitmap 主要用于管理位圖數據結構 (bitmap) 的內存使用。它的設計初衷是為了實現高效的位存儲與處理，主要用于存儲和操作需要標志位（bit flag）來跟蹤或控制的數據。其具體存儲的數據包括但不限于：

索引或表分區的狀態：位圖被用來記錄索引或分區的使用狀態。例如，在分區表掃描時，通過位圖可以高效管理哪些分區需要掃描或已經掃描。

事務或鎖狀態：記錄事務的特定標志位或鎖的使用狀態，比如資源鎖（resource locks）的分配狀態。

InnoDB 的內部操作：位圖被用于跟蹤一些內部存儲引擎的優化過程，例如自適應哈希索引、頁的臟位（dirty bit）標記等。

線程管理：管理線程池中線程的分配和使用狀態。

性能統計：在某些性能分析的場景下，位圖用于記錄啟用或禁用的統計模塊。

其中 memory/group_rpl/GCS_XCom::xcom_cache 是 MGR Xcom cache，在 GreatSQL 8.0.32-26 中初始默認值即為 1GB，詳情參考 Xcom cache分配靜態化。其他幾個包含 group_rpl 關鍵字特征的，也是和 MGR 相關的模塊。
其中 memory/mysys/IO_CACHE 是一個重要的內存管理模塊，主要用于管理和優化文件I/O操作。IO_CACHE 提供了一個高效的緩存機制，可以顯著提高文件讀寫操作的性能。主要存儲的數據有：
臨時文件數據：排序、分組、聯接等操作過程中生成的中間結果。
二進制日志：binlog 的寫入和讀取操作中使用緩存。
文件塊：GreatSQL 訪問文件時，將數據塊加載到緩存中，避免重復讀取。
表數據：表掃描或索引掃描時，用于緩存表或索引的數據。
如果 memory/sql/temporary_table 值較高，說明內存被臨時表消耗。
如果 memory/innodb/hash_index 值較高，可能是 InnoDB 的自適應哈希索引占用內存。

上面的查詢結果表明，memory/innodb/buf_buf_pool(IBP) 占用內存約 20G，memory/group_rpl/GCS_XCom::xcom_cache(MGR XCom Cache) 占用內存約 1G，都是符合預期的。但是 memory/mysys/IO_CACHE 占用的內存較高，需要重點排查。

2.2 跟蹤各模塊內存使用變化

可以每間隔一段時間重復執行下面的 SQL 請求，觀察各個模塊的內存消耗變化，找出內存消耗增長較快的模塊，它們可能就是導致 mysqld 進程消耗較大內存的“元兇”。

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 |
+---------------------------------------------+------------------+

結合前面各模塊當前占用的內存情況，從上述查詢結果綜合分析看，較大概率應該就是 memory/mysys/IO_CACHE 模塊消耗內存過大。

2.3 按線程查看內存占用

接著繼續查看各線程內存占用情況，確認是否有個別線程（尤其是長連接線程）消耗了過多內存資源。使用 memory_summary_by_thread_by_event_name 查看各線程的內存分配，同時關聯查詢 threads 視圖，可以顯示各線程當前正在執行的 SQL 請求及其執行耗時：

-- 1. 查看各線程當前的內存分配情況
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. 查看各線程匯總的內存分配情況
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                    |
+----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+

從上面的查詢結果可見，當前有較多的 LOAD DATA 請求正在運行，有可能是它們導致的內存占用較高的原因。

其中

CURRENT_NUMBER_OF_BYTES_USED 表示當前分配但尚未釋放的內存塊的聚合大小。CURRENT_NUMBER_OF_BYTES_USED = SUM_NUMBER_OF_BYTES_ALLOC ? SUM_NUMBER_OF_BYTES_FREE。
SUM_NUMBER_OF_BYTES_ALLOC 表示已分配內存塊的聚合大小。
SUM_NUMBER_OF_BYTES_FREE 表示已釋放內存塊的聚合大小。

排查分析道這里，基本上可以推斷是由于有大量并發 LOAD DATA 導入數據請求導致 mysqld 內存占用較高。

3. 利用 sys schema 簡化分析

相對于用 Performance Schema 排查分析，采用 sys schema 分析則更簡單省事。接下來介紹如何利用 sys schema 分析。

GreatSQL sys schema 是一組視圖、存儲過程和函數的集合，它基于 performance_schema 提供了更易讀和易用的性能數據匯總。sys schema 通過簡化復雜的性能指標，幫助數據庫管理員和開發人員快速診斷和優化 GreatSQL 的性能問題。

3.1 查看全局及各模塊內存分布

首先，查看當前全部內存分配情況：

greatsql> USE sys;
greatsql> SELECT * FROM memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 22.08 GiB       |
+-----------------+

在 IBP 設置為 20G 的前提下，從 memory_global_total 查詢到的內存分配總數并沒有超過太多，說明較大可能性是由于用戶的 SQL 請求（比如效率較低的慢查詢 SQL）或其他模塊引起。

繼續查詢內存使用的全局分布情況：

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 中，大部分視圖都同時存儲原始數據以及格式化后可讀性更強的兩種視圖。所以上面的 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 |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+

從上面兩個查詢結果可知，除了 IBP 和 MGR 之外，模塊 memory/mysys/IO_CACHE 占用的內存最高，是重點分析排查對象。

查看 sys.memory_global_by_current_bytes 視圖定義，可知它的原始數據來自 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 中讀取源數據，并進行格式化處理，大大提升了可讀性。同理，其他視圖也如此。

3.2 查看各線程內存分布

查看各線程的內存使用詳情：

-- 按歷史總消耗內存排序
-- 這里因為要按 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 |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+

-- 按當前內存消耗排序
-- 已默認按 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 關聯查詢，就可以找到相應線程/會話中可能正在運行的 SQL 請求。

從查詢結果明顯可知，是由于當前有大量 root@localhost 連接會話執行 LOAD DATA 導入數據，這些會話占用了較多內存。

3.3 查看各用戶內存分配

如果懷疑是某個用戶的查詢導致內存消耗過高，還可按用戶分別統計：

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 內存，可見它的嫌疑最大。執行 SHOW PROCESSLIST 可以看到當前 root 用戶在反復執行并發導入大量數據，這個原因造成了內存總消耗超過較大，等待導入完成后，自然就會回收釋放。

綜合以上兩種分析方法和過程，基本上可以排查定位是什么原因導致 mysqld 進程占用過多內存。

4. 檢查內存分配的主要可能原因

4.1 InnoDB 內存相關設置

InnoDB 模塊可能消耗大量內存，以下參數需要關注：

innodb_buffer_pool_size IBP緩沖池。
innodb_log_buffer_size 事務日志緩沖區。
innodb_adaptive_hash_index 自適應哈希索引，默認開啟，可能占用額外內存。建議關閉。
innodb_buffer_pool_instances 緩沖池分區數量，過多分區可能引起額外內存開銷。

分別檢查確認這些參數設置情況：

greatsql> SHOW GLOBAL VARIABLES LIKE 'innodb%';

4.2 臨時表內存

如果查詢生成大量臨時表，可能會占用內存。以下參數決定了臨時表的大小和行為：

tmp_table_size 和 max_heap_table_size：

greatsql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
greatsql> SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

臨時表的創建數量：

greatsql> SHOW GLOBAL STATUS LIKE 'Created_tmp%';

4.3 線程/會話內存

高并發會導致內存分配超標，尤其是以下參數：

thread_stack 每個線程的棧大小，默認 256KB。
read_buffer_size / read_rnd_buffer_size / join_buffer_size / sort_buffer_size 線程級分配的內存緩沖區。

4.4 復雜查詢的內存消耗

復雜的排序、聯接、子查詢等操作會額外分配內存緩沖區，如果有較多的慢查詢也表明可能存在一些消耗較多內存的查詢請求，可以通過查詢以下變量確認消耗：

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 表緩存與元數據緩存

表和源數據緩存 table_open_cache 和 table_definition_cache 也可能占用較多內存：

greatsql> SHOW VARIABLES LIKE 'table_open_cache';
greatsql> SHOW VARIABLES LIKE 'table_definition_cache';

5. 分析排查方法總結

確認內存使用是否超標：結合系統工具與 GreatSQL 內部視圖分析。
確定具體內存分配模塊：通過 performance schema 或 sys schema 系統視圖查詢。
檢查確認內存、緩沖等相關參數是否設置合理：

如果臨時表消耗過高，降低 tmp_table_size 和 max_heap_table_size。

如果線程占用過多內存，調整 read_buffer_size 和 join_buffer_size。

如果 IBP 占用過多內存，則適當調低 innodb_buffer_pool_size，一般上限設置為物理內存的 70% 左右。

優化查詢和索引設計，避免復雜查詢和不必要的臨時表創建。
優化慢查詢 SQL 請求，避免低效率的 SQL 請求消耗過多CPU、內存及磁盤 I/O 資源，并對其他 SQL 請求造成間接關聯影響。

相信通過以上方法，基本上可以分析定位并解決 mysqld 進程內存占用異常的問題。

6. 如何避免 GreatSQL 消耗過多內存

從上面的分析排查過程及思路中，也就知道了有哪些方法可以避免讓 GreatSQL 在運行過程中消耗太多內存，以下是幾條建議：

采用 jemalloc 代替 glibc 自帶的 malloc 庫，其優勢在于減少內存碎片和提升高并發場景下內存的分配效率，提高內存管理效率的同時還能降低數據庫運行時發生 OOM 的風險。在本案例中，原來 mysqld 進程最高跑到27.8G(占物理內存90%)，改用 jemalloc 后最高只跑到24.2G(占物理內存78.7%)，效果相當顯著。
根據數據庫負載以及業務特征，設置合適的 IBP 值，一般上限設置為物理內存的 70% 左右，設置過大容易造成 OOM。同時也要根據實際情況，適當調整（或調低）會話級緩沖池，包括 tmp_table_size / max_heap_table_size / read_buffer_size / read_rnd_buffer_size / join_buffer_size / sort_buffer_size / thread_stack 等多個參數，同時也要適當控制最大連接數參數 max_connection。
加強監控，及時發現并處理一些消耗內存較大的 SQL 操作，比如大事務（把大事務拆分成多個小事務）、長事務（長時間不提交的事務要做好監控并發出告警，甚至主動終止這些事務），以及全表掃描、分組、排序、多表聯接（是否可以添加合適的索引）等類型，這種請求通常比較容易產生臨時表、臨時文件，通常也是慢查詢 SQL，需要重點關注。要定時巡查并優化這些慢查詢 SQL。

重點做好上面這幾點，基本上就能避免大部分容易造成 mysqld 消耗內存過多的情況，讓 GreatSQL 運行的更絲滑平穩。

責任編輯：武曉燕來源： GreatSQL社區

GreatSQL 內存系統

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看