內核不中斷前提下，Gaussdb（DWS）記憶體報錯排查方法

摘要：本文主要講解如何在內核保證操作不能中斷采取的特殊處理，理論上用戶執行的sql使用的記憶體（dynamic_used_memory） 是不會大范圍的超過max_dynamic_memory的記憶體的

本文分享自華為云社區《Gaussdb（DWS）記憶體報錯排查方法》，作者： fighttingman，

Gaussdb記憶體布局

記憶體背景關系memoryContext記憶體結構

一、記憶體問題定位方法

分析場景1：資料庫日志出現ERROR：memory is temporarily unavailable

從報錯資訊可以找到是哪個節點的記憶體不足，例如 dn_6003_6004: memory is temporarily unavailable ， 這個就是dn_6003的記憶體不足了

1.從日志分析

觀察對應dn的日志，是否為“reaching the database memory limitation”，表示為資料庫的邏輯記憶體管理機制保護引起，需要進一步分析資料庫的視圖；若為“reaching the OS memory limitation”，表示為作業系統記憶體分配失敗引起，需要查看作業系統引數配置及記憶體硬體情況等，

1）reaching the database memory limitation情況實體

----debug_query_id=76279718689098154, memory allocation failed due to reaching the database memory limitation. Current thread is consuming about 10 MB, allocating 240064 bytes.
----debug_query_id=76279718689098154, Memory information of whole process in MB:max_dynamic_memory: 18770, dynamic_used_memory: 18770, dynamic_peak_memory: 18770, dynamic_used_shrctx: 1804, dynamic_peak_shrctx: 1826, max_sctpcomm_memory: 4000, sctpcomm_used_memory: 1786, sctpcomm_peak_memory: 1786, comm_global_memctx: 0, gpu_max_dynamic_memory: 0, gpu_dynamic_used_memory: 0, gpu_dynamic_peak_memory: 0, large_storage_memory: 0, process_used_memory: 22105, cstore_used_memory: 1022, shared_used_memory: 2605, other_used_memory: 0, os_totalmem: 257906, os_freemem: 16762.

此時，作業76279718689098154準備申請240064 bytes記憶體，dynamic_used_memory記憶體值為18770MB，二者之和大于max_dynamic_memory（18770MB），超出資料庫限制，記憶體申請失敗，

在811之后的版本還會列印top3的memoryContext記憶體占用，示例如下

----debug_query_id=72339069014641088, sessId: 1670914731.140604465997568.coordinator1, sessType: postgres, contextName: ExprContext, level: 5, parent: FunctionScan_140604465997568, totalSize: 950010640, freeSize: 0, usedSize: 950010640
----debug_query_id=72339069014641053, pid=140604465997568, application_name=gsql, query=select * from pv_total_memory_detail, state=retrying, query_start=2022-12-13 14:59:22.059805+08, enqueue=no waiting queue, connection_info={"driver_name":"gsql","driver_version":"(GaussDB 8.2.0 build bc4cec20) compiled at 2022-12-13 14:45:14 commit 3629 last mr 5138 debug","driver_path":"/data3/x00574567/self/gaussdb/mppdb_temp_install/bin/gsql","os_user":"x00574567"}
----debug_query_id=72339069014641088, sessId: 1670914731.140604738627328.coordinator1, sessType: postgres, contextName: ExprContext, level: 5, parent: FunctionScan_140604738627328, totalSize: 900010080, freeSize: 0, usedSize: 900010080
----debug_query_id=72339069014641057, pid=140604738627328, application_name=gsql, query=select * from pv_total_memory_detail, state=retrying, query_start=2022-12-13 14:59:22.098775+08, enqueue=no waiting queue, connection_info={"driver_name":"gsql","driver_version":"(GaussDB 8.2.0 build bc4cec20) compiled at 2022-12-13 14:45:14 commit 3629 last mr 5138 debug","driver_path":"/data3/x00574567/self/gaussdb/mppdb_temp_install/bin/gsql","os_user":"x00574567"}
----debug_query_id=72339069014641088, sessId: 1670914731.140603779163904.coordinator1, sessType: postgres, contextName: ExprContext, level: 5, parent: FunctionScan_140603779163904, totalSize: 890009968, freeSize: 0, usedSize: 890009968
----debug_query_id=72339069014641058, pid=140603779163904, application_name=gsql, query=select * from pv_total_memory_detail, state=retrying, query_start=2022-12-13 14:59:22.117463+08, enqueue=no waiting queue, connection_info={"driver_name":"gsql","driver_version":"(GaussDB 8.2.0 build bc4cec20) compiled at 2022-12-13 14:45:14 commit 3629 last mr 5138 debug","driver_path":"/data3/x00574567/self/gaussdb/mppdb_temp_install/bin/gsql","os_user":"x00574567"}
----allBackendSize=34, idleSize=7, runningSize=7, retryingSize=20

