DWS SQL慢问题排查手段总结

以下场景未做总结，排查SQL慢问题的时候，请注意分辨：

• SQL偶发慢问题定位需要部署监控、探针，对于没有现场的情况，不在本次总结范围

• BUG场景，一般比较极端，不在本次总结范围

• 适用813版本，其他版本不作保证

1、单SQL慢

先通过 pgxc_lock_conflicts 视图判断SQL是否在等锁，如果不是等锁，继续往下看↓↓↓

1.1 查询慢

总体调优策略 https://bbs.huaweicloud.com/blogs/200906

味道SQL识别 https://bbs.huaweicloud.com/blogs/197413

好味道表定义 https://bbs.huaweicloud.com/blogs/203219

SQL改写 https://bbs.huaweicloud.com/blogs/203420

路径干预 https://bbs.huaweicloud.com/blogs/212459

Plan hint运用 https://bbs.huaweicloud.com/blogs/213195

单点性能案例集锦

https://bbs.huaweicloud.com/forum/thread-90331-1-1.html

1.2 入库慢（insert/upsert/merge into/copy）

1.2.1 原表统计信息不准

如果统计信息没收集，建议先做analyze再重新入库

-- 查看当前查询的计划
select queryid,query,warning,query_plan from pgxc_wlm_session_statistics where queryid=xx;

1.2.2 执行计划不是最优

比如merge into ... using ...走了nestloop join计划，通过hint或者设置会话级别参数强制走hash join计划，效率可能会提升很多

1.2.3 目标表或者目标表索引膨胀

业务如果经常update/delete目标表，会导致目标表脏页变多，表和索引膨胀，进而导致insert效率下降，可以定期做下vacuum full

1.2.4 索引多

索引多会影响写入性能，建议等入库完后再加索引

-- 查看表定义
select pg_get_tabledef('tablename');

1.2.5 insert/upsert batchsize较小

• 一个事务中多个单value insert比一个insert多个values的SQL性能差，且对于列存表，一个事务中多个单value insert会导致小CU

• insert/upsert values数量较少，会影响入库效率，且对于列存表，会导致小CU

• 建议优先使用merge into/copy的方式入库

1.2.6 raid卡写策略是Write Through

Write-through（直写模式）在数据更新时，同时写入缓存Cache和后端存储。此模式的优点是操作简单；缺点是因为数据修改需要同时写入存储，数据写入速度较慢。

Write-back（回写模式）在数据更新时只写入缓存Cache。只在数据被替换出缓存时，被修改的缓存数据才会被写到后端存储。此模式的优点是数据写入速度快，因为不需要写存储；缺点是一旦更新后的数据未被写入存储时出现系统掉电的情况，数据将无法找回。

建议使用Write-back

1.3 analyze 慢

1.3.1 行存表

• 检查字段个数，字段多会影响analyze执行速度

• 分区表需要检查分区个数，分区多会影响analyze执行速度

• 检查脏页率，脏页多会影响analyze执行速度

-- 查看表定义
select pg_get_tabledef('tablename');
​
-- 查看某个表的脏页率
select a.schemaname,a.relname,pg_size_pretty(pg_table_size(b.oid)),a.dirty_page_rate from pgxc_get_stat_dirty_tables(10,1000) a,pg_catalog.pg_class b where a.relname = b.relname and a.relname = 'tablename' order by pg_table_size(b.oid) desc;

脏页多会导致analyze慢，可以执行vacuum full，analyze速度会提升。如果未定位analyze慢的原因，也可以尝试做下vacuum full，观察analyze性能是否提升。

1.3.2 列存表

• 检查字段个数，字段多会影响analyze执行速度

• 分区表需要检查分区个数，分区多会影响analyze执行速度

• 检查小CU，小CU会影响analyze执行速度

