GaussDB(DWS)应急预案（三）：CPU整体使用率高

select 'execute direct on(' || coorname || ') ''select pg_cancel_backend(' || pid || ')'';', sysdate - query_start as dur, state, query_id, enqueue, query from pgxc_stat_activity where state <> 'idle' and usename <> 'omm' and usename <> 'Ruby' order by dur desc limit 100;

SELECT 'execute direct on(' || s.nodename || ') ''select pg_cancel_backend(' || s.threadid || ')'';', s.node_group::name AS nodegroup,s.resource_pool rpname, CAST(ROUND(res.total_cpu_time/ng.dn_cnt/(res.duration+1),3) AS DOUBLE PRECISION) AS used_cpu, s.elapsed_time AS duration_s, substr(a.query,1,10), a.query_start,s.block_time, res.query_band,s.attribute,s.lane, ( CASE WHEN s.enqueue != 'None' AND s.status = 'pending' THEN 'pending' ELSE 'running' END ) AS status, ( CASE WHEN s.enqueue = 'None' OR s.status != 'pending' THEN 'None' WHEN s.enqueue='Global' THEN 'Global' WHEN s.enqueue = 'CentralQueue' THEN 'CCN' WHEN s.enqueue = 'Respool' THEN 'Respool' WHEN ccn.on AND s.lane = 'slow' THEN 'CCN' ELSE 'Respool' END ) AS queue, res.max_peak_memory AS used_mem,s.statement_mem AS estimate_mem, COALESCE(thread.dn_cnt, 0) AS dn_count,COALESCE(thread.stream_cnt, 0) AS stream_count,a.query_id  FROM pg_catalog.pgxc_session_wlmstat s, pg_catalog.pgxc_stat_activity a, ( SELECT MAX(query_band) AS query_band,MAX(max_peak_memory) AS max_peak_memory,MAX(total_cpu_time) AS total_cpu_time, MAX(duration) AS duration,queryid FROM pg_catalog.pgxc_wlm_session_statistics GROUP BY queryid ) res, ( SELECT (setting='on') AS on FROM pg_catalog.pg_settings WHERE name='enable_dynamic_workload' ) ccn, ( SELECT query_id, SUM(CASE WHEN tlevel > 0 AND node_name LIKE '%dn%' THEN 1 ELSE 0 END) AS stream_cnt, SUM(CASE WHEN tlevel = 0 AND node_name LIKE '%dn%' THEN 1 ELSE 0 END) AS dn_cnt, SUM(CASE WHEN node_name LIKE '%cn%' then 1 ELSE 0 END) AS cn_cnt FROM pg_catalog.pgxc_thread_wait_status GROUP BY 1 ) thread, ( SELECT (CASE is_installation WHEN true THEN 'installation'::name ELSE group_name::name END) AS group_name, array_length(group_members,1) AS dn_cnt FROM pg_catalog.pgxc_group ) ng WHERE s.threadid = a.pid(+) AND a.query_id = thread.query_id(+) AND a.query_id = res.queryid(+) AND s.node_group = ng.group_name AND s.resource_pool != 'root' AND s.status NOT IN ('finished','aborted','active','unknown') AND a.state = 'active' AND a.query NOT LIKE '%pgxc_session_wlmstat%' AND a.query NOT LIKE '%pgxc_stat_activity%' ORDER BY used_cpu desc nulls last , s.status,s.priority DESC,a.query_start;

