PostgreSQL慢COMMIT PREPARED问题的调试与调优方案咨询

问题背景 #

日志中出现大量执行缓慢的COMMIT PREPARED操作，耗时普遍在1000ms以上，部分甚至超过2000ms。这些操作关联的事务大多仅包含SELECT查询。已排查内存、磁盘IO等系统资源，未发现异常；pgbench测试的事务处理率（TPS）表现正常。

相关日志示例 #

2023-10-06 14:35:47 CEST Tx[0] cmd[COMMIT PREPARED] PID[16591] DB[db_XX] LOG: duration: 1049.941 ms execute <unnamed>: COMMIT PREPARED '131077_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdiSzE=_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdiWAAAAAQAAAAA'
2023-10-06 14:35:47 CEST Tx[0] cmd[COMMIT PREPARED] PID[24320] DB[db_XX] LOG: duration: 1049.209 ms execute <unnamed>: COMMIT PREPARED '131077_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdjzzE=_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdj1QAAAAQAAAAA'
2023-10-06 14:35:47 CEST Tx[0] cmd[COMMIT PREPARED] PID[27681] DB[db_XX] LOG: duration: 1048.986 ms execute <unnamed>: COMMIT PREPARED '131077_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdjNDE=_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdjOgAAAAQAAAAA'
2023-10-06 14:35:47 CEST Tx[0] cmd[COMMIT PREPARED] PID[18060] DB[db_XX] LOG: duration: 1188.703 ms execute <unnamed>: COMMIT PREPARED '131077_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdiTTE=_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdiVwAAAAQAAAAA'
2023-10-06 14:35:43 CEST Tx[0] cmd[COMMIT PREPARED] PID[28640] DB[db_XX] LOG: duration: 1033.370 ms execute <unnamed>: COMMIT PREPARED '131077_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdgsjE=_AAAAAAAAAAAAAP//ZGKVXjMhkrRlCESsBTdhHwAAAAQAAAAA'

数据库配置 #

• PostgreSQL 12.6（基于Alpine镜像）
• 12GB内存，1-8核CPU
• shared_buffers = 8GB
• synchronous_commit = 'off'

调试方法 #

1. 追踪PREPARED事务全生命周期 #

• 启用log_statement = 'all'（或至少log_statement = 'mod'），同时设置log_transaction_sample_rate = 1.0，记录这些事务从启动到PREPARE TRANSACTION再到COMMIT PREPARED的完整流程，确认PREPARE与COMMIT PREPARED之间是否存在长时间间隔。
• 查询pg_prepared_xacts视图，查看事务的prepared时间、所属用户和数据库，排查是否有事务长时间处于PREPARED状态未提交，导致后续操作积压。

2. 排查锁等待冲突 #

• 执行以下查询检查锁状态：

  SELECT * FROM pg_locks WHERE locktype IN ('transactionid', 'relation');
  

  重点关注是否有COMMIT PREPARED操作等待事务ID锁或关系锁。
• 结合pg_stat_activity，查看对应PID的wait_event_type和wait_event字段，确认操作是否卡在某个等待事件（如LWLock、IO等）。

3. 启用性能分析工具 #

• 安装并启用pg_stat_statements扩展，查看COMMIT PREPARED语句的调用次数、平均耗时、最大耗时等统计数据，对比正常场景的差异。
• 开启log_lock_waits = on，记录锁等待事件，明确是否存在锁等待导致的延迟。

4. 确认分布式事务场景（若适用） #

• 如果应用使用了XA分布式事务，检查外部节点是否存在延迟，因为COMMIT PREPARED可能需要协调外部资源。但结合事务仅含SELECT的情况，需确认是否误用了分布式事务机制。

调优建议 #

1. 优化PREPARED事务的使用场景 #

• 避免在只读（仅SELECT）事务中使用两阶段提交（PREPARED TRANSACTION），这类事务不需要两阶段提交的保障，属于不必要的性能开销。检查应用代码，确认是否错误地将只读事务标记为需要PREPARE。
• 确保PREPARE TRANSACTION后立即执行COMMIT PREPARED，避免事务长时间处于PREPARED状态，减少元数据管理的开销。

2. 调整数据库配置参数 #

• 检查max_prepared_transactions参数，确保其值足够处理当前负载，建议设置为max_connections的10%-20%，避免因参数过小导致的等待。
• 优化WAL相关配置：虽然synchronous_commit = 'off'，COMMIT PREPARED仍需写入WAL。建议将wal_buffers设置为shared_buffers的1%-2%（或至少16MB），同时合理调整checkpoint_timeout和max_wal_size，减少检查点对性能的影响。

3. 系统层面补充优化 #

• 测试磁盘随机写入性能：使用fio工具验证磁盘fsync效率，即使synchronous_commit = 'off'，部分场景下COMMIT PREPARED仍会触发WAL刷新。
• 监控CPU负载：使用vmstat、top等工具检查是否存在CPU上下文切换过高的情况，高并发下的CPU瓶颈也可能导致操作延迟。

内容的提问来源于stack exchange，提问作者mishka