1. 为什么我一直建议先学会“找慢查询”我在企业里跟 PostgreSQL 打交道这么多年最常被拉过去处理的不是“数据库挂了”而是“系统突然变慢”“某个页面转圈”“一到月底报表就跑不完”。等登录到服务器上一看CPU 没满、内存没爆但数据库里就是有一堆会话卡在那里不动。这时候你要是只会ps -ef | grep postgres基本啥也看不出来。真正的问题往往藏在 SQL 层面某个查询跑了几个小时某个事务一直不提交把别的查询全堵在锁等待里。这三类问题——慢查询、长事务、阻塞查询其实是同一个病根的三张脸必须放在一起排查。这篇文章我不打算讲什么高深调优就聊一个最实际的话题给你一套完整的方法从 PostgreSQL 里把慢查询、长事务、阻塞查询一个一个揪出来搞清楚它们为什么慢、为什么堵、怎么处理。这套方法不需要你装额外的监控软件只要你手上有一个能连数据库的客户端哪怕是 psql 命令行就够了。适合谁看刚接手 PostgreSQL 的 DBA、后端开发、运维同学都适合。我尽量把每条 SQL、每个视图字段都讲透你照着抄就能用。2. 核心思路为什么慢查询、长事务、阻塞查询必须一起查很多新手最容易犯的错就是只盯着“慢查询”这一个指标。慢查询日志开起来看到某条 SQL 跑了 5 秒就急着去优化这条 SQL。结果优化了半天发现这条 SQL 单跑只要 50 毫秒但在线上就是慢。问题出在哪大概率是它被别的会话堵住了。换句话说你看到的“慢查询”可能不是真的慢而是“被阻塞”。所以我的排查思路从来不是单看某一个视图而是先把三件事当成一个整体来看慢查询单条 SQL 执行时间超过阈值。这是结果不是原因。长事务事务长时间不提交或回滚。这是很多问题的源头因为它会拽住一堆旧版本数据不放还占着锁。阻塞查询会话 A 锁住了某行或某张表会话 B 想操作同一行只能干等。这是真正的“堵点”。它们三者之间有一个典型的因果关系链某个长事务先锁住了一行数据然后一堆访问这一行数据的查询全部陷入等待表面上看这些查询都是“慢查询”但实际上它们全是被这个长事务连坐的。所以在排查时我的固定套路是“三步走”先看pg_stat_activity找出所有状态异常的会话。查一下锁等待情况定位到底是谁堵住了谁。最后再回到慢查询本身去看那些真正消耗 CPU、IO 的“重量级选手”。这套顺序很重要。如果你上来就翻慢查询日志很容易被表象误导把精力浪费在一堆“被堵住的冤大头”身上却放过了真正搞事情的那个长事务。3. 第一步实操30 秒内列出当前所有慢查询3.1 第一板斧pg_stat_activity 直接看现场PostgreSQL 里最核心的动态视图就是pg_stat_activity它记录着当前每一个数据库连接的实时状态。我最常用的一条 SQL哪个环境都先跑一遍SELECT pid, usename, datname, application_name, client_addr, state, wait_event_type, wait_event, now() - xact_start AS transaction_duration, now() - query_start AS query_duration, query FROM pg_stat_activity WHERE state idle AND pid pg_backend_pid() ORDER BY query_start ASC;这里我解释几个关键字段因为很多人就是看不懂这些字段才无从下手state会话状态。正常情况下要么是active正在执行要么是idle空闲要么是idle in transaction事务开了但没提交。“idle in transaction”这个状态要特别注意它是长事务的重灾区。query_start当前这条 SQL 开始执行的时间。用now() - query_start就能算出已经跑了多久这就是慢查询的直观证据。xact_start当前事务开始的时间。now() - xact_start是事务的存活时长。如果这个值特别大比如超过了 10 分钟那基本可以断定是一个长事务。wait_event_type和wait_event这个字段太重要了。如果wait_event_type是Lock说明这个会话正在等锁如果是IO说明它在等磁盘读写如果是CPU说明它在拼命计算。这条 SQL 跑出来的结果就是一张“现场全景图”谁在干活、谁在摸鱼、谁在排队等锁一目了然。3.2 第二板斧用慢查询日志抓漏网之鱼pg_stat_activity只能看到“正在执行”的查询但那些已经跑完了、却跑了很久的 SQL它看不见。这时候就得靠慢查询日志。PostgreSQL 的慢查询日志配置其实很简单改几个参数就行。我建议你在配置里把这些设置好log_min_duration_statement 1000 log_line_prefix %t [%p]: [%l-1] user%u,db%d,app%a,client%h log_checkpoints on log_connections on log_disconnections on log_lock_waits on deadlock_timeout 1s重点解释两个参数log_min_duration_statement 1000意思是执行时间超过 1000 毫秒即 1 秒的 SQL 都会被记录到日志里。你可以根据业务实际情况调整有的系统 200 毫秒就算慢有的系统 5 秒才算慢没有绝对标准。log_lock_waits on配合deadlock_timeout 1s如果一条 SQL 等待锁超过了 1 秒就会在日志里额外记一条。这能帮你发现那些“不是慢而是被堵”的查询。改完配置后需要重载不需要重启数据库psql -c ALTER SYSTEM SET log_min_duration_statement 1000; psql -c ALTER SYSTEM SET log_lock_waits on; psql -c ALTER SYSTEM SET deadlock_timeout 1s; psql -c SELECT pg_reload_conf();慢查询日志是排查历史问题的关键证据。有时候业务方反馈“昨晚 8 点系统很卡”你不可能穿越回去看pg_stat_activity但日志里什么都有。3.3 第三板斧启用 pg_stat_statements 看统计前两板斧看的是“当前正在跑的”和“已经跑完的”那“经常跑但单次不算太慢的”怎么抓比如一条 SQL 每次跑 800 毫秒一天跑 10 万次整体消耗巨大但慢查询日志阈值设的是 1 秒它就永远进不了日志。这种场景需要用到pg_stat_statements扩展。它是 PostgreSQL 自带的 SQL 级性能统计模块能记录每条 SQL 的总执行次数、总耗时、平均耗时、缓存命中率等。启用方法很简单CREATE EXTENSION IF NOT EXISTS pg_stat_statements;然后在配置里加上shared_preload_libraries pg_stat_statements注意shared_preload_libraries需要重启数据库实例才生效。这也是为什么我建议你在装 PostgreSQL 的时候就顺手把这个扩展加进去别等出了问题再倒腾。启用之后用这条 SQL 就能找出“累计耗时最长的 Top 10”SELECT calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage, substring(query, 1, 80) AS query_preview FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;pg_stat_statements是我日常优化 SQL 最依赖的工具没有之一。它把“哪些 SQL 最该优化”这个问题直接变成了排名表省去了大量靠猜的时间。4. 第二步实操揪出长事务和“IDLE in transaction”陷阱4.1 长事务为什么可怕PostgreSQL 的 MVCC多版本并发控制机制决定了一个事实一个事务开启得越久它看到的“旧版本”数据就越多而清理这些旧版本数据的VACUUM进程就没法清理它们被这个事务“钉住”的部分。打个比方一个长事务就像你在一间仓库里占了一个货架说一会儿就搬走结果一占占了一整天。别人想往这个货架上放新货清理旧版本只能等着。实际后果就是表膨胀、磁盘占用飙升、查询性能越来越差、VACUUM跑不动。很多 PostgreSQL 实例跑着跑着磁盘满了罪魁祸首不是数据量真的涨了而是有个长事务卡在那里导致一堆死元组dead tuples清不掉。所以排查长事务是 PostgreSQL 运维里优先级非常高的一件事。4.2 找出所有长事务的专用 SQL我最常用的长事务查询语句是这样的SELECT pid, usename, datname, state, now() - xact_start AS xact_age, now() - query_start AS query_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now() - xact_start interval 5 minutes ORDER BY xact_start ASC;这里有两个容易踩的坑我特意说明一下坑一忘了查idle in transaction。很多事务跑完 SQL 后不提交也不回滚就挂在那里。这种会话的state不是active而是idle in transaction。它虽然没有在跑任何 SQL但事务的锁和版本信息全都还在危害一点不比长查询小。我见过最夸张的一次有个连接开了一天事务没提交差点把磁盘塞爆。坑二把query_start和xact_start搞混。判断“长事务”要看xact_start判断“慢查询”要看query_start。一条 SQL 跑了 30 秒事务可能是刚开的一个事务开了 3 小时但最后一条 SQL 只跑了 1 毫秒。两者的处理方式完全不同一个要优化 SQL一个要杀掉会话。4.3 处理长事务的正确姿势发现长事务之后很多人第一反应是直接pg_terminate_backend(pid)杀会话。这个操作不是不行但要分清轻重缓急。如果这个事务正在跑一个关键业务逻辑粗暴杀掉可能导致部分数据未提交业务侧要处理回滚或重试。我的建议是分三步走先发信号让应用侧自己处理。通过pg_cancel_backend(pid)取消当前查询但不终止整个会话。如果应用有完善的错误处理会自动回滚事务这是最温和的方式。如果应用侧没法响应或者事务卡死没有进展再评估使用pg_terminate_backend(pid)强制终止。杀掉之后立刻检查pg_stat_activity里是否还有残留会话同时看一眼表的膨胀情况必要时手动执行VACUUM。我自己在实操中看到一个事务超过 30 分钟还没结束基本就先标记为“高危”通知业务方评估是否可以终止。这里有一个我个人的经验判断不一定适用所有场景但你可以参考OLTP 系统里单个事务超过 5 分钟就要上监控告警超过 30 分钟应该启动人工介入流程。5. 第三步实操定位阻塞查询与锁等待链5.1 PostgreSQL 的锁机制和等待链PostgreSQL 的锁分为很多级别常见的包括行级锁Row-level Lock和表级锁Table-level Lock。行级锁由普通 DMLINSERT/UPDATE/DELETE触发表级锁由 DDLALTER TABLE、TRUNCATE 等触发。阻塞查询的本质就是一个事务持有某行或某表的锁另一个事务想获取同一行或同一表的冲突锁于是后者进入锁等待状态。要理解冲突关系可以记住一个简化规则两个事务都只做SELECT不冲突都能并行跑。一个事务UPDATE某一行另一个事务也UPDATE同一行冲突后者必须等前者事务结束。一个事务ALTER TABLE锁整张表另一个事务想SELECT或UPDATE冲突DML 必须等 DDL 结束。还有一种更隐蔽的“锁升级”问题批量 UPDATE 大量数据时事务先获取了第一行锁然后慢慢一行行更新后面还想更新更多的行另一个事务恰好想更新其中某一行两边互相等对方持有的锁就可能形成死锁。PostgreSQL 有死锁检测机制会自动牺牲其中一个事务但牺牲的那一个会收到报错业务侧如果没处理好就会抛异常。5.2 一条 SQL 看清所有锁等待我常用的锁等待查询是这样写的SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, blocking.state AS blocking_state, now() - blocking.xact_start AS blocking_xact_age FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocking ON blocked.pid blocking.pid WHERE blocked.wait_event_type Lock ORDER BY blocking_xact_age DESC;这条 SQL 的思路很简单遍历所有正在等锁的会话wait_event_type Lock然后找到哪个会话持有了它所需的锁。有时候会出现一条很长的“锁等待链”A 等 BB 等 CC 等 A。这时候单靠这条 SQL 一眼看不出来但我有个土办法把查询结果里每个会话的查询语句打出来一条条梳理。通常阻塞链的源头都是某个长事务或者没提交的 DDL 操作。5.3 检查表锁和未被索引命中的风险除了等锁的会话还应该定期检查那些“安静但危险”的表锁。一个很典型的场景开发同学在测试环境跑了一下ALTER TABLE ... ADD COLUMN结果忘记提交事务连接也没关一直挂着。生产环境同表的其他会话全部卡死。这时候pg_stat_activity里看起来全是正常查询在跑但全都卡在Lock等待上。想直接看哪些表被锁住了可以用这条SELECT l.locktype, l.relation::regclass AS table_name, l.mode, l.pid, a.usename, a.state, a.query FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid a.pid WHERE l.locktype relation AND l.relation IS NOT NULL ORDER BY l.relation;另外我还想提醒一个新手容易踩的坑UPDATE 大量行但 WHERE 条件没有走索引会造成行锁风暴。比如执行UPDATE users SET status 0 WHERE created_at 2020-01-01如果created_at字段没有索引PostgreSQL 只能全表扫描逐行判断、逐行加锁可能短时间内把表里一大半的行都锁住。此时任何针对这些行的操作全被堵死表现就是系统“假死”。这类问题的排查思路仍然是通过pg_stat_activity看wait_event是不是Lock。6. 现场实录一次典型数据库“卡死”的完整排查过程前面讲了工具和原理可能还是有点抽象。我拿一个实际案例来串一遍这个案例我做过不止一次非常有代表性你可以把它当成一个排查模板来参考。6.1 现象某天下午业务方反馈“后台订单列表打不开了一直转圈。”登录监控平台看数据库 CPU 占用不高内存正常但连接数快要打满了。6.2 第一步看现场我先跑了一遍pg_stat_activity的查询结果如下有 20 多个会话处于active状态wait_event_type全部是Lock。这些会话的query几乎都是同一个模式SELECT * FROM orders WHERE order_no $1。只有 1 个会话是idle in transaction状态而且它的xact_start已经是一个小时之前。看到这里思路已经很清晰了那 1 个idle in transaction的会话大概率就是万物之源。6.3 第二步找锁源头我又跑了锁等待链的查询结果定位到那个idle in transaction的会话就是blocking_pid。它的最后一条查询是UPDATE orders SET status shipped WHERE order_no SO123456;这条 UPDATE 执行完了但事务一直没提交。于是那一行的排他锁就一直在它手里攥着。而订单列表页面要按订单号查询虽然走的是索引但需要读取到的行恰好包括这一行于是全部卡住。6.4 第三步确认影响范围再动手我先确认了那个会话属于哪个应用通过application_name和client_addr判断是后台管理系统的一个连接。然后我联系业务方确认这个事务对应的订单更新操作是否已经完成业务方回复说这张订单早就处理完了。于是我执行了SELECT pg_cancel_backend(阻塞会话PID);注意这里我先用的pg_cancel_backend而不是pg_terminate_backend。因为这个事务已经没有正在执行的查询了pg_cancel_backend实际上不会生效。所以这一步做完发现没反应我直接升级为SELECT pg_terminate_backend(阻塞会话PID);事务被强制终止那行的锁立刻释放。20 多个被堵的查询几乎在同一瞬间全部恢复了执行页面秒开。6.5 事后复盘与长期方案这个案例里最值得反思的一点是问题不是出现在查询慢而是出现在一个业务代码里忘了提交事务。那行 UPDATE 早就执行完了但应用层把事务对象挂在那边一直没 commit连接池里的这个连接就一直是“idle in transaction”状态。事后我做了三件事给应用层代码加上了事务超时控制确保任何事务在指定时间内必须结束。在 PostgreSQL 侧设置了idle_in_transaction_session_timeout让超过阈值的空闲事务自动断开。建立了一个定时巡检脚本每 5 分钟执行一次长事务扫描发现超阈值就告警。这里也顺便说一下idle_in_transaction_session_timeout的配置这个参数对很多团队来说是个保命符idle_in_transaction_session_timeout 10min设置了这个参数之后任何事务处于 “空闲但未提交” 状态超过 10 分钟都会被 PostgreSQL 自动终止并记录日志。代价是业务侧可能会收到一个连接断开的报错但总比整个数据库被拖垮要好得多。7. 常见问题与排查技巧速查表我整理了一张速查表把日常最常遇到的几类问题现象、判断方法、处置动作放在一起方便你直接对照使用。这张表是我这几年排查问题的经验浓缩值得你收藏一份。现象核心判断字段常见原因处置动作查询执行很久但wait_event为空wait_event_type CPU或IOSQL 写得太烂缺索引或者统计信息过期分析执行计划补索引执行ANALYZE查询卡住wait_event_type Lockpidwait_event其他会话持有行锁或表锁用锁等待链 SQL 定位阻塞源头评估终止阻塞会话会话显示idle in transaction且时间很久statexact_start应用代码忘了 commit/rollback终止会话同时设置idle_in_transaction_session_timeout连接数打满但单条 SQL 都不算特别慢pg_stat_activity里的会话数量连接池配置过大或存在阻塞导致连接占着不放排查锁等待优化连接池大小表的体积异常膨胀pg_stat_user_tables.n_dead_tup长事务阻碍 VACUUM 清理终止长事务手动执行VACUUM日志里出现大量deadlock detectedPostgreSQL 日志文件多个事务以不同顺序更新同一组行调整应用加锁顺序或优化事务逻辑SQL 单跑快线上总是慢pg_stat_activity的wait_event被其他事务的锁或 IO 波动影响先查锁等待再查主机层的 IO 指标7.1 关于pg_cancel_backend和pg_terminate_backend的选择很多初学者搞不清这两个函数的区别我在实操中总结出几个原则pg_cancel_backend(pid)是“温和取消”它只取消当前正在执行的查询但保留会话和事务。事务内的已执行修改操作不会自动回滚应用可以自行处理。pg_terminate_backend(pid)是“强制断开”直接杀掉整个后端进程当前事务会回滚所有该会话持有的锁都会释放。我的选择逻辑很简单如果这个会话正在跑一条巨大的查询但事务还没写太多数据先尝试 cancel如果这个会话处于idle in transactioncancel 根本没意义直接 terminate。还有一种情况是事务还没提交但已经写入了大量数据杀掉了会回滚很久甚至可能比原来卡住还久这时候就更需要谨慎最好先让应用侧主动提交或回滚。7.2 一次搞定多会话终止的小技巧有时候阻塞源头不止一个会话比如连接池里每个连接都开了一个长事务此时你需要批量终止。我写过一个快速脚本把空闲超过 15 分钟且处于idle in transaction的会话一次性终止SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state idle in transaction AND now() - xact_start interval 15 minutes AND pid pg_backend_pid();这个脚本执行前要三思确认这些会话都是可以放弃的连接比如定时任务遗留的、无人使用的。如果是业务高峰期最好先通知开发同学。7.3 不要忽视query字段里的“隐藏信息”在pg_stat_activity里query字段显示的是当前正在执行的语句原文。我曾经通过这个字段抓到一个非常隐蔽的问题某个应用每隔几秒就会执行一次SELECT 1来做连接保活但因为连接池配置问题这个简单查询也会被阻塞在锁等待上。这种“心跳查询都被堵住”的现象恰恰说明数据库层面的阻塞已经严重到了一定程度。看到这种情况不要急着去分析为什么SELECT 1会慢而是立刻锁定向阻塞链的源头。7.4 关于索引和统计信息的两个检查项最后聊聊慢查询里占比最高的“真·慢查询”——也就是没有被锁影响、单纯就是 SQL 执行慢的情况。遇到这种我的固定套路是在 psql 里看执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_no SO123456;重点看三块Seq Scan 是否出现在了不该出现的表上如果一张 100 万行的表WHERE 条件明明可以走索引却选择了全表扫描大概率是统计信息过旧或者缺索引。先执行ANALYZE orders;刷新统计信息再重新看计划。行数估算偏差EXPLAIN ANALYZE输出的rows估算值和actual rows如果差了数量级说明统计信息不准可能需要调整default_statistics_target。排序和哈希操作如果看到Sort或Hash Join消耗了大量时间思考是否可以通过索引避免排序或者调整work_mem让排序在内存里完成。work_mem的设置是另一个常见坑。默认值只有 4MB这意味着很多排序、哈希操作会被迫落到临时文件里磁盘 IO 一慢查询自然就慢。我在线上环境一般会调到 16MB 到 64MB 之间但也不能盲目调太大因为这是“每个会话、每次排序”都会占用的内存。你开 100 个连接每个连接都做 64MB 的排序就是 6.4GB 内存服务器分分钟被吃满。8. 我自己的一点实际体会这套排查方法我用过很多次从几十人的小团队到几千台服务器的场景都验证过。最想跟你强调的一点是排查慢查询这件事千万别等到线上出事了才开始做准备。PostgreSQL 把pg_stat_activity、pg_stat_statements、慢查询日志这些工具都给你备好了关键是你得提前把该开的配置开好该建的视图建好该设的超时设好。否则真出问题的时候你连现场都没法还原。我个人现在每接手一个新的 PostgreSQL 实例第一件事就是检查三样东西pg_stat_statements有没有启用、idle_in_transaction_session_timeout有没有设置、慢查询日志有没有开。这三件事做完等于给数据库上了一道最基本的保险。后面再配合定期的巡检脚本绝大多数慢查询和阻塞问题都能在萌芽阶段被发现。另外还想分享一个来自实战的体会很多“数据库慢”的问题根子并不在数据库而在应用代码。一个忘了提交的事务、一个没走索引的字段、一个锁顺序不一致的业务逻辑都会在数据库层面被放大成“慢查询”。所以排查的时候别只盯着 SQL多跟开发同事聊聊业务逻辑往往能少走很多弯路。排查慢查询、长事务、阻塞查询之所以要放在一篇文章里讲就是因为在真实环境里它们极少单独出现。掌握这套组合拳之后我遇到数据库变慢通常几分钟内就能给出结论是 SQL 该优化了还是某个事务在堵路或者干脆是配置不合理。这个能力带来的好处远超你省下的那几分钟它让你在团队里真正有点“数据库救火队员”的样子。
PostgreSQL慢查询、长事务与阻塞查询：三步排查数据库卡死
1. 为什么我一直建议先学会“找慢查询”我在企业里跟 PostgreSQL 打交道这么多年最常被拉过去处理的不是“数据库挂了”而是“系统突然变慢”“某个页面转圈”“一到月底报表就跑不完”。等登录到服务器上一看CPU 没满、内存没爆但数据库里就是有一堆会话卡在那里不动。这时候你要是只会ps -ef | grep postgres基本啥也看不出来。真正的问题往往藏在 SQL 层面某个查询跑了几个小时某个事务一直不提交把别的查询全堵在锁等待里。这三类问题——慢查询、长事务、阻塞查询其实是同一个病根的三张脸必须放在一起排查。这篇文章我不打算讲什么高深调优就聊一个最实际的话题给你一套完整的方法从 PostgreSQL 里把慢查询、长事务、阻塞查询一个一个揪出来搞清楚它们为什么慢、为什么堵、怎么处理。这套方法不需要你装额外的监控软件只要你手上有一个能连数据库的客户端哪怕是 psql 命令行就够了。适合谁看刚接手 PostgreSQL 的 DBA、后端开发、运维同学都适合。我尽量把每条 SQL、每个视图字段都讲透你照着抄就能用。2. 核心思路为什么慢查询、长事务、阻塞查询必须一起查很多新手最容易犯的错就是只盯着“慢查询”这一个指标。慢查询日志开起来看到某条 SQL 跑了 5 秒就急着去优化这条 SQL。结果优化了半天发现这条 SQL 单跑只要 50 毫秒但在线上就是慢。问题出在哪大概率是它被别的会话堵住了。换句话说你看到的“慢查询”可能不是真的慢而是“被阻塞”。所以我的排查思路从来不是单看某一个视图而是先把三件事当成一个整体来看慢查询单条 SQL 执行时间超过阈值。这是结果不是原因。长事务事务长时间不提交或回滚。这是很多问题的源头因为它会拽住一堆旧版本数据不放还占着锁。阻塞查询会话 A 锁住了某行或某张表会话 B 想操作同一行只能干等。这是真正的“堵点”。它们三者之间有一个典型的因果关系链某个长事务先锁住了一行数据然后一堆访问这一行数据的查询全部陷入等待表面上看这些查询都是“慢查询”但实际上它们全是被这个长事务连坐的。所以在排查时我的固定套路是“三步走”先看pg_stat_activity找出所有状态异常的会话。查一下锁等待情况定位到底是谁堵住了谁。最后再回到慢查询本身去看那些真正消耗 CPU、IO 的“重量级选手”。这套顺序很重要。如果你上来就翻慢查询日志很容易被表象误导把精力浪费在一堆“被堵住的冤大头”身上却放过了真正搞事情的那个长事务。3. 第一步实操30 秒内列出当前所有慢查询3.1 第一板斧pg_stat_activity 直接看现场PostgreSQL 里最核心的动态视图就是pg_stat_activity它记录着当前每一个数据库连接的实时状态。我最常用的一条 SQL哪个环境都先跑一遍SELECT pid, usename, datname, application_name, client_addr, state, wait_event_type, wait_event, now() - xact_start AS transaction_duration, now() - query_start AS query_duration, query FROM pg_stat_activity WHERE state idle AND pid pg_backend_pid() ORDER BY query_start ASC;这里我解释几个关键字段因为很多人就是看不懂这些字段才无从下手state会话状态。正常情况下要么是active正在执行要么是idle空闲要么是idle in transaction事务开了但没提交。“idle in transaction”这个状态要特别注意它是长事务的重灾区。query_start当前这条 SQL 开始执行的时间。用now() - query_start就能算出已经跑了多久这就是慢查询的直观证据。xact_start当前事务开始的时间。now() - xact_start是事务的存活时长。如果这个值特别大比如超过了 10 分钟那基本可以断定是一个长事务。wait_event_type和wait_event这个字段太重要了。如果wait_event_type是Lock说明这个会话正在等锁如果是IO说明它在等磁盘读写如果是CPU说明它在拼命计算。这条 SQL 跑出来的结果就是一张“现场全景图”谁在干活、谁在摸鱼、谁在排队等锁一目了然。3.2 第二板斧用慢查询日志抓漏网之鱼pg_stat_activity只能看到“正在执行”的查询但那些已经跑完了、却跑了很久的 SQL它看不见。这时候就得靠慢查询日志。PostgreSQL 的慢查询日志配置其实很简单改几个参数就行。我建议你在配置里把这些设置好log_min_duration_statement 1000 log_line_prefix %t [%p]: [%l-1] user%u,db%d,app%a,client%h log_checkpoints on log_connections on log_disconnections on log_lock_waits on deadlock_timeout 1s重点解释两个参数log_min_duration_statement 1000意思是执行时间超过 1000 毫秒即 1 秒的 SQL 都会被记录到日志里。你可以根据业务实际情况调整有的系统 200 毫秒就算慢有的系统 5 秒才算慢没有绝对标准。log_lock_waits on配合deadlock_timeout 1s如果一条 SQL 等待锁超过了 1 秒就会在日志里额外记一条。这能帮你发现那些“不是慢而是被堵”的查询。改完配置后需要重载不需要重启数据库psql -c ALTER SYSTEM SET log_min_duration_statement 1000; psql -c ALTER SYSTEM SET log_lock_waits on; psql -c ALTER SYSTEM SET deadlock_timeout 1s; psql -c SELECT pg_reload_conf();慢查询日志是排查历史问题的关键证据。有时候业务方反馈“昨晚 8 点系统很卡”你不可能穿越回去看pg_stat_activity但日志里什么都有。3.3 第三板斧启用 pg_stat_statements 看统计前两板斧看的是“当前正在跑的”和“已经跑完的”那“经常跑但单次不算太慢的”怎么抓比如一条 SQL 每次跑 800 毫秒一天跑 10 万次整体消耗巨大但慢查询日志阈值设的是 1 秒它就永远进不了日志。这种场景需要用到pg_stat_statements扩展。它是 PostgreSQL 自带的 SQL 级性能统计模块能记录每条 SQL 的总执行次数、总耗时、平均耗时、缓存命中率等。启用方法很简单CREATE EXTENSION IF NOT EXISTS pg_stat_statements;然后在配置里加上shared_preload_libraries pg_stat_statements注意shared_preload_libraries需要重启数据库实例才生效。这也是为什么我建议你在装 PostgreSQL 的时候就顺手把这个扩展加进去别等出了问题再倒腾。启用之后用这条 SQL 就能找出“累计耗时最长的 Top 10”SELECT calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage, substring(query, 1, 80) AS query_preview FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;pg_stat_statements是我日常优化 SQL 最依赖的工具没有之一。它把“哪些 SQL 最该优化”这个问题直接变成了排名表省去了大量靠猜的时间。4. 第二步实操揪出长事务和“IDLE in transaction”陷阱4.1 长事务为什么可怕PostgreSQL 的 MVCC多版本并发控制机制决定了一个事实一个事务开启得越久它看到的“旧版本”数据就越多而清理这些旧版本数据的VACUUM进程就没法清理它们被这个事务“钉住”的部分。打个比方一个长事务就像你在一间仓库里占了一个货架说一会儿就搬走结果一占占了一整天。别人想往这个货架上放新货清理旧版本只能等着。实际后果就是表膨胀、磁盘占用飙升、查询性能越来越差、VACUUM跑不动。很多 PostgreSQL 实例跑着跑着磁盘满了罪魁祸首不是数据量真的涨了而是有个长事务卡在那里导致一堆死元组dead tuples清不掉。所以排查长事务是 PostgreSQL 运维里优先级非常高的一件事。4.2 找出所有长事务的专用 SQL我最常用的长事务查询语句是这样的SELECT pid, usename, datname, state, now() - xact_start AS xact_age, now() - query_start AS query_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now() - xact_start interval 5 minutes ORDER BY xact_start ASC;这里有两个容易踩的坑我特意说明一下坑一忘了查idle in transaction。很多事务跑完 SQL 后不提交也不回滚就挂在那里。这种会话的state不是active而是idle in transaction。它虽然没有在跑任何 SQL但事务的锁和版本信息全都还在危害一点不比长查询小。我见过最夸张的一次有个连接开了一天事务没提交差点把磁盘塞爆。坑二把query_start和xact_start搞混。判断“长事务”要看xact_start判断“慢查询”要看query_start。一条 SQL 跑了 30 秒事务可能是刚开的一个事务开了 3 小时但最后一条 SQL 只跑了 1 毫秒。两者的处理方式完全不同一个要优化 SQL一个要杀掉会话。4.3 处理长事务的正确姿势发现长事务之后很多人第一反应是直接pg_terminate_backend(pid)杀会话。这个操作不是不行但要分清轻重缓急。如果这个事务正在跑一个关键业务逻辑粗暴杀掉可能导致部分数据未提交业务侧要处理回滚或重试。我的建议是分三步走先发信号让应用侧自己处理。通过pg_cancel_backend(pid)取消当前查询但不终止整个会话。如果应用有完善的错误处理会自动回滚事务这是最温和的方式。如果应用侧没法响应或者事务卡死没有进展再评估使用pg_terminate_backend(pid)强制终止。杀掉之后立刻检查pg_stat_activity里是否还有残留会话同时看一眼表的膨胀情况必要时手动执行VACUUM。我自己在实操中看到一个事务超过 30 分钟还没结束基本就先标记为“高危”通知业务方评估是否可以终止。这里有一个我个人的经验判断不一定适用所有场景但你可以参考OLTP 系统里单个事务超过 5 分钟就要上监控告警超过 30 分钟应该启动人工介入流程。5. 第三步实操定位阻塞查询与锁等待链5.1 PostgreSQL 的锁机制和等待链PostgreSQL 的锁分为很多级别常见的包括行级锁Row-level Lock和表级锁Table-level Lock。行级锁由普通 DMLINSERT/UPDATE/DELETE触发表级锁由 DDLALTER TABLE、TRUNCATE 等触发。阻塞查询的本质就是一个事务持有某行或某表的锁另一个事务想获取同一行或同一表的冲突锁于是后者进入锁等待状态。要理解冲突关系可以记住一个简化规则两个事务都只做SELECT不冲突都能并行跑。一个事务UPDATE某一行另一个事务也UPDATE同一行冲突后者必须等前者事务结束。一个事务ALTER TABLE锁整张表另一个事务想SELECT或UPDATE冲突DML 必须等 DDL 结束。还有一种更隐蔽的“锁升级”问题批量 UPDATE 大量数据时事务先获取了第一行锁然后慢慢一行行更新后面还想更新更多的行另一个事务恰好想更新其中某一行两边互相等对方持有的锁就可能形成死锁。PostgreSQL 有死锁检测机制会自动牺牲其中一个事务但牺牲的那一个会收到报错业务侧如果没处理好就会抛异常。5.2 一条 SQL 看清所有锁等待我常用的锁等待查询是这样写的SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, blocking.state AS blocking_state, now() - blocking.xact_start AS blocking_xact_age FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocking ON blocked.pid blocking.pid WHERE blocked.wait_event_type Lock ORDER BY blocking_xact_age DESC;这条 SQL 的思路很简单遍历所有正在等锁的会话wait_event_type Lock然后找到哪个会话持有了它所需的锁。有时候会出现一条很长的“锁等待链”A 等 BB 等 CC 等 A。这时候单靠这条 SQL 一眼看不出来但我有个土办法把查询结果里每个会话的查询语句打出来一条条梳理。通常阻塞链的源头都是某个长事务或者没提交的 DDL 操作。5.3 检查表锁和未被索引命中的风险除了等锁的会话还应该定期检查那些“安静但危险”的表锁。一个很典型的场景开发同学在测试环境跑了一下ALTER TABLE ... ADD COLUMN结果忘记提交事务连接也没关一直挂着。生产环境同表的其他会话全部卡死。这时候pg_stat_activity里看起来全是正常查询在跑但全都卡在Lock等待上。想直接看哪些表被锁住了可以用这条SELECT l.locktype, l.relation::regclass AS table_name, l.mode, l.pid, a.usename, a.state, a.query FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid a.pid WHERE l.locktype relation AND l.relation IS NOT NULL ORDER BY l.relation;另外我还想提醒一个新手容易踩的坑UPDATE 大量行但 WHERE 条件没有走索引会造成行锁风暴。比如执行UPDATE users SET status 0 WHERE created_at 2020-01-01如果created_at字段没有索引PostgreSQL 只能全表扫描逐行判断、逐行加锁可能短时间内把表里一大半的行都锁住。此时任何针对这些行的操作全被堵死表现就是系统“假死”。这类问题的排查思路仍然是通过pg_stat_activity看wait_event是不是Lock。6. 现场实录一次典型数据库“卡死”的完整排查过程前面讲了工具和原理可能还是有点抽象。我拿一个实际案例来串一遍这个案例我做过不止一次非常有代表性你可以把它当成一个排查模板来参考。6.1 现象某天下午业务方反馈“后台订单列表打不开了一直转圈。”登录监控平台看数据库 CPU 占用不高内存正常但连接数快要打满了。6.2 第一步看现场我先跑了一遍pg_stat_activity的查询结果如下有 20 多个会话处于active状态wait_event_type全部是Lock。这些会话的query几乎都是同一个模式SELECT * FROM orders WHERE order_no $1。只有 1 个会话是idle in transaction状态而且它的xact_start已经是一个小时之前。看到这里思路已经很清晰了那 1 个idle in transaction的会话大概率就是万物之源。6.3 第二步找锁源头我又跑了锁等待链的查询结果定位到那个idle in transaction的会话就是blocking_pid。它的最后一条查询是UPDATE orders SET status shipped WHERE order_no SO123456;这条 UPDATE 执行完了但事务一直没提交。于是那一行的排他锁就一直在它手里攥着。而订单列表页面要按订单号查询虽然走的是索引但需要读取到的行恰好包括这一行于是全部卡住。6.4 第三步确认影响范围再动手我先确认了那个会话属于哪个应用通过application_name和client_addr判断是后台管理系统的一个连接。然后我联系业务方确认这个事务对应的订单更新操作是否已经完成业务方回复说这张订单早就处理完了。于是我执行了SELECT pg_cancel_backend(阻塞会话PID);注意这里我先用的pg_cancel_backend而不是pg_terminate_backend。因为这个事务已经没有正在执行的查询了pg_cancel_backend实际上不会生效。所以这一步做完发现没反应我直接升级为SELECT pg_terminate_backend(阻塞会话PID);事务被强制终止那行的锁立刻释放。20 多个被堵的查询几乎在同一瞬间全部恢复了执行页面秒开。6.5 事后复盘与长期方案这个案例里最值得反思的一点是问题不是出现在查询慢而是出现在一个业务代码里忘了提交事务。那行 UPDATE 早就执行完了但应用层把事务对象挂在那边一直没 commit连接池里的这个连接就一直是“idle in transaction”状态。事后我做了三件事给应用层代码加上了事务超时控制确保任何事务在指定时间内必须结束。在 PostgreSQL 侧设置了idle_in_transaction_session_timeout让超过阈值的空闲事务自动断开。建立了一个定时巡检脚本每 5 分钟执行一次长事务扫描发现超阈值就告警。这里也顺便说一下idle_in_transaction_session_timeout的配置这个参数对很多团队来说是个保命符idle_in_transaction_session_timeout 10min设置了这个参数之后任何事务处于 “空闲但未提交” 状态超过 10 分钟都会被 PostgreSQL 自动终止并记录日志。代价是业务侧可能会收到一个连接断开的报错但总比整个数据库被拖垮要好得多。7. 常见问题与排查技巧速查表我整理了一张速查表把日常最常遇到的几类问题现象、判断方法、处置动作放在一起方便你直接对照使用。这张表是我这几年排查问题的经验浓缩值得你收藏一份。现象核心判断字段常见原因处置动作查询执行很久但wait_event为空wait_event_type CPU或IOSQL 写得太烂缺索引或者统计信息过期分析执行计划补索引执行ANALYZE查询卡住wait_event_type Lockpidwait_event其他会话持有行锁或表锁用锁等待链 SQL 定位阻塞源头评估终止阻塞会话会话显示idle in transaction且时间很久statexact_start应用代码忘了 commit/rollback终止会话同时设置idle_in_transaction_session_timeout连接数打满但单条 SQL 都不算特别慢pg_stat_activity里的会话数量连接池配置过大或存在阻塞导致连接占着不放排查锁等待优化连接池大小表的体积异常膨胀pg_stat_user_tables.n_dead_tup长事务阻碍 VACUUM 清理终止长事务手动执行VACUUM日志里出现大量deadlock detectedPostgreSQL 日志文件多个事务以不同顺序更新同一组行调整应用加锁顺序或优化事务逻辑SQL 单跑快线上总是慢pg_stat_activity的wait_event被其他事务的锁或 IO 波动影响先查锁等待再查主机层的 IO 指标7.1 关于pg_cancel_backend和pg_terminate_backend的选择很多初学者搞不清这两个函数的区别我在实操中总结出几个原则pg_cancel_backend(pid)是“温和取消”它只取消当前正在执行的查询但保留会话和事务。事务内的已执行修改操作不会自动回滚应用可以自行处理。pg_terminate_backend(pid)是“强制断开”直接杀掉整个后端进程当前事务会回滚所有该会话持有的锁都会释放。我的选择逻辑很简单如果这个会话正在跑一条巨大的查询但事务还没写太多数据先尝试 cancel如果这个会话处于idle in transactioncancel 根本没意义直接 terminate。还有一种情况是事务还没提交但已经写入了大量数据杀掉了会回滚很久甚至可能比原来卡住还久这时候就更需要谨慎最好先让应用侧主动提交或回滚。7.2 一次搞定多会话终止的小技巧有时候阻塞源头不止一个会话比如连接池里每个连接都开了一个长事务此时你需要批量终止。我写过一个快速脚本把空闲超过 15 分钟且处于idle in transaction的会话一次性终止SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state idle in transaction AND now() - xact_start interval 15 minutes AND pid pg_backend_pid();这个脚本执行前要三思确认这些会话都是可以放弃的连接比如定时任务遗留的、无人使用的。如果是业务高峰期最好先通知开发同学。7.3 不要忽视query字段里的“隐藏信息”在pg_stat_activity里query字段显示的是当前正在执行的语句原文。我曾经通过这个字段抓到一个非常隐蔽的问题某个应用每隔几秒就会执行一次SELECT 1来做连接保活但因为连接池配置问题这个简单查询也会被阻塞在锁等待上。这种“心跳查询都被堵住”的现象恰恰说明数据库层面的阻塞已经严重到了一定程度。看到这种情况不要急着去分析为什么SELECT 1会慢而是立刻锁定向阻塞链的源头。7.4 关于索引和统计信息的两个检查项最后聊聊慢查询里占比最高的“真·慢查询”——也就是没有被锁影响、单纯就是 SQL 执行慢的情况。遇到这种我的固定套路是在 psql 里看执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_no SO123456;重点看三块Seq Scan 是否出现在了不该出现的表上如果一张 100 万行的表WHERE 条件明明可以走索引却选择了全表扫描大概率是统计信息过旧或者缺索引。先执行ANALYZE orders;刷新统计信息再重新看计划。行数估算偏差EXPLAIN ANALYZE输出的rows估算值和actual rows如果差了数量级说明统计信息不准可能需要调整default_statistics_target。排序和哈希操作如果看到Sort或Hash Join消耗了大量时间思考是否可以通过索引避免排序或者调整work_mem让排序在内存里完成。work_mem的设置是另一个常见坑。默认值只有 4MB这意味着很多排序、哈希操作会被迫落到临时文件里磁盘 IO 一慢查询自然就慢。我在线上环境一般会调到 16MB 到 64MB 之间但也不能盲目调太大因为这是“每个会话、每次排序”都会占用的内存。你开 100 个连接每个连接都做 64MB 的排序就是 6.4GB 内存服务器分分钟被吃满。8. 我自己的一点实际体会这套排查方法我用过很多次从几十人的小团队到几千台服务器的场景都验证过。最想跟你强调的一点是排查慢查询这件事千万别等到线上出事了才开始做准备。PostgreSQL 把pg_stat_activity、pg_stat_statements、慢查询日志这些工具都给你备好了关键是你得提前把该开的配置开好该建的视图建好该设的超时设好。否则真出问题的时候你连现场都没法还原。我个人现在每接手一个新的 PostgreSQL 实例第一件事就是检查三样东西pg_stat_statements有没有启用、idle_in_transaction_session_timeout有没有设置、慢查询日志有没有开。这三件事做完等于给数据库上了一道最基本的保险。后面再配合定期的巡检脚本绝大多数慢查询和阻塞问题都能在萌芽阶段被发现。另外还想分享一个来自实战的体会很多“数据库慢”的问题根子并不在数据库而在应用代码。一个忘了提交的事务、一个没走索引的字段、一个锁顺序不一致的业务逻辑都会在数据库层面被放大成“慢查询”。所以排查的时候别只盯着 SQL多跟开发同事聊聊业务逻辑往往能少走很多弯路。排查慢查询、长事务、阻塞查询之所以要放在一篇文章里讲就是因为在真实环境里它们极少单独出现。掌握这套组合拳之后我遇到数据库变慢通常几分钟内就能给出结论是 SQL 该优化了还是某个事务在堵路或者干脆是配置不合理。这个能力带来的好处远超你省下的那几分钟它让你在团队里真正有点“数据库救火队员”的样子。