SELECT u.usename, u.nodegroup, u.respool, r.short_acc, coalesce(ss.session_cnt, 0) AS session_cnt, coalesce(ss.slow_run, 0) AS slow_run, coalesce(ss.slow_wait, 0) AS slow_wait, (CASE WHEN r.active_statements>0 THEN r.active_statements*ccn.cnt ELSE r.active_statements END) AS slow_limit, coalesce(ss.fast_run, 0) AS fast_run, coalesce(ss.fast_wait, 0) AS fast_wait, (CASE WHEN r.max_dop>0 THEN r.max_dop*cn.cnt ELSE r.max_dop END) AS fast_limit, DECODE(coalesce(ur.total_cpu, c.total_cpu), 0, c.total_cpu, coalesce(ur.total_cpu, c.total_cpu))/(c.total_cpu)*100 AS cpu_percent, ur.used_cpu/(g.node_cnt/dn.cnt) AS used_cpu, DECODE(coalesce(ur.total_cpu, c.total_cpu), 0, c.total_cpu, coalesce(ur.total_cpu, c.total_cpu))/(g.node_cnt/dn.cnt) total_cpu, ( CASE WHEN r.mem_percent = 0 THEN 100 WHEN r.mem_percent*ccn.cnt > 100 THEN 100 ELSE r.mem_percent*ccn.cnt END ) AS mem_percent, coalesce(ss.est_mem, 0) AS est_mem_mb, ur.used_memory/g.node_cnt used_mem_mb, ( CASE WHEN r.mem_percent=0 OR r.mem_percent=100 THEN concat(ROUND(ur.total_memory/g.node_cnt/1024, 2), 'GB') WHEN DECODE(r.mem_percent, 0, 100, r.mem_percent)*ccn.cnt > 100 THEN concat(ROUND(ur.total_memory/g.node_cnt/mem_percent*100/1024, 2), 'GB') ELSE concat(ROUND(ur.total_memory/g.node_cnt*ccn.cnt/1024, 2), 'GB') END ) AS total_mem, ( CASE WHEN r.memory_limit = 'default' THEN CASE WHEN DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory) is NULL THEN concat(0, 'GB') WHEN DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory) = 0 THEN concat(0, 'GB') WHEN DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory) > 1024 THEN concat(ROUND(DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory)/g.node_cnt*4/10/1024, 2), 'GB') ELSE concat((DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory)/g.node_cnt*4/10)::INT, 'MB') END ELSE r.memory_limit END ) AS query_mem_limit, ur.read_speed/1024 read_speed_mbs, ur.write_speed/1024 write_speed_mbs, ur.used_space/1024/1024 AS used_space, (CASE WHEN ur.total_space < 0 THEN -1 ELSE ur.total_space/1024/1024 END) AS total_space, ur.used_temp_space/1024/1024 AS used_temp_space, (CASE WHEN ur.total_temp_space < 0 THEN -1 ELSE ur.total_temp_space/1024/1024 END) AS total_temp_space, ur.used_spill_space/1024/1024 AS used_spill_space, (CASE WHEN ur.total_spill_space < 0 THEN -1 ELSE ur.total_spill_space/1024/1024 END) AS total_spill_space FROM pg_user u, pg_total_user_resource_info ur, ( SELECT rr.respool_name,rr.memory_limit,rr.short_acc,rr.active_statements,rr.max_dop, ( CASE WHEN rr.parentid = 0 THEN rr.mem_percent ELSE (SELECT mem_percent FROM pg_resource_pool WHERE oid=rr.parentid)*rr.mem_percent/100 END ) mem_percent FROM pg_resource_pool rr ) r, ( SELECT DISTINCT coalesce(au.nodegroup, 'installation') AS nodegroup, AVG(CASE WHEN total_cpu = 0 THEN -1 ELSE total_cpu END) AS total_cpu, AVG(CASE WHEN total_memory = 0 THEN -1 ELSE total_memory END) AS total_mem FROM pg_total_user_resource_info rr, pg_user au WHERE rr.username = au.usename AND au.respool = 'default_pool' AND au.usesysid!=10 GROUP BY 1 ) c, ( SELECT count(*) node_cnt, group_name FROM (SELECT (CASE is_installation WHEN true THEN 'installation' ELSE group_name END), unnest(group_members) FROM pgxc_group) GROUP BY 2 ) g, ( SELECT usename, count(1) AS session_cnt, SUM(CASE WHEN lane= 'fast' AND status = 'running' THEN 1 ELSE 0 END) AS fast_run, SUM(CASE WHEN lane= 'fast' AND enqueue != 'None' AND status = 'pending' THEN 1 ELSE 0 END) AS fast_wait, SUM(CASE WHEN lane= 'slow' AND status = 'running' THEN 1 ELSE 0 END) AS slow_run, SUM(CASE WHEN lane= 'slow' AND enqueue != 'None' AND status = 'pending' THEN 1 ELSE 0 END) AS slow_wait, SUM(CASE status WHEN 'running' THEN statement_mem ELSE 0 END) AS est_mem FROM pgxc_session_wlmstat GROUP BY 1 ) ss, ( SELECT node_host,count(1) as cnt FROM pgxc_node WHERE node_type = 'D' GROUP BY 1 limit 1 ) dn, ( SELECT ( CASE WHEN setting='on' THEN 1 ELSE (SELECT COUNT(1) FROM pgxc_node WHERE node_type = 'C') END ) AS cnt FROM pg_settings WHERE name='enable_dynamic_workload' ) ccn, ( SELECT COUNT(1) cnt FROM pgxc_node WHERE node_type = 'C' ) cn WHERE u.respool = r.respool_name(+) and coalesce(u.nodegroup, 'installation') = c.nodegroup(+) and coalesce(u.nodegroup, 'installation') = g.group_name(+) and u.usename = ur.username(+) and u.usename = ss.usename(+) and u.usesysid != 10 order by used_cpu desc;

