数据库性能排查这事儿最尴尬的时刻就是业务方在群里喊了一句“数据库卡了”你登录上去之后面对铺天盖地的会话列表和等待事件一时不知道先看哪个。GaussDB 这类国产分布式数据库平时用着没什么脾气真出了问题焦虑感一点不比当年的 Oracle 少。这篇文章不打算讲什么高深的原理就把我平时在 GaussDB 上做性能排查时反复用到的 SQL 整理出来主要覆盖五个场景TOP SQL 定位、锁等待分析、长事务监控、LwLock 轻量级锁排查以及执行计划里最常见的 SubPlan 刺客。如果你是刚接手 GaussDB 的开发或者运维同学这套 SQL 能帮你把“数据库卡了”这个问题快速落成“哪条 SQL 卡了、卡在什么锁上、是谁在阻塞、执行计划坏了没有”这样一个个能推进的结论。1. 性能排查的整体思路与工具准备1.1 先把排查链路理成一条线接到性能问题报告我先不急着连库。先花 10 秒想清楚这个问题是普遍性的还是局部性的是整个集群都慢还是只有某个业务模块慢如果是整个集群慢大概率是资源问题比如 CPU 打满、磁盘 IO 延迟飙升、连接数打满如果是某个模块慢那更可能是某条 SQL、某个锁竞争或者执行计划出了岔子。在 GaussDB 上我习惯按下面这条链路走系统资源确认CPU、内存、磁盘 IO、网络吞吐用 top、iostat、vmstat这步和数据库无关但必须第一个做。很多所谓数据库卡其实是主机层的问题。会话视图确认登录数据库后先看 pg_stat_activity统计活跃会话数、空闲会话数、等待事件分布。统计视图确认如果整体不忙但业务就是慢去看 dbe_perf.top_sql 这类统计视图找耗时和频次异常的 SQL。执行计划确认锁定了具体 SQL再用 EXPLAIN ANALYZE 把执行计划打出来看节点耗时和行数估算。这套链路的核心不是技巧而是收敛。所有排查都忌讳眉毛胡子一把抓把“整个库卡”收敛到“一条 SQL 慢”问题其实就解决一半了。我见过不少同行一上来就抓着一堆等待事件发呆分析一个小时还在原地打转就是因为没有先建立这个收敛意识。1.2 一套趁手的系统视图清单GaussDB 的开放能力比传统商业数据库好很多很多性能数据直接能从系统视图里查。我常用的视图就这几个翻来覆去都用它们。排查目标视图主要信息TOP SQLdbe_perf.top_sql / pg_stat_statementsSQL 累计耗时、执行次数、平均耗时实时会话pg_stat_activity会话状态、等待事件、当前 SQL锁等待pg_locks / pgxc_locks / pgxc_lock_conflicts锁持有、等待、阻塞关系等待事件pg_thread_wait_status线程级等待状态LWLock 定位靠它表统计pg_stat_user_tables表行数、扫描次数、vacuum 信息不过不同版本的 GaussDB尤其在集中式和分布式两种形态下视图命名会有差异。比如分布式环境里锁视图经常是 pgxc_locks等锁冲突视图是 pgxc_lock_conflicts而集中式环境里可能是 pg_locks。拿到一个新环境先跑一条 SQL 确认视图是否存在SELECT viewname, schemaname FROM pg_views WHERE viewname LIKE %lock% OR viewname LIKE %top_sql% ORDER BY viewname;这一步很土但很管用。别拿着文档上的 SQL 直接敲上去发现报错才回去翻版本浪费时间。GaussDB 版本迭代快不同小版本、不同部署形态之间视图名有出入是常态我的经验是先把环境里实际存在哪些视图摸清楚再套用下面的排查 SQL。2. TOP SQL 定位别让慢 SQL 藏在大海里2.1 快速抓取 TOP SQL如果 GaussDB 开启了 SQL 统计开关instr_unique_sql_count 大于 0dbe_perf.top_sql 里会有累计的 SQL 执行数据。我最常用的排序方式是总耗时倒序因为总耗时等于执行次数乘以单次耗时它能暴露“平时很稳、但次数极多”的隐形消耗也能暴露“单次就很离谱”的重量级慢 SQL。SELECT query_id, query, calls, total_exec_time, mean_exec_time, total_exec_time / calls AS avg_time_ms, rows, total_exec_time / NULLIF(rows, 0) AS time_per_row_ms FROM dbe_perf.top_sql ORDER BY total_exec_time DESC LIMIT 20;如果你要找的是“此刻正在跑的会话里谁最慢”统计视图不够实时应该用 WLM 会话视图。分布式环境里这个视图经常叫 gs_wlm_session_query_info_all它记录的是当前还在执行的查询elapsed_time 表示从开始到现在的耗时对定位“刚才那波卡顿”很有用。SELECT query_id, user_name, start_time, elapsed_time, query FROM gs_wlm_session_query_info_all WHERE elapsed_time 1000 ORDER BY elapsed_time DESC LIMIT 20;说明一下elapsed_time 的单位在各版本里不见得一致有的返回秒有的返回毫秒。先查一条样本试一下再排序别把单位搞混。2.2 拿到 TOP SQL 之后看些什么很多同学拿到 TOP SQL 列表就开始对着文本发呆其实顺序应该是先看统计数据再看计划最后才看文本。第一看 calls 和 avg_time 的组合。calls 很高、avg_time 很低说明这条 SQL 被高频执行单次不慢但总时间占比高。这类 SQL 的优化方向是减少执行次数比如改成批量操作、加缓存。calls 不高、avg_time 很高说明是单条重量级 SQL重点看执行计划有没有走偏索引是否失效。第二看执行计划。对可疑 SQL 跑 EXPLAIN ANALYZE重点不是看计划树长什么样而是看每个节点的 actual rows 和估算 rows 是否差距巨大。一旦 actual rows 比估算大出两三个数量级统计信息基本是脏的优化器选错计划是必然的。我遇到过一次很典型的案例一张 5000 万行的订单表业务反馈某查询平时毫秒级某天突然变成 3 秒。EXPLAIN ANALYZE 一打发现原来该走 Index Scan 的地方变成了 Seq Scan原因就是表行数膨胀了统计信息没有及时更新优化器误判全表扫描更快。ANALYZE 之后执行计划恢复正常耗时立刻降回毫秒级。第三才是看 SQL 文本。看文本主要是为了识别有没有可以改写的地方比如 IN 列表过长、隐式类型转换导致索引失效、函数包裹列导致无法走索引。这些属于执行计划优化范畴后面 SubPlan 那节还会展开。3. 锁等待分析业务卡死的头号元凶3.1 一条 SQL 找出所有等锁会话锁等待在 pg_stat_activity 里表现为 wait_event_type 是 Lock。注意这里说的“锁”是重量级锁包括表锁、行锁、事务锁和 LwLock 不是一回事但排查入口是同一个视图。SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS wait_duration, query FROM pg_stat_activity WHERE wait_event_type Lock AND state idle ORDER BY wait_duration DESC;这条 SQL 能告诉你多少个会话在等锁、等了多久、等的是什么类型的锁。但有个关键信息它给不出来到底是谁握着锁不放手。要回答这个问题要么用 GaussDB 自带的冲突视图要么用 pg_locks 自关联。在支持 pgxc_lock_conflicts 视图的版本里直接查它是最省事的里面已经把申请锁会话、阻塞会话、SQL 文本、客户端信息都列出来了。SELECT * FROM pgxc_lock_conflicts;3.2 定位阻塞源头与终止会话的正规姿势如果版本不支持冲突视图就用经典的自关联 SQL。核心逻辑是把争取锁granted false的会话和持有锁granted true的会话通过锁的标识字段串起来。SELECT blocked.pid AS blocked_pid, blocked.client_addr AS blocked_client, left(blocked.query, 120) AS blocked_query, blocking.pid AS blocking_pid, blocking.client_addr AS blocking_client, left(blocking.query, 120) AS blocking_query, bl.locktype FROM pg_locks bl JOIN pg_stat_activity blocked ON blocked.pid bl.pid JOIN pg_locks bing ON bing.locktype bl.locktype AND bing.database bl.database AND bing.relation bl.relation AND bing.page IS NOT DISTINCT FROM bl.page AND bing.tuple IS NOT DISTINCT FROM bl.tuple AND bing.transactionid IS NOT DISTINCT FROM bl.transactionid JOIN pg_stat_activity blocking ON blocking.pid bing.pid WHERE bl.granted false AND bing.granted true AND blocked.pid blocking.pid;拿到阻塞源blocking_pid之后不要脑门一热就去 kill。先看一眼阻塞会话在干什么如果它是一条跑了 10 个小时的报表 SQL业务上已经不重要了那可以放心终止如果它是一个正在跑核心交易的会话杀了是要出事故的。终止的姿势也有两种pg_cancel_backend 是取消当前查询连接还在、事务还在pg_terminate_backend 是断开会话连事务一起干掉。优先用 cancel不行再 terminate。提示终止会话前最好把双方的 SQL 文本和 session 信息保存下来复盘的时候用得上。尤其分布式环境一个业务会话可能跨多个节点杀错节点反而制造新的不一致。3.3 几种屡见不鲜的锁等待场景实战里锁等待翻来覆去就那几个戏码。第一个是 DDL 撞上 DML。ALTER TABLE 这类 DDL 要拿 AccessExclusiveLock和你业务里的任意 DML 锁都冲突。白天业务高峰跑 DDL等于封路施工后面堵一大串。对策很简单DDL 全挪到低峰期需要在线加字段时评估版本是否支持在线 DDL。第二个是热点行更新。同一个账户、同一个库存行被并发 update后到的会话要等前面的提交才能拿到行的 transactionid 锁。排队短还好如果每个事务都要等几秒那这条热点行的处理能力就到瓶颈了。对策是业务侧做拆分比如账户余额拆成多行、库存扣减做排队缓冲SQL 层面很难根治。第三个是外键锁。往子表插数据时数据库会在父表对应行上拿锁做完整性检查。高并发插入子表时如果有大量子表并发引用同一条父表记录父表那行就成了全局热点锁排队现象非常明显。外键约束如果业务上可以用应用层保证或者数据仓库场景根本不需要干脆考虑去掉收益立竿见影。4. 长事务监控缓存雪崩和表膨胀的推手4.1 一条 SQL 揪出所有长事务长事务的定位比锁还简单锁是会话之间的互相卡脖子长事务是会话自己赖着不走。直接从 pg_stat_activity 里按事务开始时间xact_start排序就能拿到。SELECT pid, usename, datname, state, xact_start, now() - xact_start AS xact_age, query_start, now() - query_start AS query_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state idle AND xact_start IS NOT NULL AND now() - xact_start interval 30 seconds ORDER BY xact_age DESC;阈值设成 30 秒是因为大多数 OLTP 事务执行时间都在毫秒到数百毫秒级别超过 30 秒的事务已经有排查价值。当然这个阈值要按业务调跑批业务的正常事务可能就是十几分钟你把阈值设成 30 秒会让监控天天报警警报疲劳之后真出事反而没人看了。特别提醒一下state 字段里有一种状态是 idle in transaction意思是这个事务已经把 SQL 跑完了但一直没提交也没回滚。这种会话往往不占锁但它持有事务快照是 MVCC 旧版本清理的头号敌人后面危害部分细说。4.2 长事务为什么能拖垮整个集群长事务的杀伤力体现在三个层面。第一是持锁时间拉长。锁是跟着事务走的不是跟着单条 SQL 走的事务只要不结束它拿到的表锁、行锁就全不释放。一个长事务在业务高峰拖住一堆小事务排队后面就是连锁反应。第二是 MVCC 旧版本堆积。关系型数据库的 MVCC 机制里数据页上被事务更新过的旧行不会立刻删除要等其他事务的活跃快照不再引用它之后VACUUM 才能把它们清掉。长事务只要存在它那个事务快照对所有旧版本都可见清理机制就被冻结了。表现是什么表和索引持续膨胀查询访问的页数越来越多磁盘占用越来越高即使所有长事务都退出了VACUUM 还要花很长时间才能把堆积清完。打个比方长事务就像一个占着超市储物柜不走的顾客保洁阿姨没法打扫这个柜子后来的人也用不了。第三是日志和复制相关资源的堆积。在 GaussDB 这类基于日志的架构里如果开启了逻辑复制或物理级联复制槽需要保留事务开始之后的所有 WAL 日志长事务会拖住日志推进严重时 WAL 积累到磁盘告警整个集群进入只读保护。4.3 处置长事务的实战建议处置的第一步是先分清楚它是不是真的还活着。state 是 active 且一直有 SQL 在跑的长事务要评估 SQL 本身是不是有问题state 是 idle in transaction 的属于典型的“忘记提交”可以联系业务确认后直接终止。第二步是设置兜底参数。GaussDB 里 idle_in_transaction_session_timeout 参数能自动断开停留在 idle in transaction 超过指定时间的会话这个参数强烈建议打开。事务超时方面还有 statement_timeout 控制单条 SQL 执行时间但注意别一刀切设太短跑批 SQL 会被误杀。第三步是业务侧改造。长事务大多不是数据库的问题而是应用的事务设计问题。常见病一个业务接口里做了十几次数据库操作中间还夹着一次远程 HTTP 调用事务迟迟不提交批量任务循环里每条数据都开一个新事务但偶尔某一条报错导致整体回滚变慢ORM 框架自动开启事务后没有及时 commit。遇到这些SQL 层面只能缓解根治要靠代码 review。5. LwLock 轻量级锁排查看不见的内部竞争5.1 LwLock 是什么LwLock轻量级锁和前面说的 Lock 完全不同。Lock 保护的是用户对象表、行、事务由锁管理器统一管理等待时能看到具体会话、具体对象LwLock 保护的是数据库内部共享内存结构比如缓冲区、WAL 写入位置、事务提交日志等。LwLock 持有时间极短正常情况下微秒级就释放你根本感知不到它。但一旦出现大量会话堆积在某个 LWLock 上说明内部组件出现了资源争抢。一个合适的类比是图书馆的借阅登记台。读者数据库线程每次借书都要去登记台办手续正常情况排队几秒钟就完事但某天登记台前的队伍排了几百米那大概率不是登记台本身坏了而是借书的人太多、归还的书没及时上架、或者门口查包太慢。LWLock 等待是结果不是病根。5.2 用等待事件定位 LwLock定位 LwLock 最直接的视图是 pg_thread_wait_status它能看到数据库所有线程实时的等待状态wait_status 字段就是线程当前被卡在什么地方。SELECT schemaname, relname, sessid, thread_id, wait_status, wait_event FROM pg_thread_wait_status WHERE wait_status LWLock ORDER BY wait_event NULLS LAST;也可以在会话级视图看把 wait_event_type 过滤成 LWLock这样能顺带看到是哪条 SQL 在等SELECT pid, state, wait_event, now() - query_start AS wait_duration, query FROM pg_stat_activity WHERE wait_event_type LWLock ORDER BY wait_duration DESC;看到会话在等 LwLock 后先别急着调数据库参数。要顺着 wait_event 的名字去对病根。不同 LwLock 名字对应的东西完全不一样搞错了方向参数调了也白调。5.3 常见 LwLock 的根因与对策常见的等待事件有 CLog、BufMappingLock、WALWriteLock、ProcArrayLock 这几个遇到频率最高。等待事件保护对象高发根因CLog事务提交日志提交频率过高、高并发小事务BufMappingLockbuffer 映射表缓存池过小数据页频繁驱逐重读WALWriteLockWAL 日志写入磁盘 IO 延迟高、WAL 无处缓冲ProcArrayLock事务快照数组事务开启/提交过于密集PartitionLock分区结构高并发分区 DDL 或分区裁剪冲突CLog 竞争最常见于“高并发短事务”场景。每个事务提交都要在 CLog 里记录一条如果业务每秒提交上万个小事务CLog 那条路径就会排队。对策是降低提交频率把多条操作合并成一个事务或者从 Oracle 迁移过来的批量应用检查一下是否有逐条提交的习惯。BufMappingLock 是 buffer 池不足的典型信号。查询需要的数据页不在内存里要从磁盘拉进来当并发查询很多、内存又小buffer 里的页被反复逐出再读入映射表就成了瓶颈。对策很简单粗暴调大 shared_buffers同时检查是不是有大量全表扫描在污染缓存。GaussDB 的 shared_buffers 建议值一般在物理内存的 20% 到 30%但具体还要配合操作系统的 huge pages、以及是否开启 numactl 等一起评估不要无脑调。WALWriteLock 则要去查磁盘。WAL 日志的写入如果在机械盘或者负载极高的共享存储上每次 fsync 都可能拖慢整个提交链路。把 WAL 放到低延迟的高性能磁盘上是最有效的处理方式。6. SubPlan 子计划藏在执行计划里的性能刺客6.1 SubPlan 是怎么产生的SubPlan 是执行计划里很不起眼但破坏力极大的一类节点。它的来源很常见SQL 里写了 IN、EXISTS、标量子查询时优化器如果没有把子查询上提成连接就会在主查询的节点下面挂一个 SUBPLAN 节点。更要命的是这种子计划如果做的是相关子查询每扫描主表一行就可能被重新执行一次。主表 100 万行、子查询每次执行 1 毫秒这 100 万次就是 1000 秒。这个账很好算但很多同学看到执行计划里只是多了一个 SubPlan 节点根本不重视。打个比方你在一个陌生城市送外卖每送一单都要先回一趟站点查客户地址而不是出发前把所有地址一次查好。如果只送一单无所谓送 1 万单你就永远在路上。SubPlan 的问题本质是“重复计算”跟循环里写 SQL 属于同一个坏味道。6.2 用 EXPLAIN 验证 SubPlan 的代价识别 SubPlan 的办法很简单EXPLAIN ANALYZE 打出来之后在计划树里找 SubPlan 字样然后看它的 actual time 和实际循环次数。举一个我优化过的真实案例。业务表 orders 有近 200 万行vip_customers 有 10 万行原 SQL 长这样SELECT order_id, order_time FROM orders WHERE customer_id IN (SELECT id FROM vip_customers);当时执行计划的主要形状是Seq Scan on orders Filter: (customer_id ANY (subplan)) SubPlan 1 - Materialize - Seq Scan on vip_customersSubPlan 1 被挂在 orders 的 Seq Scan 下面意味着每扫描一行订单都可能在子计划里去检查一次当前客户是不是会员。虽然 Materialize 节点让子计划只物化了一次但 Filter 的逐行判断开销仍然很大。这条 SQL 实际执行耗时 8.5 秒。优化方式就是改写 SQL把 IN 子查询改成 JOINSELECT o.order_id, o.order_time FROM orders o JOIN vip_customers vc ON o.customer_id vc.id;改写后执行计划变成 Hash Joinorders 和 vip_customers 各扫一遍然后在哈希表里碰撞总耗时降到 0.34 秒。同一张表、同一条业务逻辑差了 25 倍。这只把 SQL 文本改了索引一条没加。这里要强调一句不是所有 SubPlan 都要改写。如果子查询结果集很小、主查询的行数也小或者子查询有唯一索引可以快速命中SubPlan 的开销完全可接受。判断标准不是看到 SubPlan 就紧张而是看它的“执行次数乘以单次代价”是否超出了整条 SQL 的合理范围。6.3 改写思路与调整参数改写思路按场景来分。第一种是 IN/EXISTS 子查询优先尝试改成 JOIN。注意 IN 语义自带去重改 JOIN 时如果子查询结果中有重复值可能造成结果翻倍需要在 join 前对子查询做 DISTINCT或者用 EXISTS 语义带依赖列。第二种是标量子查询例如 SELECT (SELECT name FROM customer WHERE id o.customer_id) FROM orders o这种可以改成 LEFT JOIN但要注意语义如果有聚合要做等价的 GROUP BY 处理。第三种是关联子查询把相关条件提取出来改写成长用 LATERAL 或临时表先算好再关联。GaussDB 也给了参数层面的杠杆。rewrite_rule 参数控制了一批 SQL 重写规则子查询上提、IN 列表转 JOIN 这类转换都可以由它影响IN 列表场景还有 qrw_inlist2join 之类的控制参数当 IN 列表长度超过阈值时自动改写为 JOIN。不过参数优化这事有两个原则一是先改 SQL 后动参数SQL 能解决的问题不要指望优化器兜底二是参数改动要在测试环境用真实数据量压一遍别信网上流传的所谓万能配置。最后给一个排查建议以后遇到 SQL 突然变慢执行计划里出现 SubPlan 且主表行数很大时第一反应不是加索引而是先做个数学题算出这个 SubPlan 的重复执行代价。很多索引加不上、加上也没用的问题本质都是 SQL 写法该改没改。7. 一点个人的排查体会上面这些 SQL 我平时是揉成一套脚本用的。出问题时顺序永远是先 TOP SQL 看有没有异常耗时再看锁等待视图有没有阻塞链接着查长事务有没有旧事务卡着随后用等待事件看是不是 LwLock 在捣乱最后对具体 SQL 打 EXPLAIN ANALYZE 找 SubPlan 这类执行计划刺客。这套流程不保证每次都能一击命中但至少能在业务方追问“好了没”的时候给出一个不丢人的中间结论。还有一句实话GaussDB 版本迭代很快不同形态、不同小版本的系统视图命名经常有差异我给的 SQL 到你手里可能得改个名字才能跑。这不丢人。赶紧在库上跑一条 \dv 或者查 pg_views 确认视图真实存在比背什么文档都靠谱。多跑几遍、多记几份笔记慢慢你就不怕这种问题了。