DWS慢作业排查全攻略：从执行计划到数据倾斜的优化实战

一个数据仓库跑批作业慢下来最折磨人的不是“慢”本身而是你不知道它到底卡在哪一环是某个节点CPU被打满还是某个SQL执行计划走了歪路又或者干脆是资源池排队、锁等待把作业堵在门外。做DWSGaussDB运维这几年我排查过不少执行作业慢的问题今天就把这套排查思路和优化手段完整梳理一遍。无论你是刚接触GaussDB的新手还是已经被慢作业折磨过几轮的老手这篇文章都更适合照着实操而不是当概念看看就过。先说清楚背景。DWS全称Data Warehouse Service它的内核底座就是GaussDB基于开放生态的分布式数据库整体架构是典型的多节点MPP协调节点CN负责接收SQL、生成计划、汇总结果数据节点DN负责实际存储和计算。慢作业的根因往往不是单一SQL写得好不好而是“分布式环境下的资源配置、数据分布、执行计划、并发冲突”这四个层面交叉作用的结果。所以排查慢作业我的习惯是先用一个三层法给问题定性再逐个层面深入而不是一上来就抓某条SQL看执行计划。1. 先分清作业慢的类型是资源不够还是计划太差1.1 三类慢作业的典型特征我把在大规模数据仓库里遇到的慢作业分成三类每一类的表现、定位方法、解决路径都不一样资源型慢作业作业本身不复杂但运行时把某个节点的CPU、内存、磁盘IO打满导致整体吞吐下降。典型表现是作业长时间停留在某个状态系统监控里能看到某个DN节点的CPU接近100%或者内存使用率持续高企。计划型慢作业SQL逻辑没问题但优化器生成的执行计划低效比如对超大表做了全表扫描、join顺序选择失误、估算行数严重失真。典型表现是EXPLAIN ANALYZE里能看到某些算子的实际执行时间远高于预期或者出现了意想不到的Nested Loop 和大量循环。等待型慢作业作业本身不耗资源但一直在等。等什么等锁释放、等资源池里的内存配额、等DN之间的通信返回。典型表现是会话状态长期处于“wait”CPU和IO都不忙作业就是不动。这个分类看起来简单但非常管用。我见过很多人在第一步就跑偏明明资源池排队都堵成一串了还在那里一条条优化SQL或者压根不看系统视图一上来就按通用建议加索引、改SQL结果越改越慢。1.2 用“三层法”给慢作业定位所谓三层法就是从外到内逐层定位每层都用最少的命令来判断不用一开始就把问题放大到整个集群第一层看集群资源水位。确认是不是某个或某些节点资源打满如果是优先处理资源问题。用系统监控工具看一眼CPU、内存、磁盘IO和网络如果整体空闲直接跳到第二层。第二层看作业在等什么。如果资源空闲但作业不跑赶紧查等待事件和锁信息确认作业是不是被堵住了。第三层看执行计划是否合理。资源不忙、也没有等待那就把目标SQL抓出来做执行计划分析。这套流程避免了“眉毛胡子一把抓”也能快速形成一个初步结论方便后续专项排查。下面每一层我都会给出具体的操作方法和可用命令。2. 用系统视图和工具锁定瓶颈2.1 pg_stat_activity / PGXC_STAT_ACTIVITY 看会话状态DWSGaussDB兼容PostgreSQL生态所以排查会话状态的第一入口就是pg_stat_activity视图。但注意在多节点环境下我更推荐直接看PGXC_STAT_ACTIVITY它能把CN和所有DN上的线程状态汇总展示比一个个节点去查方便得多。核心排查SQL如下SELECT coorname, datname, usename, pid, waiting, query_start, state, wait_status, query FROM pgxc_stat_activity WHERE state idle ORDER BY query_start;里面几个字段要重点看waiting表示会话是否处于等待锁的状态如果长时间为true基本能确认锁阻塞。wait_status这是DWS特有字段显示当前线程在等待什么。常见的等待状态有wait lock、wait io、wait cpu、wait memory、wait stream等。query_startSQL开始执行的时间据此估算已经跑了多久。stateactive表示执行中idle in transaction表示事务里挂着没提交这类会话也容易堵住别人。我通常会再按节点分组看定位作业到底在哪个DN上卡住了SELECT coorname, count(*), max(query_start) FROM pgxc_stat_activity WHERE state idle GROUP BY coorname ORDER BY count(*) DESC;如果某个DN上的活跃会话数量特别多或者某个SQL只在这个DN上长时间不结束多半和数据倾斜有关后面我会专门讲。2.2 GS_WLM_SESSION_STATISTICS 看作业资源消耗GS_WLM_SESSION_STATISTICS 是DWS负载管理WLM相关的重要视图记录作业的执行统计信息。它相当于作业的“体检报告”能看到一个作业实际消耗了多少CPU时间、内存、读写IO以及排队时间。常用查询SELECT usename, application_name, query, total_duration / 1000 AS duration_sec, queue_duration / 1000 AS queue_sec, execution_duration / 1000 AS exec_sec, cpu_time / 1000 AS cpu_sec, max_peak_memory, read_kbytes, write_kbytes FROM gs_wlm_session_statistics WHERE query NOT LIKE %gs_wlm_% ORDER BY total_duration DESC LIMIT 20;看到这张表你就能判断作业慢的“慢”到底发生在哪个阶段如果queue_duration占了大头说明作业一直都在资源池排队那就该去调资源池并发和资源配置如果cpu_time非常大说明SQL确实在大量消耗CPU做计算如果read_kbytes/write_kbytes高得离谱说明IO读写量异常大概率是全表扫描或者中间结果落盘严重。需要注意gs_wlm_session_statistics保留的是历史窗口内的作业记录如果作业实在执行太久没结束可能还没生成完整统计此时更适合用2.1的实时会话视图来观察。2.3 查看节点和等待事件PGXC_THREAD_WAIT_STATUS当作业呈现“僵死”状态时直接查看线程等待状态的视图非常关键SELECT * FROM pgxc_thread_wait_status;它会返回类似wait_status、locktype、lockmode、waiting_pid、waiting_session_id等信息。这里要解释一下常见的几种等待事件wait lock在等待表级/行级锁后面跟的locktype会告诉你持锁对象类型比如 relation、transactionid 等。关系很大。wait io在等磁盘IO常见于大批量数据落盘、列存表merge、以及临时文件排序。wait stream在等DNs之间的stream数据通信常见问题是某个DN计算太慢导致整体等待。wait memory在等资源池分配内存说明当前并发作业的内存请求超出了资源池上限。线程等待视图能看到的是“某个连接上的某个线程在等什么”但如果同时有大量会话堆积等待你最该关注的是锁等待。锁问题单独拿来说都不为过。2.4 WDR报告和自诊断不想手动一条条SQL查的话DWS提供了WDRWorkload Diagnosis Report类似PostgreSQL社区里的pg_profile思路。一条命令就能生成一份全集群的负载诊断报告里面包含SQL运行统计Top N节点资源使用趋势IO/网络/等待事件分布锁等待和死锁情况生成WDR报告大致步骤是-- 创建快照 SELECT create_wdr_snapshot(); -- 生成报告 SELECT generate_wdr_report(开始快照ID, 结束快照ID, all, node_name, 报告文件路径);实际使用时建议在慢作业的“慢”时间段内连续创建两个快照再生成对比报告。这样能看到这段时间内集群到底发生了什么变化而不是只看到当前一个静态截图。3. SQL执行计划分析慢查询优化的主战场3.1 先做 EXPLAIN ANALYZE别只看EXPLAIN很多刚接触DWS的人容易犯一个错误用EXPLAIN看输出计划觉得“哎有索引、没全表扫描挺正常”但真实跑起来还是很慢。原因在于EXPLAIN只输出预估计划EXPLAIN ANALYZE才真正执行SQL并输出每个算子的实际行数、实际耗时、内存占用。正确做法是EXPLAIN ANALYZE SELECT ...;对于慢作业我更建议加上VERBOSE和COSTSEXPLAIN (ANALYZE, VERBOSE, COSTS) SELECT ...;重点看四样东西Actual Rows / Rows Removed by Filter实际输出行数与过滤行数如果实际行数和估算行数差距超过数量级说明统计信息有问题。Actual Time 的 first_row 和 last_rowlast_row是算子总耗时first_row是输出第一行的时间。如果first_row很大说明这个算子在“启动”阶段就花了很长时间常见于sort、hash join建hash表。内存使用峰值Memory Usage如果大到触发落盘执行速度会断崖式下跌。Stream节点分布式计划里会看到Streaming (type: REDISTRIBUTE / BROADCAST / GATHER)这些节点代表数据在DN间流动。如果某个Redistribute动作处理的行数特别大就说明发生了大量跨节点数据搬移。3.2 计划里的关键风险点在实际业务里我总结出了几个在执行计划里看到就“心里咯噔一下”的风险点Nested Loop 嵌套循环如果内层循环的驱动表估算行数不准确实际可能执行几十亿次这是最典型的性能杀手。除非inner侧能走索引、且数据量很小否则一般建议改写为hash join。Hash Join 的 Hash Side 选择通常应该用小表作为hash侧大表作为probe侧。如果计划里发现hash侧是大表检查一下统计信息和join列的分布。Seq Scan 全表扫描在OLAP场景里全表扫描不一定是坏事但如果一个查询只取很小一部分数据却走了全表扫描就值得怀疑统计信息和索引缺失。内存落盘排序或聚合时内存不够会临时写到磁盘。计划输出中如果有Sort Method: external merge Disk或者Spill字样基本就是内存参数设置不合理。单DN执行如果计划里大量算子之后才出现Stream甚至整个计划只有一个DN在干活也要怀疑数据倾斜。3.3 常见低效计划形态与改写方向以一条我实际优化过的SQL为例。原SQL长这样SELECT a.user_id, sum(b.amount) FROM dim_user a LEFT JOIN fact_order b ON a.user_id b.user_id WHERE b.order_date 2024-01-01 GROUP BY a.user_id;执行计划里出现了Nested Loop Left Join (cost...)而且fact_order表因为过滤条件不准确被全表扫描了。实际跑了一个小时都没结束。优化过程分两步首先把事实表的过滤条件从JOIN中剥离并提前聚合SELECT a.user_id, t.total_amount FROM dim_user a LEFT JOIN ( SELECT user_id, sum(amount) AS total_amount FROM fact_order WHERE order_date 2024-01-01 GROUP BY user_id ) t ON a.user_id t.user_id;其次拿业务中的高频过滤字段在fact_order上建了分区和索引尤其是按日期分区的场景。因为DWS常见做法是列存分区表分区裁剪之后扫描量能下降90%。优化之后作业从60分钟压到40秒以内主要变化是扫描量下降、join方式变为hash join、聚合提前下推。这个例子说明执行计划分析不能停留在“看到某个算子慢”而要思考“为什么这里会生成这种算子”通常是统计信息、过滤条件下推、join顺序三方面的问题。4. 数据倾斜DWS慢作业最容易被忽略的元凶4.1 为什么倾斜会让作业变慢DWS的数据是按分布键distribute key打散到各个DN节点的。理想情况下数据均匀分布每个DN等量处理但如果分布键选得不好、或者数据本身就有天然的热点某个DN上的数据量远大于其他DN就会出现“一拖多”的长尾效应。最直观的现象就是检查集群CPU只有一个或两个DN节点CPU很高其他节点空闲CLI上能看到某个SQL在某个DN上迟迟不结束。整个作业的耗时取决于最慢的那个DN这就是“木桶效应”。4.2 如何查询倾斜表判断一张表是否倾斜可以查DWS提供的系统视图PGXC_GET_TABLE_SKEWNESSSELECT schemaname, tablename, sum(dn_skew_percent) AS total_skew_percent, max(dn_skew_percent) AS max_skew_percent FROM pgxc_get_table_skewness WHERE schemaname public GROUP BY schemaname, tablename ORDER BY total_skew_percent DESC LIMIT 20;更精细的分析是手动按分布键分组统计每个DN的行数。比如分布键是user_id用如下SQL看行数分布SELECT node_name, count(*) FROM table_name GROUP BY node_name;注意node_name是DWS特有的伪列能够反映一行数据实际存储在哪个DN上。如果分布键导致某个DN行数占比超过50%就要立刻处理。4.3 倾斜表的处理和分布键选择处理倾斜最直接的办法是调整分布键。有些场景下可以基于当前常用的查询条件来选比如订单表经常按user_idjoin用户表按order_date做过滤分布键就应该尽量选用高基数、且join查询最常用的字段。避免用性别、地区、状态这类低基数字段做分布键否则很容易倾斜。如果业务上无法修改分布键还有一些替代手段重分布重建表通过CREATE TABLE ... AS SELECT的方式把数据按新分布键重新分布。注意重建期间要评估空间和时间成本使用在线DDL时也要控制业务窗口。倾斜表打散处理对倾斜键值加随机后缀比如把热点user_id拆成多行查询时再通过where条件恢复。这是一种hashtag拆分思路能缓解热点但会给SQL逻辑带来复杂度。大表拆小表把超大事实表按日期拆成多个分区子表减少单个表的扫描和分布压力。顺带补充一个容易踩的坑DWS里修改分布键一般不能直接ALTER TABLE而是需要重建表。所以建表之前一定要想清楚数据特征否则后面改起来非常痛。5. 资源池、并发和队列作业为什么“看起来在跑”5.1 资源池和并发管理DWS的负载管理通过资源池Resource Pool来控制作业消耗。资源池设置了CPU配额、内存上限、并发度等参数。如果作业提交量超过资源池并发上限后面的作业就会进入排队状态表现为“作业一直没开始执行”但也不报错。查看资源池配置和当前使用情况SELECT pool_name, active_statements, max_dop, memory_limit, cpu_limit, running_statements, waiting_statements FROM pg_pool_status;如果发现waiting_statements一直大于0说明资源池确实在排队。常见的调优方向适当调高active_statements并发度但要控制在自己设计的并发范围内避免并发过高导致CPU和内存争抢。给不同的业务类型划分独立资源池比如“跑批池”和“即席查询池”隔离避免一个慢查询把资源池占满拖垮所有业务。对长任务设置合理的statement_timeout和query_band控制防止个别作业“占着茅坑不拉屎”。5.2 内存参数与OOM问题DWS执行作业时每个算子都有内存配额如果work_mem、资源池内存设置不足排序和hash join就会频繁落盘。落盘导致的慢往往表现为磁盘IO升高、执行时间陡增但CPU又不是很高。查看作业内存消耗SELECT query, max_peak_memory, total_peak_memory, memory_skew_percent FROM gs_wlm_session_statistics ORDER BY max_peak_memory DESC LIMIT 10;如果某个作业的峰值内存接近资源池上限同时出现落盘行为就可以考虑调大资源池的memory_limit。降低该作业的并发度。优化SQL本身减少排序和hash join的数据量。比如提前过滤、使用limit下推、避免大范围窗口函数排序。关于OOMDWS中作业OOM不会只“杀掉”自己有时会导致节点内存紧张引发其他作业异常。所以内存参数调整必须配合并发度调整不能只改一个口味。我的建议是每次只调一个变量观察半小时到一小时确认稳定后再调下一个。5.3 锁等待与阻塞场景锁等待造成作业慢也非常常见。典型场景是上游ETL事务一直没有提交下行报表查询等不到元数据锁于是长时间卡住。关键查询SELECT t1.pid AS waiting_pid, t2.pid AS holding_pid, t1.query AS waiting_query, t2.query AS holding_query, t1.wait_start FROM pg_locks t1 JOIN pg_locks t2 ON t1.locktype t2.locktype AND t1.lockmode t2.lockmode WHERE t1.granted false AND t2.granted true;如果查出等待和持有会话优先和业务确认持有会话是否可以结束。确认可以结束后用如下方式终止生产环境务必谨慎-- 终止指定会话 SELECT pg_terminate_backend(pid);另外DWS里事务型业务如果跑得很慢还会带来一个隐蔽问题闲置事务持锁。比如某个会话开了事务、查了点数据然后一直不提交它持有的锁会把其他所有需要该表的作业全部堵住。所以排查时不仅要看active会话也要看idle in transaction状态。这块很多人容易漏。6. 从案例复盘看真实排查过程6.1 案例一统计信息过期导致计划走错现象某张订单表的日跑批任务通常20分钟结束某天突然跑了2小时还没结束。排查先看pgxc_stat_activity发现SQL在DN上跑但CPU不高IO也不高。然后抓出来做EXPLAIN ANALYZE发现对订单表估算行数只有100万实际却有8000万行于是优化器选了Nested Loop内层循环按小表逻辑去执行实际循环次数爆炸。根因表数据量在短时间内暴涨但统计信息没有自动更新优化器还在用旧统计。解决办法两步走——先执行ANALYZE更新统计信息再重新跑作业恢复到25分钟左右。随后设置了周期性 ANALYZE 任务并在批量导入大表数据后自动触发。6.2 案例二分布键选错导致DN单点打满现象多张大表join的报表作业每天都在凌晨跑最近发现某个作业越来越慢且集群监控显示只有一个DN节点的CPU接近100%。排查先看PGXC_GET_TABLE_SKEWNESS发现核心事实表倾斜严重。再用SELECT node_name, count(*) FROM fact_table GROUP BY node_name确认业务热点集中在同一个DN上。进一步查分布键发现在建表时用了status字段作为分布键而status的枚举值只有两三个必然倾斜。根因分布键低基数导致大部分行都落在同一个DN上。处理用user_id作为新分布键重建表重分布后各DN数据量基本均衡慢作业从40分钟降到6分钟。这个案例给团队立了一个规矩核心大表的分布键必须是高基数且常用于join/过滤的字段设计阶段就要评审。6.3 案例三资源池排队导致作业假死现象业务方反馈“所有作业都卡住了”看监控集群CPU不到10%磁盘IO也正常但作业全部停留在提交状态。排查先查pg_pool_status发现某个资源池的waiting_statements高达几十个running_statements已经达到并发上限。再查资源池里正在跑的作业发现有一个大查询因为统计信息有误跑了很久没结束占了并发名额后面的作业全部排队。处理先终止那个异常大查询释放一个并发名额再把异常查询涉及的表做 ANALYZE 并优化SQL让正常作业恢复。随后为不同业务划分独立资源池并给大查询单独设置更小的并发上限避免单个离线作业堵死所有查询。7. 排查命令速查与日常预防7.1 速查表一条命令对应一类问题排查目标使用命令/视图关键字段会话实时状态pgxc_stat_activitystate, waiting, wait_status, query_start作业资源统计gs_wlm_session_statisticscpu_time, max_peak_memory, queue_duration线程等待事件pgxc_thread_wait_statuswait_status, locktype, lockmode锁等待场景pg_locks关联查询granted, lockmode, pid表倾斜分析pgxc_get_table_skewnessdn_skew_percent资源池状态pg_pool_statusrunning_statements, waiting_statements执行计划EXPLAIN ANALYZEActual Rows, Actual Time, Memory Usage节点数据分布GROUP BY node_namecount(*) 对比这张表基本覆盖了90%以上的慢作业排查场景。实际遇到问题时不要背命令而是理解每个视图字段背后的含义。7.2 预防性优化清单排查是事后补救我更建议把工作做在前面。以下几条是我在所有接入的项目里强制要求的“预防清单”关键表必须有ANALYZE计划尤其是批量导入后。统计信息不更新优化器等于“盲人摸象”再好的SQL也白搭。所有核心大表必须做分布键评审高基数、高join频率是底线。离线链路和即席查询一定要分资源池避免互相干扰。每条上线前SQL必须过EXPLAIN ANALYZE重点检查Nested Loop、落盘、全表扫描三类危险信号。监控要盯住“等待事件”而不是只盯CPU/IO很多慢作业发生时集群资源是“假空闲”的实际上是锁等和等待。定期清理闲置事务防止持有锁的僵尸会话堆积日积月累会带来各种莫名其妙的问题。日常值班脚本里我习惯加一条兜底SQL每分钟查一次pgxc_stat_activity一旦发现某个会话state idle且持续超过阈值就触发告警并把会话信息、执行计划、等待事件一起抓到告警平台。这样早发现早处理比等到业务方反馈再排查要省力得多。排查慢作业这件事本身并不难难的是建立一条清晰的定位链路。我自己摸索下来最顺手的顺序永远是“资源水位 - 等待事件 - 执行计划 - 数据分布 - 资源池约束”每一步都只花几分钟很快就能把问题范围压缩到很小的点。你在自己环境里排查的时候也不妨先按这个顺序走一遍。每个企业的数据量、表结构、SQL风格都不一样但底层这几板斧是通用的。如果看完这篇文章能帮你少熬一次夜那这半个小时的阅读就值了。