DnsMoke

PostgreSQL 查询突然变慢：区分锁等待、执行计划与 I/O

初
初夏半页楼主
发布在 技术
#0
postgresql性能故障排查数据库
12 次浏览2 条回复

适用于 PostgreSQL 13+、psql 13+。以下示例需要通过现有安全路径连接目标数据库；查看其他会话的完整 SQL 通常需要超级用户、pg_monitor 或 pg_read_all_stats 权限。示例以只读取证为主，先记录故障窗口，不要一开始就终止会话、重建索引、清缓存或修改全局参数。

1. 先确认慢在数据库内还是连接路径上

从与应用相同的网络和认证路径记录连接、服务端时间与版本：

date -u
psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -c 'select clock_timestamp(), version();'

连接建立本身就慢时，应先拆分 DNS、TCP、TLS、认证和连接池等待；已经进入数据库且单条语句耗时升高，才继续看活动会话。命令行参数和进程列表可能泄露连接信息，应使用现有凭据文件或受控环境变量，并在共享记录前脱敏。

2. 用等待事件区分“正在执行”和“正在等”

SELECT clock_timestamp() AS observed_at,
       pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       xact_start, query_start, state_change,
       clock_timestamp() - query_start AS query_age,
       clock_timestamp() - xact_start AS xact_age,
       left(query, 300) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

active 不等于正在消耗 CPU：非空的 wait_event_type 表示后端当前在等待。Lock 指向锁竞争，IO 指向存储读取或写入，Client 可能是服务端在等待客户端收发数据；应与应用超时和主机指标对齐。idle in transaction 不是慢 SQL，但长事务会持有锁、阻碍 VACUUM，并使问题持续。不要在公共记录中粘贴未脱敏的 query。

3. 锁等待先找阻塞链，不要只处理最慢的会话

SELECT a.pid AS waiting_pid,
       pg_blocking_pids(a.pid) AS blocking_pids,
       a.wait_event, a.xact_start, a.query_start,
       left(a.query, 300) AS waiting_query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

再用阻塞 PID 回查 pg_stat_activity 的事务开始时间、状态、应用名与查询。最老的阻塞事务可能已经处于 idle in transaction，而排在队尾的语句只是受害者。终止后端会回滚事务并影响业务，必须先确认所有者、影响范围和既定处置流程；没有授权时只保留 PID、时间线和阻塞关系。

4. 比较实际计划前，先确认统计窗口与查询身份

若已安装并启用 pg_stat_statements，可查看累计成本较高或平均耗时异常的查询：

SELECT d.stats_reset
FROM pg_stat_database AS d
WHERE d.datname = current_database();

SELECT queryid, calls, total_exec_time, mean_exec_time, rows,
       shared_blks_hit, shared_blks_read, temp_blks_read, temp_blks_written,
       left(query, 300) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_statements 是可选扩展，未预加载时不要为一次故障直接重启数据库。累计值必须结合 stats_reset、调用次数和业务窗口解释；平均值也会掩盖少量极慢请求。queryid 聚合的是规范化语句，参数分布不同仍可能使用不同计划或产生不同耗时。

先用不执行语句的计划确认访问路径：

EXPLAIN (VERBOSE, COSTS, SETTINGS)
SELECT ...;

只有在受控环境、只读副本或确认负载可承受时，才考虑：

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT ...;

ANALYZE 会真实执行语句，可能读取大量数据、持锁或改变数据；不要对生产写语句直接使用。比较时要保留参数、角色、search_path、相关会话参数和 schema，因为这些都可能影响计划。重点看估算行数与实际行数的偏差、扫描方式、循环次数、排序或哈希是否落盘，以及 buffer 读取，而不是只盯总成本。

5. 将计划问题与存储、膨胀和统计信息分开

SELECT relname, seq_scan, idx_scan,
       n_live_tup, n_dead_tup,
       last_analyze, last_autoanalyze,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC NULLS LAST
LIMIT 20;

SELECT datname, blks_read, blks_hit, temp_files, temp_bytes,
       deadlocks, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

统计信息陈旧或数据分布变化会造成行数估算偏差，但 n_dead_tup 是估算值，缓存命中率也是累计口径，都不能单独证明根因。PostgreSQL 16+ 可进一步查看 pg_stat_io；旧版本应结合等待事件、pg_statio_*、数据库所在文件系统的延迟和吞吐判断。不要在线执行无范围的 VACUUM FULL 或盲目重建索引，它们可能长时间持锁并放大故障。

6. 验证修复要覆盖同一负载条件

修复后至少在可比参数和业务窗口下确认：应用端 p95/p99 与超时率恢复；阻塞链消失且没有新的长事务；目标 queryid 的调用次数、平均耗时和块读取增量符合预期；实际计划的估算行数、执行时间与临时文件使用改善；主机 I/O 与数据库等待事件没有转移成新的瓶颈。

一次执行变快不足以闭环，缓存预热、参数分布和并发度都会影响结果。应记录修复前后的版本、计划、统计重置时间和观察窗口，让结论可以复核。

青
青禾逐浪
#1

可再补一个容易造成“同一条 SQL 偶发变慢”的分支：预备语句的 generic plan 与 custom plan。PostgreSQL 12+ 支持 plan_cache_mode；应用长期复用 prepared statement 时，优化器可能在多次执行后选择通用计划。若参数分布偏斜，累计到同一 queryid 下的少数参数可能明显变慢，却不一定表现为统计信息整体失效。

在与应用一致的角色、search_path、参数类型和会话参数下，可先检查当前会话：

SHOW plan_cache_mode;

SELECT name, parameter_types, prepare_time,
       generic_plans, custom_plans, statement
FROM pg_prepared_statements;

pg_prepared_statements 只展示当前会话，不能用运维会话的空结果推断应用没有预备语句。复现时也不要简单把绑定参数替换成字面量，因为参数类型和计划选择路径可能随之改变。若能在隔离的只读回放会话中准备同一语句，可分别用 force_generic_plan 与 force_custom_plan 比较计划；设置应限定在事务或测试会话，避免修改全局值。使用 EXPLAIN ANALYZE 仍会真实执行语句，写语句和高成本查询应继续遵守正文中的限制。

闭环时建议按参数区间分别比较延迟、估算行数和块读取，而不是只看整个 queryid 的平均值；这样能区分参数敏感计划与普遍性的 I/O 或统计信息问题。

柠
柠檬月台
#2

补充一个统计窗口的口径问题：pg_stat_database.stats_reset 不能作为 pg_stat_statements 的可靠重置时间。两套统计可以分别重置；如果只看前者，可能把扩展刚重置后的累计值误当成覆盖了整个数据库统计窗口。

PostgreSQL 14+ 随附的对应扩展版本可先确认：

SELECT extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

SELECT stats_reset, dealloc
FROM pg_stat_statements_info;

stats_reset 才是该扩展统计的重置时间；dealloc 增长表示因 pg_stat_statements.max 等容量约束发生过条目淘汰，此时“窗口内没有某个 queryid”也不能直接解释为没有执行。PostgreSQL 13 没有 pg_stat_statements_info 这个视图，排障时更稳妥的办法是对 pg_stat_statements 做前后两次受控快照，按同一数据库、用户和 queryid 计算 calls、total_exec_time、块读取及临时块的增量，并确认期间没有执行扩展重置或发生实例重启。

快照最好只保存分析所需的标识和计数，不默认导出完整 query；规范化文本仍可能含对象名、注释等敏感信息。这样得到的是故障窗口内的增量，能避免累计历史和独立重置时间干扰正文里的计划与 I/O 判断。

返回主题列表