1. 慢查询排查先定位“病根”再对症下药做PostgreSQL运维的人迟早都会遇到这么一天应用层超时告警刷屏老板冲过来问数据库怎么回事。你登录上去一看连接数爆满CPU跑满但到底是谁在作妖一时半会儿还真看不出来。这时候就需要一套排查方法论把慢查询、长事务、阻塞这三类问题一次性查清楚。慢查询是“症状”长事务往往是“病灶”阻塞则是两者叠加后的“并发症”所以我会按这个顺序逐步展开。1.1 开启pg_stat_statements让PostgreSQL自己记账排查慢查询的第一步不是去翻应用日志而是开启PostgreSQL自带的统计模块pg_stat_statements。这个扩展相当于数据库的“黑匣子”它会把每条SQL的执行次数、总耗时、平均耗时、最大耗时、读取/命中的缓冲块数全部记录下来。没有这个扩展排查慢查询就像摸黑修电路只能靠猜。启用方式分两步。先修改配置文件postgresql.conf在shared_preload_libraries里加上这个库shared_preload_libraries pg_stat_statements注意这一步必须重启PostgreSQL进程才能生效因为它属于共享库预加载不是普通扩展光执行CREATE EXTENSION是没用的。重启之后再连到目标数据库执行CREATE EXTENSION pg_stat_statements;这一步只需要在你要监控的数据库里执行一次统计信息是实例级的所有数据库共享只是视图需要每个库自己创建才能查。启用之后查看统计信息的核心SQL长这样SELECT queryid, calls, round(mean_exec_time::numeric, 2) AS avg_ms, round(max_exec_time::numeric, 2) AS max_ms, round(total_exec_time::numeric, 2) AS total_ms, rows, shared_blks_hit, shared_blks_read FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;这里重点关注的字段是total_exec_time和mean_exec_time。前者的意义在于找出“累计消耗最多的查询”这类查询就算单次不快架不住调用频率高累加起来就是大头后者的意义在于找出“单次执行特别慢的查询”通常是真正的性能瓶颈点。实际使用中我会先按total_exec_time排序看一遍再按mean_exec_time排序看一遍两份清单对照着分析。1.2 用log_min_duration_statement抓“现场”pg_stat_statements擅长做“事后统计”但它有个天生的短板只记录SQL文本的前若干字节而且不包含执行计划的信息。如果一条慢SQL的文本不是稳定的字面量比如带了不同参数值的WHERE条件统计信息会按queryid把不同参数聚合到一起分析的粒度就不够细。所以我的习惯是同时开启慢查询日志设置一个阈值让PostgreSQL把超过阈值的每一条SQL连同执行时间一起写进日志。修改配置log_min_duration_statement 1000单位是毫秒意思是执行超过1秒的SQL全部记入日志。生产环境我建议先设500跑一周看情况如果瓶颈集中在极端慢的查询再放宽到2000避免日志量过大。日志里每条慢SQL会带时间戳、数据库名、用户名、客户端地址配合pg_stat_statements的统计视图一起看既能知道“哪些SQL总体上消耗大”又能看到“某一次执行到底慢在哪”。这里有个容易忽略的点log_min_duration_statement只对“执行完成的语句”生效。如果一条SQL跑到一半被pg_cancel_backend取消或者因为死锁被回滚它可能不会出现在慢查询日志里但会以另一种形态出现在我们后面讲的pg_stat_activity和日志的ERROR记录里。所以排查的时候慢查询日志、错误日志、活动会话视图这三份数据要交叉着看缺一不可。1.3 EXPLAIN ANALYZE把慢查询拆开看统计和日志只能告诉你“哪条SQL慢”但要回答“为什么慢”就必须看执行计划。在慢SQL前面加上EXPLAIN ANALYZE让PostgreSQL真正执行一遍把每一步的实际耗时、扫描行数、内存占用全部打印出来EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id 10248 ORDER BY created_at DESC LIMIT 50;拿到执行计划之后我一般按三个步骤读。第一步看有没有Seq Scan。全表扫描本身不算坏事小表扫一遍开销很低但如果一个大表几百万行还在做全表扫描第一个怀疑对象就是索引没建或者没被用上。执行计划里如果显示Seq Scan on orders且rows很大然后出现Sort说明过滤条件走了全表排序又额外消耗了内存和CPU。第二步对比actual rows和estimated rows。PostgreSQL基于统计信息估算行数但统计信息可能过期或者WHERE条件里的表达式太复杂导致估算失真。如果预估100行实际扫了10万行说明统计信息需要更新执行ANALYZE一下通常能缓解。第三步看Buffers部分。shared hit代表从共享缓冲池里直接命中速度快shared read代表要从磁盘读这是慢的主要来源。一次查询如果shared read的块数特别多要么是共享缓冲区shared_buffers配得太小要么是这条SQL的数据访问模式本身就不适合走缓冲池比如大范围的聚合分析。很多人会忽略EXPLAIN ANALYZE会真实执行SQL这一点在写操作上执行时一定要小心。排查慢查询时如果是INSERT/UPDATE/DELETE语句建议在事务里跑BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;这样既能拿到执行计划又不会把数据真的改掉。2. 长事务排查拖住系统的“老钉子户”慢查询本身不可怕跑完就结束了。真正可怕的是长事务——一个事务从开始到结束一直持有锁、占用快照拖着不提交也不回滚。它会卡住autovacuum的清理工作导致表膨胀甚至引发事务ID回卷的严重故障。所以查完慢查询下一步必须排查长事务。2.1 pg_stat_activity每一个会话的“现场状态”pg_stat_activity是整个排查过程里最重要的一张视图没有之一。它实时反映每一个后端进程当前在干什么。核心字段的解读决定了你能不能快速定位问题state当前状态最常见的几个取值我们下一段细说。query_start当前这条查询开始执行的时间。xact_start当前事务开始的时间。注意事务可能包含多条SQLxact_start比query_start更早。backend_start后端进程的启动时间代表这条连接的存活时长。wait_event_type和wait_event如果会话在等待什么这里会显示等待类型和具体事件。application_name连接的应用名比如JDBC、psql排查时能快速区分是哪个业务方在捣乱。我最常用的一个查询是找出所有非空闲且运行超过阈值的会话SELECT pid, datname, usename, application_name, client_addr, state, now() - query_start AS query_duration, now() - xact_start AS xact_duration, wait_event_type, wait_event, left(query, 120) AS query_sample FROM pg_stat_activity WHERE state idle AND now() - query_start interval 5 minutes ORDER BY xact_duration DESC NULLS LAST;这个SQL把所有“活跃中的”“空闲但事务内的”会话全捞出来按事务时长倒序排一眼就能看出谁是最老的钉子户。2.2 事务状态的五种形态别只看activepg_stat_activity.state字段有五种常见取值很多人只盯着active看结果漏掉了真正的大问题state含义是否危险active正在执行查询看耗时idle连接空闲不在事务内基本无害idle in transaction在事务内但当前没有执行SQL很危险idle in transaction (aborted)事务内出错处于回滚等待态危险fastpath function call正在执行快速路径函数少见但也要关注最容易踩坑的是idle in transaction。应用代码里开了事务执行了几条SQL然后因为业务逻辑卡在别的地方比如调用了外部HTTP接口迟迟不返回事务既不提交也不回滚一直挂着。这种会话本身不消耗CPU但它持有的快照和锁会持续产生影响autovacuum无法清理被它看到的死元组表就越涨越大。我曾经遇到过一个极端案例一条idle in transaction的会话挂了整整一个周末回来之后发现核心业务表膨胀了三倍磁盘空间告急。针对这类问题PostgreSQL 9.6之后提供了idle_in_transaction_session_timeout参数设置后可以让空闲在事务内的会话被自动断开idle_in_transaction_session_timeout 60000单位毫秒60秒是我在多数系统上验证过的相对稳妥的取值。这个参数只针对“空闲在事务内”的状态不会误杀真正在跑SQL的长查询。2.3 长事务为什么是“定时炸弹”长事务最危险的地方在于事务ID回卷保护机制。PostgreSQL使用32位事务ID总量约42亿一旦用尽会强制关闭数据库并进入单用户模式做紧急处理。虽然这个场景极少发生但长事务会阻止事务ID的回收尤其是那些迟迟不结束的idle in transaction会话。更常见也更隐蔽的危害是xmin水位线问题。pg_stat_activity里的backend_xmin字段表示当前事务能看到的最老快照位置autovacuum只清理比xmin更老的死元组。如果有一个长事务把xmin钉住不动所有超过该水位线的死元组全部无法清理表膨胀、索引膨胀、查询性能下降就会接踵而至。排查时可以这样查看全局最老的xminSELECT pid, usename, state, backend_xmin, now() - xact_start AS xact_age, left(query, 80) AS query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY backend_xmin ASC LIMIT 10;如果排在最前面的会话xact_age已经以小时甚至天为单位基本可以断定这就是导致膨胀的元凶。3. 阻塞查询排查理清“锁链”找到源头慢查询和长事务通常能解释大部分性能问题但还有一种情况查询本身不慢却卡着不动也就是我们常说的“被锁住了”。PostgreSQL的锁机制比较复杂排查时如果只会看active状态很容易误判为CPU忙实际上数据库可能完全是在等锁。3.1 从wait_event判断“卡”在哪pg_stat_activity里有两个关键字段wait_event_type和wait_event。前者是等待事件的分类后者是具体事件名称。最常见的几个wait_event_type如下Lock在等待获取锁。具体wait_event可能是transactionid等行级锁、relation等表级锁、tuple等行更新等。Activity通常是AutoVacuumMain、LogicalLauncherMain等后台进程在正常闲等不是问题。IO在等数据页从磁盘读入或者等fsync刷盘常见于大查询或checkpoint期间。Client在等待应用端读取数据或发送下一条指令典型场景是应用忘了关闭结果集。判断是否“被阻塞”最直接的方法是看wait_event_type Lock。这时候会话一定在等某把锁而且大概率因为别的事务没有释放导致的。下面这段查询可以快速列出所有在等锁的会话SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS wait_duration, left(query, 100) AS query FROM pg_stat_activity WHERE wait_event_type Lock;3.2 pg_locks锁的“户口本”等锁是症状想知道谁握着锁不放必须查pg_locks。这张视图记录了当前实例内所有的锁对象常用字段有locktype锁类型常见的有relation表级、transactionid事务级、tuple行级、virtualxid虚拟事务ID。relation被锁对象的OID关联pg_class可以拿到表名。pid持有或等待锁的后端进程ID。grantedtrue表示已经拿到锁false表示正在等待。一张经典的阻塞关系查询是把自己跟自己关联找出谁在等谁的锁SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, blocked_query.query AS blocked_query, blocker_query.query AS blocker_query, blocked.wait_event_type, blocked.wait_event FROM pg_stat_activity blocked JOIN pg_locks blocked_locks ON blocked.pid blocked_locks.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.granted JOIN pg_stat_activity blocker ON blocking_locks.pid blocker.pid LEFT JOIN LATERAL ( SELECT query FROM pg_stat_activity WHERE pid blocked.pid ) blocked_query ON true LEFT JOIN LATERAL ( SELECT query FROM pg_stat_activity WHERE pid blocker.pid ) blocker_query ON true WHERE NOT blocked_locks.granted;这段SQL的核心逻辑是找到所有granted false的锁请求即正在等待的会话再找到同一把锁上granted true的持有者最后把两者的PID和当前SQL都列出来。这样就能把阻塞链的“受害者”和“施害者”一一对应上。现实中的阻塞经常不止一层A锁住了BB又锁住了C这时需要顺着链条往上追。下面的递归CTE可以追溯到最顶层的源头WITH RECURSIVE lock_tree AS ( SELECT pid, 0 AS depth, ARRAY[pid] AS path, wait_event_type, wait_event, left(query, 80) AS query FROM pg_stat_activity WHERE wait_event_type Lock UNION ALL SELECT a.pid, lt.depth 1, lt.path || a.pid, a.wait_event_type, a.wait_event, left(a.query, 80) FROM lock_tree lt JOIN pg_locks l ON l.pid lt.pid AND NOT l.granted JOIN pg_locks lo ON lo.locktype l.locktype AND lo.database IS NOT DISTINCT FROM l.database AND lo.relation IS NOT DISTINCT FROM l.relation AND lo.transactionid IS NOT DISTINCT FROM l.transactionid AND lo.granted JOIN pg_stat_activity a ON lo.pid a.pid WHERE NOT lt.path ARRAY[a.pid] ) SELECT * FROM lock_tree ORDER BY depth;不过说实话递归CTE在超复杂锁链下性能一般而且容易把自己绕晕。我实际排查时更多是先跑一次简单版找到“等锁的会话”和“持锁的会话”如果持锁者本身也在等锁再手动多查几层一般两层就能定位到根因。4. 一键诊断组合脚本与实操实录前面的内容分别介绍了慢查询、长事务、阻塞的排查思路但实际故障处理时时间紧迫不可能一条一条SQL慢慢敲。我把常用诊断SQL整理成了一套组合脚本遇到线上问题直接跑节省时间的同时也能避免遗漏。4.1 慢查询Top 10SELECT round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, calls, rows, left(query, 100) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;这条SQL优先关注总耗时适合回答“数据库整体的时间都花在哪儿了”。如果要看单次慢把排序字段换成mean_exec_time DESC即可。4.2 所有运行中的查询时长SELECT pid, datname, usename, state, now() - query_start AS query_age, now() - xact_start AS xact_age, wait_event_type, wait_event, left(query, 150) AS query FROM pg_stat_activity WHERE state idle ORDER BY xact_age DESC NULLS LAST;这条SQL是所有排查的“总入口”任何突发故障上来先跑它能在一屏内看到所有会话的状态、时长、等待情况。state idle会漏掉一种情况就是idle in transaction它是非idle的吗实际上PostgreSQL中idle in transaction不等于idle它会被过滤条件包含进来这正好是我们需要关注的。4.3 锁等待详情SELECT pid, locktype, relation::regclass AS relname, mode, granted, now() - query_start AS wait_age FROM pg_locks LEFT JOIN pg_stat_activity USING (pid) WHERE locktype IN (relation, transactionid, tuple) AND NOT granted ORDER BY wait_age DESC;relation::regclass可以把OID直接转成表名省去手动关联pg_class的麻烦。这里过滤掉virtualxid和extended等噪声锁只看最常出问题的三类锁。4.4 终止查询的正确姿势定位到具体PID之后下一步是决定怎么终止。PostgreSQL提供了两个函数区别很大函数行为适用场景pg_cancel_backend(pid)礼貌地请求取消当前查询类似发送中断信号查询还在跑能响应取消请求pg_terminate_backend(pid)强制终止整个后端进程像强杀进程查询无法取消会话卡死连接有异常pg_cancel_backend是最优先的选择它会让正在执行的SQL主动检查取消标志然后终止不会中断连接事务也可以继续。但如果被取消的查询处于idle in transaction状态或者因为某种原因无法响应取消请求就需要pg_terminate_backend(pid)直接断掉连接。强制结束时应用端会收到连接断开的报错所以操作之前最好确认一下对应业务是否可接受瞬间闪断。举一个实操案例。某次线上事故一个报表查询每天定时跑某天突然从原来的10秒变成20分钟不结束。我先跑了第4.2节的SQL发现这个会话wait_event_type显示Lock说明不是查询本身慢而是被锁堵住了。再跑第4.3节的SQL发现它正在等待一张业务表的AccessExclusiveLock而持锁者是一个idle in transaction的会话已经挂了40多分钟。确认该持锁会话没有正在执行的关键事务后我执行了pg_terminate_backend(持锁PID)。锁立刻释放报表查询在3秒内跑完全程影响范围控制在单个会话内。4.5 预防性参数组合排查完问题亡羊补牢更重要。我建议在这几个参数上做统一设置降低同类问题再犯的概率# 慢查询日志阈值单位毫秒 log_min_duration_statement 1000 # 单条语句最大执行时间超过自动取消 statement_timeout 30000 # 锁等待超时超过自动放弃 lock_timeout 10000 # 空闲事务超时超过自动断开 idle_in_transaction_session_timeout 60000statement_timeout和lock_timeout的区别值得说清楚。前者管所有SQL的执行总时长后者只管等待锁的时间。如果一条SQL本身要跑很久比如数据仓库里的批处理statement_timeout设得太小会误杀这时候可以单独用lock_timeout来防止它在锁上死等而执行时长按业务需求放宽。生产环境的推荐做法是先加lock_timeout确认没有锁等待问题之后再把statement_timeout放宽到业务可接受的上限。5. 排查过程中的“坑”与实战心得工具和方法都齐了但真正干活的时候还有一些容易让人误判的细节。这些坑我基本都踩过一遍列出来供参考。5.1 pg_stat_statements看不到数据的几个原因最常见的情况是配好了shared_preload_libraries重启了实例也执行了CREATE EXTENSION但查视图一直是空的。原因大概率是pg_stat_statements.track参数问题。默认值是top只统计顶层SQL如果应用使用了大量存储过程内部嵌套的SQL默认不统计。可以把参数改成allpg_stat_statements.track all另一个常见原因是查询了错误的数据库。pg_stat_statements视图的内容按数据库区分统计数据是实例级汇总但你在哪个库查就只能看到该库自己相关的条目。如果应用连的是A库你却在B库执行查询看到的结果自然不全。还有一点pg_stat_statements有内存上限由pg_stat_statements.max控制默认1000条。SQL数量超过上限后新的SQL会挤掉旧的统计信息所以定期手工清理是一种好习惯SELECT pg_stat_statements_reset();这个操作建议在业务低峰期做否则会把有价值的长期统计冲掉。我在生产环境会把它写进每周的定时维护任务。5.2 “假的”慢查询日志里出现长时间执行的SQL不一定就是SQL本身有问题。有几种情况需要区分。第一种是checkpoint期间的IO抖动。PostgreSQL的checkpoint会把脏页刷到磁盘此时大量查询会出现IO等待单条SQL的执行时间被拉长但等checkpoint结束就恢复正常。判断方法是看慢查询日志的时间分布是不是集中在checkpoint时间段对应查看pg_stat_bgwriter里的checkpoint_write_time。第二种是autovacuum造成的IO争抢。大表做vacuum时会扫描大量页面产生密集的IO操作导致同时进行的业务查询受影响。这类问题的根源是autovacuum触发时机太频繁或者表膨胀严重导致vacuum耗时过长需要回到长事务治理的层面去解决。第三种是首次访问的冷数据。数据页不在共享缓冲区要从磁盘读入第一次查询比后续查询慢一个数量级是正常现象。判断方法是同一SQL连续跑两次第二次明显变快就说明是冷缓存问题不是SQL结构问题。5.3 “阻塞”不等于“死锁”很多初学者容易把阻塞和死锁混为一谈。死锁是指两个或多个事务互相持有对方需要的锁谁都动不了PostgreSQL依靠死锁检测机制自动回滚其中一个事务日志里会看到deadlock detected字样。而阻塞通常是一方持有锁另一方在等待只要持有方提交或回滚等待方就能继续。需要明确的是PostgreSQL默认的死锁检测时间是deadlock_timeout默认1秒。也就是说每条等待锁的SQL至少要等1秒数据库才会去检测是否构成死锁环。如果你的应用经常报“deadlock detected”说明你的业务代码里有不合理的锁获取顺序比如两个事务分别先更新表A再更新表B顺序正好相反。这类问题不是调大deadlock_timeout就能解决的反而会掩盖问题应该从应用代码层面统一资源获取顺序。5.4 别把“长连接”当“长事务”最后再强调一个概念区分。pg_stat_activity里backend_start很早的连接比如连接池里的连接可能已经存在好几天了这本身不是问题。一条连接可以执行无数个短事务关键是xact_start所代表的事务开始时间而不是连接建立时间。我见过新手排查时看到连接存活时间2天就慌直接pg_terminate_backend把连接池连接全杀了结果业务雪崩。记住连接存活不等于事务存活真正要盯的是xact_start和state字段。