PostgreSQL 中的 BufferPin 冲突：如何查找和修复

# PostgreSQL 中的 BufferPin 冲突：副本与自动真空的诊断与优化

BufferPin 冲突发生在固定缓冲区时——这是 PostgreSQL 中一个隐蔽但关键的性能问题，尤其在副本上以及页面冻结期间。它们不会出现在标准监控指标中，伪装成“挂起”或“莫名延迟”，需要深入理解 DBMS 内部机制才能诊断。本文将剖析其成因、症状、检测方法，以及实际降低影响的策略。

为什么 BufferPin 冲突危险且难以察觉

主要挑战在于，BufferPin 等待不会记录为 pg_locks 中的锁，也不会总出现在冲突统计中。等待固定释放的进程只是挂起在 wait_event = 'BufferPin' 状态，而不触发警报。这在副本上打击尤为严重：

• startup 进程在重放 WAL 记录（例如 HOT 清理）时需要对缓冲区独占访问——即必须等待 pincount = 0。
• 如果副本上正在执行长运行查询（例如 Seq Scan），它会持有该块的固定。
• Startup 进程完全停滞，直到块被释放。
• 由于 WAL 重放是单线程进行的，一个块的延迟会导致整个副本的延迟积累。

主库和副本上的自动真空在页面冻结（freeze）期间也会出现类似情况。自动真空在正常模式下会跳过已固定的块，但在激进模式（冻结期间）下——它会等待。如果此类块很多，工作进程就会挂起，清理积压积累，最终可能导致事务 ID 环绕。

隐蔽症状：

• 副本延迟无负载增加却持续增长。
• 日志消息如：automatic aggressive vacuum of table... 或 skipped due to pins。
• pg_locks 中无等待可见，但 pg_stat_activity.wait_event 中有 BufferPin。

如何重现与诊断问题

一个简单示例即可理解机制。创建一个表，并使用事务中的游标固定一行：

CREATE TABLE t AS SELECT 1 c;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t;
FETCH c;

现在通过 pg_buffercache 检查缓冲区状态：

select c.relname, bufferid, relforknumber, usagecount, pinning_backends
  from pg_buffercache b join pg_class c on b.relfilenode =
   pg_relation_filenode(c.oid)
   and c.relname = 't';

结果将显示 pinning_backends = 1——我们的成交在持有固定。

在第二个会话中运行激进 VACUUM：

vacuum (freeze, verbose, skip_locked off) t;

VACUUM 将挂起。重复查询 pg_buffercache 将显示 pinning_backends = 2。检查等待：

SELECT query, wait_event FROM pg_stat_activity WHERE query ILIKE 'vacuum%';
-- wait_event = 'BufferPin'

如果你打开更多类似游标的会话，pinning_backends 将持续增长。VACUUM 直到所有事务完成才继续。

实际监控与预防方法

标准工具无法实时显示问题。但你可以基于 pg_buffercache 和 pg_stat_activity 构建有效监控。

检测热点块的基本查询：

SELECT 
  n.nspname AS schema_name,
  c.relname AS table_name,
  b.bufferid,
  b.pinning_backends,
  b.usagecount,
  b.isdirty,
  c.relpages
FROM pg_buffercache b 
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
  AND b.pinning_backends > 1
ORDER BY b.pinning_backends DESC
LIMIT 10;

定期检查内容：

• pinning_backends 高的表——优化首要候选。
• 长运行事务的存在（pg_stat_activity.xact_start）。
• wait_event = 'BufferPin' 的等待进程。

减少冲突的方法：

• 优化 fillfactor——如果表行数少但频繁更新，降低 fillfactor 可将行分散到多个块，减少单个缓冲区的争用。
• 专用副本——无用户查询的副本完全避免固定冲突，确保同步稳定。例如 OpenAI 就是这样做的。
• 调整 max_standby_streaming_delay——增大值给副本查询更多运行时间，但增加延迟风险。
• 限制事务时长——实现超时并自动终止挂起会话。
• 冻结前预清理——低负载期运行手动 VACUUM FREEZE。

深入分析的扩展与替代方案

Alexandra Bondar 的演讲提到一个自定义扩展，用于监控热点块。它使用回调钩子和交替内存写入以最小化开销。然而，此类方案很少被社区接受——它们需要核心修改，且针对极小众问题。

无需核心变更的替代方案：

• pg_wait_sampling——通过采样收集等待统计，包括 BufferPin。
• pg_buffercache + cron——定期收集数据并与先前状态比较。
• 自动真空日志——设置 log_autovacuum_min_duration = 0 捕获所有跳过块情况。

示例日志设置：

ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();

此后，日志将包含如：

LOG:  skipping block N of relation "public.t" --- block is pinned

这即使无活跃监控，也能识别问题表。

关键要点

• BufferPin 冲突是副本延迟增长和自动真空挂起的隐形原因。
• 诊断仅通过 pg_buffercache 和 pg_stat_activity.wait_event 可能。
• fillfactor 和专用副本是最有效减少争用的方式。
• 零阈值自动真空日志有助于生产环境发现问题表。
• 标准指标（pg_stat_database_conflicts）遗漏多数情况——自定义监控必不可少。

— Editorial Team