alter user usename account lock;

execute direct on(node_name) 'select pg_cancel_backend(pid)';

SELECT usename ,coorname ,enqueue ,count(*) FROM pgxc_stat_activity WHERE STATE = 'active' AND usename <> 'omm' AND usename <> 'Ruby' GROUP BY 1,2,3 ORDER BY 4 desc;

alter user usename account lock;

select 'execute direct on(' || node_name || ') ''select pg_cancel_backend(' || tid || ')'';' , node_name,tid,query_id from pgxc_thread_wait_status b where not exists (select query_id from  pgxc_thread_wait_status a where a.node_name like 'c%' and a.query_id=b.query_id) and b.node_name like 'd%' and b.wait_status not in ('wait cmd') and b.tid <> pg_backend_pid();

SELECT s.usename,s.nodename,s.node_group::name AS nodegroup,s.resource_pool rpname, CAST(ROUND(res.total_cpu_time/ng.dn_cnt/(res.duration+1),3) AS DOUBLE PRECISION) AS used_cpu, s.elapsed_time AS duration_s, substr(a.query,1,100), ( CASE WHEN s.priority = 8 THEN 'Rush' WHEN s.priority = 4 THEN 'High' WHEN s.priority = 2 THEN 'Medium' WHEN s.priority = 1 THEN 'Low' ELSE 'Invalid' END )::name AS priority, a.xact_start,a.query_start,s.block_time, res.query_band,s.attribute,s.lane, ( CASE WHEN s.enqueue != 'None' AND s.status = 'pending' THEN 'pending' ELSE 'running' END ) AS status, ( CASE WHEN s.enqueue = 'None' OR s.status != 'pending' THEN 'None' WHEN s.enqueue='Global' THEN 'Global' WHEN s.enqueue = 'CentralQueue' THEN 'CCN' WHEN s.enqueue = 'Respool' THEN 'Respool' WHEN ccn.on AND s.lane = 'slow' THEN 'CCN' ELSE 'Respool' END ) AS queue, res.max_peak_memory AS used_mem,s.statement_mem AS estimate_mem, COALESCE(thread.dn_cnt, 0) AS dn_count,COALESCE(thread.stream_cnt, 0) AS stream_count, s.threadid AS pid,s.processid AS lwtid,a.query_id, a.query FROM pg_catalog.pgxc_session_wlmstat s, pg_catalog.pgxc_stat_activity a, ( SELECT MAX(query_band) AS query_band,MAX(max_peak_memory) AS max_peak_memory,MAX(total_cpu_time) AS total_cpu_time, MAX(duration) AS duration,queryid FROM pg_catalog.pgxc_wlm_session_statistics GROUP BY queryid ) res, ( SELECT (setting='on') AS on FROM pg_catalog.pg_settings WHERE name='enable_dynamic_workload' ) ccn, ( SELECT query_id, SUM(CASE WHEN tlevel > 0 AND node_name LIKE '%dn%' THEN 1 ELSE 0 END) AS stream_cnt, SUM(CASE WHEN tlevel = 0 AND node_name LIKE '%dn%' THEN 1 ELSE 0 END) AS dn_cnt, SUM(CASE WHEN node_name LIKE '%cn%' then 1 ELSE 0 END) AS cn_cnt FROM pg_catalog.pgxc_thread_wait_status GROUP BY 1 ) thread, ( SELECT (CASE is_installation WHEN true THEN 'installation'::name ELSE group_name::name END) AS group_name, array_length(group_members,1) AS dn_cnt FROM pg_catalog.pgxc_group ) ng WHERE s.threadid = a.pid(+) AND a.query_id = thread.query_id(+) AND a.query_id = res.queryid(+) AND s.node_group = ng.group_name AND s.resource_pool != 'root' AND s.status NOT IN ('finished','aborted','active','unknown') AND a.state = 'active' AND a.query NOT LIKE '%pgxc_session_wlmstat%' AND a.query NOT LIKE '%pgxc_stat_activity%' ORDER BY used_cpu desc nulls last , s.status,s.priority DESC,a.query_start;

