GaussDB(DWS)应急预案（一）：CCN排队
1.1 集群出现大量wait in ccn queue（预计10min）
1.1.1 应急步骤
1. 部分操作需要连接CCN执行，建议下列命令直接登录CCN操作（沙箱内外均可）。查看资源池排队情况，将结果保存：
SELECT rp.rpname, rp.nodegroup, r.short_acc, coalesce(ss.session_cnt, 0) AS session_cnt, xc.ref_count active_cnt, coalesce(ss.fast_run,0) AS fast_run, coalesce(ss.fast_wait,0) AS 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, coalesce(ss.slow_wait,0) AS slow_wait, (CASE WHEN rp.slow_limit>0 THEN rp.slow_limit*ccn.cnt ELSE rp.slow_limit END) AS slow_limit, ( CASE WHEN rp.used_cpu>0 THEN ROUND(rp.used_cpu/(node_cnt/n.cnt),2) ELSE 0 END ) AS used_cpu, rp.cpu_limit/(node_cnt/n.cnt) total_cpu, rp.used_mem/node_cnt AS used_mem_mb, coalesce(ss.est_mem,0) AS est_mem_mb, ( CASE 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 ccn.cnt > 1 AND r.mem_percent=0 THEN concat(0,'') 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, ( CASE WHEN rp.read_speed>0 THEN ROUND(rp.read_speed/1024,2) ELSE 0 END ) AS read_speed_mbs, ( CASE WHEN rp.write_speed>0 THEN ROUND(rp.write_speed/1024,2) ELSE 0 END ) AS write_speed_mbs FROM ( SELECT rr.respool_name,rr.memory_limit,rr.short_acc, ( 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, 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,2 ) ss, ( 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 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,':');
若上述命令10s内无法执行出来，执行下面的语句：
SELECT s.resource_pool AS rpname, s.node_group, COUNT(1) AS session_cnt, SUM(CASE WHEN a.state = 'active' THEN 1 ELSE 0 END) AS active_cnt, SUM(CASE WHEN s.enqueue = 'Global' THEN 1 ELSE 0 END) AS global_wait, SUM(CASE WHEN s.lane = 'fast' AND s.status = 'running' THEN 1 ELSE 0 END) AS fast_run, SUM(CASE WHEN s.lane = 'fast' AND s.status = 'pending' AND s.enqueue NOT IN ('Global', 'None') THEN 1 ELSE 0 END) AS fast_wait, SUM(CASE WHEN s.lane = 'slow' AND s.status = 'running' THEN 1 ELSE 0 END) AS slow_run, SUM(CASE WHEN s.lane = 'slow' AND s.status = 'pending' AND s.enqueue NOT IN ('Global', 'None') THEN 1 ELSE 0 END) AS slow_wait, SUM(CASE WHEN s.status = 'running' THEN s.statement_mem ELSE 0 END) AS est_mem FROM pg_catalog.pgxc_session_wlmstat s, pg_catalog.pgxc_stat_activity a WHERE s.threadid = a.pid(+) AND s.attribute != 'Internal' AND s.resource_pool != 'root' GROUP BY 1, 2;
2. 连接CCN，执行如下命令，将结果保存（输出内容可能较多，可通过xshell日志功能保存）：
SELECT * FROM pg_stat_get_workload_struct_info();
3. 连接CCN，执行计数异常恢复方法：
select gs_wlm_node_recover(true);
4. 查杀当前内存占用高的语句，得到拼接的查杀命令：
SELECT 'execute direct on(' || s.nodename || ') ''select pg_cancel_backend(' || s.threadid || ')'';',s.usename,s.node_group,s.resource_pool respool, ( 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 ) AS priority, a.query_start,s.block_time,s.elapsed_time run_time, 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 'CN Respool' WHEN ccn.on AND s.lane='slow' THEN 'CCN' ELSE 'CN Respool' END ) AS queue,s.statement_mem est_mem,thread_info.stream_cnt,s.processid,a.query_id, substr(a.query,1,200) FROM pgxc_session_wlmstat s, pgxc_stat_activity a, ( SELECT (setting='on') AS on FROM 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 pg_cnt, SUM(CASE WHEN node_name like '%cn%' then 1 ELSE 0 END) AS cn_cnt FROM pgxc_thread_wait_status GROUP BY 1 ) thread_info WHERE s.threadid=a.pid AND a.query_id=thread_info.query_id 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 est_mem DESC, a.query_start;
5. 查杀当前执行时间长的语句，得到拼接的查杀命令:
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' and resource_pool = 'pool name' order by dur desc limit 100;
6. 若判断为计数异常，且通过gs_wlm_node_recover无法恢复，尝试应急kill ccn恢复
7. 若上述手段均无效果，查看并发最高的用户，锁定该用户
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;
1.1.2 恢复确认
重新执行资源池排队语句，查看各资源池排队状态，确认业务是否恢复。
1.1.3 原因分析
1. 集群出现排队告警或巡检/监控发现大量CCN排队时，首先通过如下语句确认各资源池的排队情况（下简称资源池排队语句）（内容较多，保存到.log文件执行）：
SELECT rp.rpname, rp.nodegroup, r.short_acc, coalesce(ss.session_cnt, 0) AS session_cnt, xc.ref_count active_cnt, coalesce(ss.fast_run,0) AS fast_run, coalesce(ss.fast_wait,0) AS 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, coalesce(ss.slow_wait,0) AS slow_wait, (CASE WHEN rp.slow_limit>0 THEN rp.slow_limit*ccn.cnt ELSE rp.slow_limit END) AS slow_limit, ( CASE WHEN rp.used_cpu>0 THEN ROUND(rp.used_cpu/(node_cnt/n.cnt),2) ELSE 0 END ) AS used_cpu, rp.cpu_limit/(node_cnt/n.cnt) total_cpu, rp.used_mem/node_cnt AS used_mem_mb, coalesce(ss.est_mem,0) AS est_mem_mb, ( CASE 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 ccn.cnt > 1 AND r.mem_percent=0 THEN concat(0,'') 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, ( CASE WHEN rp.read_speed>0 THEN ROUND(rp.read_speed/1024,2) ELSE 0 END ) AS read_speed_mbs, ( CASE WHEN rp.write_speed>0 THEN ROUND(rp.write_speed/1024,2) ELSE 0 END ) AS write_speed_mbs FROM ( SELECT rr.respool_name,rr.memory_limit,rr.short_acc, ( 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, 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,2 ) ss, ( 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 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,':');
其中各字段含义如下：需重点关注标红字段：
rpname：资源池名称；
nodegroup：资源池所属逻辑集群名称；
short_acc：是否开启短查询加速；关闭短查询加速，短查询与复杂查询都在慢车道管控；开启短查询加速，短查询与复杂查询分开管控，短查询在快车道管控；
session_cnt；资源池上关联的会话总数，包含ACTIVE、IDLE会话；
active_cnt：资源池关联的活跃会话总数；
fast_run：资源池快车道实际运行的查询数量，由pgxc_session_wlmstat中作业汇聚而成；
fast_wait：资源池快车道实际排队的查询数量，由pgxc_session_wlmstat中作业汇聚而成0；
fast_limit：资源池快车道并发限制，-1为不限制；
slow_run：资源池慢车道实际运行的查询数量，由pgxc_session_wlmstat中作业汇聚而成；
slow_wait：资源池慢车道实际排队的查询数量，由pgxc_session_wlmstat中作业汇聚而成；
slow_limit：资源池慢车道并发限制，-1为不限制；
used_cpu：资源池在所有DN上使用CPU的平均值；
total_cpu：资源池可用的CPU core总数；
used_mem_mb：资源池在所有DN上使用内存的平均值，单位：MB；
est_mem_mb：资源池当前实际正在运行的查询估算内存之和，由pgxc_session_wlmstat中作业汇聚而成，单位：MB；
mem_percent：资源池可用的内存百分比，小于100说明用户可用内存有单独限制；
total_mem：资源池可用内存上限，正在运行的查询估算内存之和不能超过该值，实际使用内存有可能超过该值，单位：GB
query_mem_limit：资源池单查询估算内存上限，用于防止单查询估算内存过大导致异常排队，单位：MB/GB，显示带单位；
read_speed_mbs：资源池在所有DN上的逻辑IO读速率之和，单位MB/s；
write_speed_mbs：资源池在所有DN上的逻辑IO写速率之和，单位MB/s；
出现排队情况通常有如下原因：
- 自定义资源池上fast_run或slow_run的个数超过快/慢车道的并发限制时会出现CCN排队；
- 自定义资源池上est_mem_mb超过该资源池分配的内存上限
- default_pool资源池上est_mem_mb超过总内存上限
- 位于ccn队首的语句估算内存超过了该语句所在资源池的剩余内存
- CCN计数异常（资源池上所有语句均在排队，无正在执行的语句）
注：低版本无pgxc_session_wlmstat视图，可以参考如下链接手动创建：
https://bbs.huaweicloud.com/blogs/386595
2. 上述结果中，根据资源池排队情况和资源池信息，可以确认排队原因： 
场景一：用户自定义资源池排队
- 若慢车道并发达到上限（slow_run >= slow_limit），说明当前排队为正常情况，若无业务影响，无需处理；否则，查杀当前慢语句，或适当调大慢车道并发：pool name替换成实际的资源池名：
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' and resource_pool = 'pool name' order by dur desc limit 100;
- 若慢车道内存达到上限（est_mem_mb接近total_mem），说明当前估算内存较高，需要找到当前正在运行的语句中估算内存高的语句，当排队影响业务时，可查杀估算内存高的语句恢复：
SELECT 'execute direct on(' || s.nodename || ') ''select pg_cancel_backend(' || s.threadid || ')'';',s.usename,s.node_group,s.resource_pool respool, ( 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 ) AS priority, a.query_start,s.block_time,s.elapsed_time run_time, 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 'CN Respool' WHEN ccn.on AND s.lane='slow' THEN 'CCN' ELSE 'CN Respool' END ) AS queue,s.statement_mem est_mem,thread_info.stream_cnt,s.processid,a.query_id, substr(a.query,1,200) FROM pgxc_session_wlmstat s, pgxc_stat_activity a, ( SELECT (setting='on') AS on FROM 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 pg_cnt, SUM(CASE WHEN node_name like '%cn%' then 1 ELSE 0 END) AS cn_cnt FROM pgxc_thread_wait_status GROUP BY 1 ) thread_info WHERE s.threadid=a.pid AND a.query_id=thread_info.query_id 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%' and respool = 'pool name' ORDER BY est_mem DESC, a.query_start;
执行结果如下图所示，与业务确认后，查杀内存估算高的语句即可，查杀后重新查看各资源池的排队情况，确认是否恢复。
- 还可以根据实时topsql查看实际最大内存使用和估算内存大小：
SELECT 'execute direct on(' || nodename || ') ''select pg_cancel_backend(' || pid || ')'';', username, start_time, block_time, duration, estimate_memory, max_peak_memory, substr(query,1,100) from pgxc_wlm_session_statistics order by estimate_memory desc;
- 若快车道并发达到上限（fast_run >= fast_limits），说明当前排队为正常情况，需要检查当前正在运行的慢语句是否存在性能瓶颈，或适当调大快车道并发，查看方法同1.(1)。若并发异常，可锁定用户快速恢复：
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;
场景二：默认资源池default_pool排队
default_pool出现排队，说明估算内存达到总内存上限（est_mem接近total_mem），可查杀估算内存高的语句恢复。
- 位于CCN队首的语句内存估算高导致剩余语句排队：该场景也需要找到当前内存估算高的语句，方法同上
- 若并发和内存均未超过对应资源池的上限，说明当前CCN计数存在异常，连接CCN执行如下语句，保存执行结果到日志文件中（日志内容可能较多）
SELECT * FROM pg_stat_get_workload_struct_info();
- CCN计数不准时，连接CCN，执行如下语句应急恢复：
select gs_wlm_node_recover(true);
计数不准的表现如下，下图中，upsert资源池，slow_run为0，slow_wait很多，说明该资源池没有语句执行，全部在排队，说明存在计数异常：
若执行上述命令后，CCN排队数（slow_wait/fast_wait）没有下降，说明计数异常未恢复，需要kill ccn应急（期间部分业务闪断）。
若是由于并发高导致的排队（通常出现在短查询并发突增，导致短查询大量排队），可以通过如下语句找到异常并发的用户，应急锁定该用户：
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;
1.1.4 预防再次出现
- 通过资源池排队语句确认当前资源池上的并发限制和内存分配是否合理，在CPU和内存资源充足的情况下，可适当调大快慢车道的并发限制
- 对于估算内存高的场景，若实际使用内存与估算内存都高，需要业务优化该语句；
- 如果实际使用内存低，估算内存高，可以通过如下方式优化：
- 作业级设置query_mem，示例：限制单个作业内存估算不超过1G：set query_mem = '1GB';
- 设置资源池mem_limit，示例：资源池上的最大估算不超过1G：Alter resource pool xxx with (memory_limit = '1GB');
- 点赞
- 收藏
- 关注作者
评论（0）