重要欄位解釋：

sessId：執行緒啟動時間+執行緒標識（字串資訊為timestamp.threadid）

sessType：執行緒名稱

contextName：memoryContext名字

totalSize：記憶體占用大小，單位Byte

freeSize：當前memoryContext釋放記憶體總數，單位Byte

usedSize：當前memoryContext已使用的記憶體總數，單位Byte

application_name：連接到該后端的應用名

query：查詢陳述句

enqueue：排隊情況

allBackendSize：總執行緒個數，idleSize：idle執行緒個數，runningSize：活躍的執行緒個數，retryingSize：重試的執行緒個數

資料庫還會在復雜作業中進行檢查，查看復雜作業預估記憶體是否超過實際使用記憶體，如果存在，則列印下列資訊，供分析，

----debug_query_id=76279718689098154, Total estimated Memory is 15196 MB, total current cost Memory is 16454 MB, the difference is 1258 MB.The count of complicated queries is 17 and the count of uncontrolled queries is 1.

上述資訊表示全部復雜作業預計使用記憶體15196 MB，實際使用16454 MB，超出1258 MB，

復雜作業共17個，其中有1個作業實際使用記憶體超過預計記憶體，

----debug_query_id=76279718689098154, The abnormal query thread id 140664667547392.It current used memory is 13618 MB and estimated memory is 1102 MB.It also is the query which costs the maximum memory.

上述資訊表示，例外執行緒id為140664667547392，該執行緒預估消耗記憶體1102MB，實際消耗13618MB，