SELECT u.usename, u.nodegroup, u.respool, r.short_acc, coalesce(ss.session_cnt, 0) AS session_cnt, coalesce(ss.slow_run, 0) AS slow_run, coalesce(ss.slow_wait, 0) AS slow_wait, (CASE WHEN r.active_statements>0 THEN r.active_statements*ccn.cnt ELSE r.active_statements END) AS slow_limit, coalesce(ss.fast_run, 0) AS fast_run, coalesce(ss.fast_wait, 0) AS fast_wait, (CASE WHEN r.max_dop>0 THEN r.max_dop*cn.cnt ELSE r.max_dop END) AS fast_limit, DECODE(coalesce(ur.total_cpu, c.total_cpu), 0, c.total_cpu, coalesce(ur.total_cpu, c.total_cpu))/(c.total_cpu)*100 AS cpu_percent, ur.used_cpu/(g.node_cnt/dn.cnt) AS used_cpu, DECODE(coalesce(ur.total_cpu, c.total_cpu), 0, c.total_cpu, coalesce(ur.total_cpu, c.total_cpu))/(g.node_cnt/dn.cnt) total_cpu, ( CASE WHEN r.mem_percent = 0 THEN 100 WHEN r.mem_percent*ccn.cnt > 100 THEN 100 ELSE r.mem_percent*ccn.cnt END ) AS mem_percent, coalesce(ss.est_mem, 0) AS est_mem_mb, ur.used_memory/g.node_cnt used_mem_mb, ( CASE WHEN r.mem_percent=0 OR r.mem_percent=100 THEN concat(ROUND(ur.total_memory/g.node_cnt/1024, 2), 'GB') WHEN DECODE(r.mem_percent, 0, 100, r.mem_percent)*ccn.cnt > 100 THEN concat(ROUND(ur.total_memory/g.node_cnt/mem_percent*100/1024, 2), 'GB') ELSE concat(ROUND(ur.total_memory/g.node_cnt*ccn.cnt/1024, 2), 'GB') END ) AS total_mem, ( CASE WHEN r.memory_limit = 'default' THEN CASE WHEN DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory) is NULL THEN concat(0, 'GB') WHEN DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory) = 0 THEN concat(0, 'GB') WHEN DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory) > 1024 THEN concat(ROUND(DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory)/g.node_cnt*4/10/1024, 2), 'GB') ELSE concat((DECODE(ur.total_memory, 0, c.total_mem, ur.total_memory)/g.node_cnt*4/10)::INT, 'MB') END ELSE r.memory_limit END ) AS query_mem_limit, ur.read_speed/1024 read_speed_mbs, ur.write_speed/1024 write_speed_mbs, ur.used_space/1024/1024 AS used_space, (CASE WHEN ur.total_space < 0 THEN -1 ELSE ur.total_space/1024/1024 END) AS total_space, ur.used_temp_space/1024/1024 AS used_temp_space, (CASE WHEN ur.total_temp_space < 0 THEN -1 ELSE ur.total_temp_space/1024/1024 END) AS total_temp_space, ur.used_spill_space/1024/1024 AS used_spill_space, (CASE WHEN ur.total_spill_space < 0 THEN -1 ELSE ur.total_spill_space/1024/1024 END) AS total_spill_space FROM pg_user u, pg_total_user_resource_info ur, ( SELECT rr.respool_name,rr.memory_limit,rr.short_acc,rr.active_statements,rr.max_dop, ( CASE WHEN rr.parentid = 0 THEN rr.mem_percent ELSE (SELECT mem_percent FROM pg_resource_pool WHERE oid=rr.parentid)*rr.mem_percent/100 END ) mem_percent FROM pg_resource_pool rr ) r, ( SELECT DISTINCT coalesce(au.nodegroup, 'installation') AS nodegroup, AVG(CASE WHEN total_cpu = 0 THEN -1 ELSE total_cpu END) AS total_cpu, AVG(CASE WHEN total_memory = 0 THEN -1 ELSE total_memory END) AS total_mem FROM pg_total_user_resource_info rr, pg_user au WHERE rr.username = au.usename AND au.respool = 'default_pool' AND au.usesysid!=10 GROUP BY 1 ) c, ( SELECT count(*) node_cnt, group_name FROM (SELECT (CASE is_installation WHEN true THEN 'installation' ELSE group_name END), unnest(group_members) FROM pgxc_group) GROUP BY 2 ) g, ( SELECT usename, count(1) AS session_cnt, SUM(CASE WHEN lane= 'fast' AND status = 'running' THEN 1 ELSE 0 END) AS fast_run, SUM(CASE WHEN lane= 'fast' AND enqueue != 'None' AND status = 'pending' THEN 1 ELSE 0 END) AS fast_wait, SUM(CASE WHEN lane= 'slow' AND status = 'running' THEN 1 ELSE 0 END) AS slow_run, SUM(CASE WHEN lane= 'slow' AND enqueue != 'None' AND status = 'pending' THEN 1 ELSE 0 END) AS slow_wait, SUM(CASE status WHEN 'running' THEN statement_mem ELSE 0 END) AS est_mem FROM pgxc_session_wlmstat GROUP BY 1 ) ss, ( SELECT node_host,count(1) as cnt FROM pgxc_node WHERE node_type = 'D' GROUP BY 1 limit 1 ) dn, ( SELECT ( CASE WHEN setting='on' THEN 1 ELSE (SELECT COUNT(1) FROM pgxc_node WHERE node_type = 'C') END ) AS cnt FROM pg_settings WHERE name='enable_dynamic_workload' ) ccn, ( SELECT COUNT(1) cnt FROM pgxc_node WHERE node_type = 'C' ) cn WHERE u.respool = r.respool_name(+) and coalesce(u.nodegroup, 'installation') = c.nodegroup(+) and coalesce(u.nodegroup, 'installation') = g.group_name(+) and u.usename = ur.username(+) and u.usename = ss.usename(+) and u.usesysid != 10 order by used_cpu desc;