-- 检查小CU
-- 查看列存表对应的cudesc表
/* 分区表时执行 */ SELECT 'cstore.'||relname FROM pg_class where oid in (SELECT p.relcudescrelid FROM pg_partition p,pg_class c,pg_namespace n where c.relnamespace = n.oid and p.parentid = c.oid and c.relname = 'tablename' and n.nspname = 'schemaname' and p.relcudescrelid != 0);
/* 非分区表时执行 */ SELECT 'cstore.'||relname FROM pg_class where oid = (SELECT relcudescrelid FROM pg_class c inner join pg_namespace n on c.relnamespace = n.oid where relname = 'tablename' and nspname = 'schemaname');
-- 查看cudesc中各CU的rowcount情况
select col_id,cu_id,row_count,size from cstore.pg_cudesc_3276934423;

小CU会导致analyze慢，可以执行vacuum full，analyze速度会提升。如果未定位analyze慢的原因，也可以尝试做下vacuum full，观察analyze性能是否提升。

1.4 DDL 慢

检查集群状态，如果CN/DN重启或者CN状态异常，DDL会重试，执行时间会变长

2、多SQL慢

2.1 SQL排队

通过全局活跃会话视图，查看活跃会话中enqueue状态

select coorname, usename, client_addr, now()-query_start as dur, state, enqueue, waiting, pid, query_id, substr(replace(query, chr(10), ' '),1,150) from pgxc_stat_activity where usename not in ('omm', 'Ruby') and state = 'active' order by dur desc;

查询资源池监控视图（适用813及以上版本）

/*
rpname：资源池名称          
nodegroup：资源池所属逻辑集群名称
session_cnt；资源池上关联的会话总数，包含ACTIVE、IDLE会话
active_cnt：资源池关联的活跃会话总数
global_wait：全局排队的查询数量
fast_run：资源池快车道实际运行的查询数量
fast_wait：资源池快车道实际排队的查询数量
slow_run：资源池慢车道实际运行的查询数量
slow_wait：资源池慢车道实际排队的查询数量
est_mem：资源池当前实际正在运行的查询估算内存之和
*/
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.1.1 wait global

触发全局max_active_statements阈值，根据资源使用情况，评估调大max_active_statements或者降低业务并发

2.1.2 wait respool

开启短查询加速场景

• slow_wait > 0，说明触发资源池max_active_statements限额，根据资源使用情况，评估是否调大资源池max_active_statements限额

• fast_wait > 0，说明触发资源池max_dop限额，根据资源使用情况，评估是否调大资源池max_dop限额

• slow_wait 和 fast_wait 都为0，说明触发资源池内存限额，根据资源使用情况，评估是否调大资源池内存限额

2.1.3 wait ccn

触发全局内存max_dynamic_memory限制，评估内存参数设置是否合理，以及资源池管控是否合理

2.2 硬件资源瓶颈

通过前台监控或者后台工具检查CPU/IO/内存/带宽是否存在瓶颈

2.2.1 多节点资源瓶颈

• 多节点CPU瓶颈，根据业务情况，评估是否降低并发或者升配、扩容，抑或优化SQL等

• 多节点IO瓶颈，根据业务情况，评估是否降低并发或者更换SSD盘、升配、扩容，抑或优化SQL等

2.2.2 单节点资源瓶颈

• 排查存储倾斜：https://bbs.huaweicloud.com/blogs/183585

• 排查计算倾斜：https://bbs.huaweicloud.com/blogs/254845

2.3 OS或硬件故障

• 查看等待视图，若发现wait_status都是wait node同一个节点，排除掉存储倾斜和计算倾斜问题，可能是OS或者硬件出现问题，需要检查OS和硬件相关日志

• 单节点存在CPU/IO瓶颈，排除掉存储倾斜和计算倾斜问题，可能是OS或者硬件出现问题，需要检查OS和硬件相关日志

2.4 网络故障

• 若非log_hostname = on导致的连接慢问题，可能存在网络问题，使用ping/gsar排查是否存在重传、丢包等网络异常

• SQL执行计划没有变化、且表数据量没有变化，但stream算子耗时比之前高，可能存在网络问题，使用ping/gsar排查是否存在重传、丢包等网络异常

• 等待视图中wait pooler状态持续时间较长，集群内部可能存在网络问题，使用ping/gsar排查是否存在重传、丢包等网络异常

• 以上排查手段均未发现问题，可能存在网络问题，使用ping/gsar排查是否存在重传、丢包等网络