GaussDB(DWS)动态内存高的统计脚本

该脚本统计在动态内存高场景下每个实例的动态内存使用情况及各实例top3的语句，适用于8.1.3.x及以上版本：

-- 脚本作用: 对当前各实例动态内存进行诊断，分析active/idle/shactx内存占比，并找到内存占用最高的活跃query
-- 输出参数说明: 
-- nodename                   : 节点名
-- "max_dynamic_memory(MB)"   : 最大可用动态内存
-- "dynamic_used_memory(MB)"  : 当前已用动态内存
-- "dynamic_ratio(%)"         : 当前已用动态内存/最大可用动态内存
-- "active_memory(MB)"        : 活跃线程已用内存
-- "active_ratio(%)"          : 活跃线程已用内存/当前已用动态内存
-- "idle_memory(MB)"          : idle线程已用内存
-- "idle_ratio(%)"            : idle线程已用内存/当前已用动态内存
-- "dynamic_used_shrctx(MB)"  : 动态共享ctx已用内存
-- "shactx_ratio(%)"          : 动态共享ctx已用内存/当前已用动态内存
-- query_info                 : 活跃sql信息

SELECT nodename,
       max_dynamic_memory  as "max_dynamic_memory(MB)",
       dynamic_used_memory as "dynamic_used_memory(MB)",
       dynamic_ratio       as "dynamic_ratio(%)",
       active_memory       as "active_memory(MB)",
       active_ratio        as "active_ratio(%)",
       idle_memory         as "idle_memory(MB)",
       idle_ratio          as "idle_ratio(%)",
       dynamic_used_shrctx as "dynamic_used_shrctx(MB)",
       shactx_ratio        as "shactx_ratio(%)",
       string_agg(query_sql,chr(10) || chr(10) order by query_total_mem desc) as query_info
  FROM pgxc_parallel_query('all', '
select t1.nodename,t1.max_dynamic_memory,t1.dynamic_used_memory,(dynamic_used_memory/max_dynamic_memory*100)::numeric(5,2) as dynamic_util,t2.active_memory,(t2.active_memory/t1.dynamic_used_memory*100)::numeric(5,2) as active_ratio,t2.idle_memory,(t2.idle_memory/t1.dynamic_used_memory*100)::numeric(5,2) as idle_ratio,t1.dynamic_used_shrctx,(t1.dynamic_used_shrctx/t1.dynamic_used_memory*100)::numeric(5,2) as shactx_ratio,t3.query_sql,t3.query_total_mem from (
select nodename,
sum(case when memorytype = ''max_dynamic_memory'' then memorymbytes end)::int as max_dynamic_memory,
sum(case when memorytype = ''dynamic_used_memory'' then memorymbytes end)::int as dynamic_used_memory,
sum(case when memorytype = ''dynamic_used_shrctx'' then memorymbytes end)::int as dynamic_used_shrctx
from pv_total_memory_Detail
group by nodename) t1
left join (
select pgxc_node_str() as nodename,
(sum(case when s.state = ''idle'' then totalsize end)/1024/1024)::int as idle_memory,
(sum(case when s.state <> ''idle'' then totalsize end)/1024/1024)::int as active_memory
from pv_session_memory_detail p inner join pg_stat_get_activity_with_conninfo(NULL::bigint) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, enqueue, query_id, connection_info, lwtid, stmt_type) on s.pid = split_part(p.sessid,''.'',2)) t2
on t1.nodename = t2.nodename
left join(select pgxc_node_str() as nodename,(sum(totalsize)/1024/1024)::int as query_total_mem, (E''\ntotal_memory: \t'' || (sum(totalsize)/1024/1024)::int || E''MB  \nquery id: \t'' || query_id ||  E''\nquery: \t'' || substr(query,1,100)) as query_sql FROM pv_session_memory_detail, pg_stat_get_activity_with_conninfo(NULL::bigint) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, enqueue, query_id, connection_info, lwtid, stmt_type) WHERE pid=split_part(sessid,''.'',2) and state <> ''idle'' GROUP BY query_id,query ORDER BY sum(totalsize) DESC limit 3) t3 
on t1.nodename = t3.nodename;
') AS t(nodename text, max_dynamic_memory int, dynamic_used_memory int, dynamic_ratio numeric, active_memory int, active_ratio numeric, idle_memory int, idle_ratio numeric, dynamic_used_shrctx int, shactx_ratio numeric, query_sql text, query_total_mem int)
 group by nodename,
          max_dynamic_memory,
          dynamic_used_memory,
          dynamic_ratio,
          active_memory,
          active_ratio,
          idle_memory,
          idle_ratio,
          dynamic_used_shrctx,
          shactx_ratio
 order by dynamic_ratio desc
 limit 10;

效果如下：