SELECT rp.rpname, rp.nodegroup, coalesce(ss.session_cnt, 0) AS session_cnt, xc.ref_count active_cnt, coalesce(ss.fast_run,0) AS fast_run,xc.fast_run AS in_fast_run, coalesce(ss.fast_wait,0) AS fast_wait,xc.fast_wait AS in_fast_wait, (CASE WHEN rp.fast_limit>0 THEN rp.fast_limit*cn.cnt ELSE rp.fast_limit END) AS fast_limit, coalesce(ss.slow_run,0) AS slow_run,xc.slow_run AS in_slow_run, coalesce(ss.slow_wait,0) AS slow_wait,xc.slow_wait AS in_slow_wait, (CASE WHEN rp.slow_limit>0 THEN rp.slow_limit*ccn.cnt ELSE rp.slow_limit END) AS slow_limit, rp.used_cpu/(node_cnt/n.cnt) used_cpu, rp.cpu_limit/(node_cnt/n.cnt) total_cpu, rp.used_mem/node_cnt used_mem_mb, coalesce(ss.est_mem,0) AS est_mem_mb, xc.est_mem AS in_est_mem_mb, ( CASE WHEN r.mem_percent = 0 THEN 100 WHEN r.mem_percent*ccn.cnt > 100 THEN 100 ELSE r.mem_percent*ccn.cnt END ) AS mem_percent, ( CASE WHEN r.mem_percent=0 OR r.mem_percent=100 THEN concat(ROUND(rp.mem_limit/g.node_cnt/1024, 2), 'GB') WHEN r.mem_percent*ccn.cnt > 100 THEN concat(ROUND(rp.mem_limit/g.node_cnt/r.mem_percent*100/1024, 2), 'GB') ELSE concat(ROUND(rp.mem_limit/g.node_cnt*ccn.cnt/1024, 2), 'GB') END ) AS total_mem, ( CASE WHEN r.memory_limit = 'default' THEN CASE WHEN rp.mem_limit is NULL THEN concat(0, 'GB') WHEN rp.mem_limit = 0 THEN concat(0, 'GB') WHEN rp.mem_limit > 1024 THEN concat(ROUND(rp.mem_limit/g.node_cnt*4/10/1024, 2), 'GB') ELSE concat((rp.mem_limit/g.node_cnt*4/10)::INT, 'MB') END ELSE r.memory_limit END ) AS query_mem_limit, rp.read_speed/1024 read_speed_mbs, rp.write_speed/1024 write_speed_mbs FROM ( SELECT rr.respool_name,rr.memory_limit, ( CASE WHEN rr.parentid = 0 THEN rr.mem_percent ELSE (SELECT mem_percent FROM pg_resource_pool WHERE oid=rr.parentid)*rr.mem_percent/100 END ) mem_percent FROM pg_resource_pool rr ) r, gs_respool_resource_info rp, ( SELECT rpname, nodegroup, SUM(ref_count) ref_count, SUM(fast_run) fast_run, SUM(fast_wait) fast_wait, SUM(slow_run) slow_run, SUM(slow_wait) slow_wait, SUM(estimate_mem) est_mem FROM pgxc_respool_resource_info WHERE nodename like '%cn%' GROUP BY 1,2 ) xc, ( SELECT resource_pool AS rpname, node_group, SUM(cnt) AS session_cnt, SUM(CASE WHEN lane= 'fast' and status = 'running' THEN cnt ELSE 0 END) AS fast_run, SUM(CASE WHEN lane= 'fast' and enqueue != 'None' and status = 'pending' THEN cnt ELSE 0 END) AS fast_wait, SUM(CASE WHEN lane= 'slow' and status = 'running' THEN cnt ELSE 0 END) AS slow_run, SUM(CASE WHEN lane= 'slow' and enqueue != 'None' and status = 'pending' THEN cnt ELSE 0 END) AS slow_wait, SUM(CASE status WHEN 'running' THEN est_mem ELSE 0 END) AS est_mem FROM (SELECT resource_pool, node_group, lane, enqueue, status, count(1) as cnt, SUM(statement_mem) as est_mem FROM pgxc_session_wlmstat GROUP BY 1,2,3,4,5) GROUP BY 1,2 ) ss, ( SELECT count(*) node_cnt, group_name FROM (SELECT group_name, unnest(group_members) FROM pgxc_group) GROUP BY 2 ) g, ( SELECT node_host,count(1) as cnt FROM pgxc_node WHERE node_type = 'D' GROUP BY 1 limit 1 ) n, ( SELECT ( CASE WHEN setting='on' THEN 1 ELSE (SELECT COUNT(1) FROM pgxc_node WHERE node_type = 'C') END ) AS cnt FROM pg_settings WHERE name='enable_dynamic_workload' ) ccn, ( SELECT COUNT(1) cnt FROM pgxc_node WHERE node_type = 'C' ) cn WHERE rp.rpname = ss.rpname(+) and rp.nodegroup = ss.node_group(+) and rp.rpname = r.respool_name(+) and rp.nodegroup = g.group_name(+) and rp.nodegroup = xc.nodegroup(+) and rp.rpname = xc.rpname(+) and INSTR(rp.cgroup,':') order by used_cpu desc;