----debug_query_id=76279718689098154, It is not the current session and beentry info : datid<16389>, app_name<cn_5001>, query_id<76279718688746485>, tid<140664667547392>, lwtid<173496>, parent_tid<0>, thread_level<0>, query_string<explainperformance with ws as (select d_year AS ws_sold_year, ws_item_sk, ws_bill_customer_sk ws_customer_sk, sum(ws_quantity) ws_qty, sum(ws_wholesale_cost) ws_wc, sum(ws_sales_price) ws_sp from web_sales left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk join date_dim on ws_sold_date_sk = d_date_sk where wr_order_number is null group by d_year, ws_item_sk, ws_bill_customer_sk ), cs as (select d_year AS cs_sold_year, cs_item_sk, cs_bill_customer_sk cs_customer_sk, sum(cs_quantity) cs_qty, sum(cs_wholesale_cost) cs_wc, sum(cs_sales_price) cs_sp from catalog_sales left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk join date_dim on cs_sold_date_sk =d_date_sk where cr_order_number is null group by d_year, cs_item_sk, cs_bill_customer_sk ), ss as (select d_year AS ss_sold_year, ss_item_sk, ss_customer_sk, sum(ss_quantity) ss_qty, sum(ss_wholesale_cost) ss_wc, sum(ss_sales_price) ss_spfrom store_sales left join store_returns on sr_ticket_numbe>.

上述資訊進一步顯示記憶體使用超過預估記憶體的作業資訊的sql資訊，其中datid表示資料庫的OID，app_name表示application name，query_string表示查詢sql，

----debug_query_id=76279718689098154, WARNING: the common memory context 'HashContext' is using 1059 MB size larger than 989 MB.----debug_query_id=76279718689098154, WARNING: the common memory context 'VecHashJoin_76279718688746485_6' is using 12359 MB size larger than 10 MB.

上述資訊表示超限的memcontext，76279718689098154號查詢中，memory context預設值的最大值為989MB，實際使用了1059 MB，

2）reaching the OS memory limitation

當GaussDB記憶體使用符合GUC中相關引數限制，但作業系統可用記憶體不足時，會出現與1.1中類似的日志資訊，格式如下

----debug_query_id=%lu, FATAL: memory allocation failed due to reaching the OS memory limitation. Current thread is consuming about %d MB, allocating %ld bytes.
----debug_query_id=%lu, Please check the sysctl configuration and GUC variable max_process_memory.
----debug_query_id=%lu, Memory information of whole process in MB:"
                            "max_dynamic_memory: %d, dynamic_used_memory: %d,
dynamic_peak_memory: %d, dynamic_used_shrctx: %d,
dynamic_peak_shrctx: %d, max_sctpcomm_memory: %d,
sctpcomm_used_memory: %d, sctpcomm_peak_memory: %d,
comm_global_memctx: %d, gpu_max_dynamic_memory: %d,
gpu_dynamic_used_memory: %d,
gpu_dynamic_peak_memory: %d, large_storage_memory: %d,
process_used_memory: %d, cstore_used_memory: %d,
shared_used_memory: %d, other_used_memory: %d,
os_totalmem: %d, os_freemem: %d

其中，os_totalmem是當前OS中的總記憶體，即“free”命令中的total資訊，os_freemem是當前OS中的可用記憶體，即“free”命令中的free資訊，

第一條日志中“allocating %ld bytes”中的待申請的記憶體大于第三條日志中“os_freemem”項，且資料庫可運行，無其他例外，則符合預期，說明OS記憶體不足，

2. 實體每個實體的記憶體使用情況，查詢pgxc_total_memory_detail

記憶體報錯后，陳述句使用的記憶體就會釋放，當時占用記憶體高的陳述句可能會因為報錯，導致現場沒有了，查詢記憶體視圖查詢不到的情況

with a as (select *from pgxc_total_memory_detail where memorytype='dynamic_used_memory'), b as(select * from pgxc_total_memory_detail wherememorytype='dynamic_peak_memory'), c as (select * from pgxc_total_memory_detailwhere memorytype='max_dynamic_memory'), d as (select * frompgxc_total_memory_detail where memorytype='process_used_memory'), e as (select* from pgxc_total_memory_detail where memorytype='other_used_memory'), f as(select * from pgxc_total_memory_detail where memorytype='max_process_memory')select a.nodename,a.memorymbytes as dynamic_used_memory,b.memorymbytes asdynamic_peak_memory,c.memorymbytes as max_dynamic_memory,d.memorymbytes asprocess_used_memory,e.memorymbytes as other_used_memory,f.memorymbytes asmax_process_memory from a,b,c,d,e,f where a.nodename=b.nodename andb.nodename=c.nodename and c.nodename=d.nodename and d.nodename=e.nodename ande.nodename=f.nodename order by a.nodename;

在查詢這個視圖也有可能會因為記憶體不足報memory is temporarily unavailable，導致視圖查不出來，此時需要將disable_memory_protect設定為off，

set disable_memory_protect=off; 之后在查詢視圖就不會報錯，

通過上邊這視圖可以找到集群中哪個節點的記憶體使用有例外，之后連接那個節點通過pv_session_memory_detail視圖找到有問題的memorycontext，

SELECT * FROM pv_session_memory_detail ORDER BY totalsize desc LIMIT 100;

結合pg_stat_activity視圖可以找到哪個陳述句使用的memcontext最多，

select sessid, contextname, level,parent, pg_size_pretty(totalsize) as total ,pg_size_pretty(freesize) as freesize, pg_size_pretty(usedsize) as usedsize, datname,query_id, query from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid order by totalsize desc limit 100;

緊急恢復

EXECUTE DIRECT ON(cn_5001) 'SELECT pg_terminate_backend(139780156290816)';

二、記憶體占用高的場景分析

1.空閑連接過多導致記憶體占用

先確認是哪個實體的記憶體占用高，確認方法如上查詢pgxc_total_memory_detail，之后連上那個cn或者dn查詢如下sql

select b.state, sum(totalsize) as totalsize, sum(freesize) as freesize, sum(usedsize) as usedsize from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid group by b.state order by totalsize desc limit 100;

如果是上圖的idle狀態的totalsize占用很多記憶體，可以嘗試清理idle狀態的空閑連接釋放記憶體

解決措施：清理idle狀態的空閑連接

CLEAN CONNECTION TO ALL FORCE FOR DATABASE xxxx;

clean connection 只能清理pg_pooler_status中 in_used是f狀態的空閑連接，不能清理in_used狀態為t的連接，in_used為t 一般是執行了pbe陳述句導致cn和dn的空閑連接不能釋放導致

如果上邊方法清理不掉，只能嘗試清理cn和客戶端的連接，之后在執行clean connection清理cn和dn之間的連接，可以嘗試在cn上找到是idle狀態的空閑連接，此操作會斷掉cn和客戶端的連接，需要和客戶確認是否可以執行

select 'execute direct on ('||coorname||') ''select pg_terminate_backend('||pid||')'';' from pgxc_stat_activity where usename not in ('Ruby', 'omm') and state='idle';

將select的結果依次執行，

2.陳述句占用記憶體過多，如果第一個步驟中的第一個陳述句查詢的是active狀態的陳述句記憶體占用多，說明是正在執行陳述句占用的記憶體多導致的

查詢下邊的陳述句找到記憶體占用多的陳述句

select b.state as state, a.sessid as sessid, b.query_id as query_id, substr(b.query,1,100) as query, sum(totalsize) as totalsize, sum(freesize) as freesize, sum(usedsize) as usedsize from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid and usename not in ('Ruby', 'omm') group by state,sessid,query_id,query order by totalsize desc limit 100;

找到陳述句后，根據query_id去對應的cn上進行查殺這個例外sql

3.dynamic_used_shrctx記憶體使用較多

dynamic_used_shrctx是共享記憶體背景關系使用的記憶體，也是通過MemoryContext分的，在執行緒之間共享，通過pg_shared_memory_detail視圖查看

select * from pg_shared_memory_detail order by totalsize desc limit 10;

一般共享記憶體背景關系分配和陳述句有關的， contextname都會帶有執行緒號或者query_id，根據query_id或者執行緒號進行查殺例外sql，除此之外共享記憶體背景關系一般是內核中各個模塊使用的記憶體，比如topsql，需要排查記憶體使用是否合理，以及釋放機制，

4. 記憶體視圖pv_total_memory_detail 中，dynamic_used_memory > max_dynamic_memory的情況

1）GUC引數disable_memory_protect為on的時候

2）分配記憶體的時候，debug_query_id為0

3）內核在執行關鍵代碼段的時候

4）內核Postmaster執行緒內的記憶體分配

5）在事務回滾階段

以上情況都是內核保證操作不能中斷采取的特殊處理，理論上用戶執行的sql使用的記憶體（dynamic_used_memory） 是不會大范圍的超過max_dynamic_memory的記憶體的

點擊關注，第一時間了解華為云新鮮技術~

標籤：SQL Server

上一篇：[20230323]ps命令查看thread.txt

下一篇：MySql隨筆記基礎