尧图网页设计 
1. PostgreSQL阻塞查询问题概述在PostgreSQL数据库运维过程中阻塞查询(Blocked Queries)是最常见的性能瓶颈之一。当某个会话持有锁资源而长时间不释放时其他需要相同锁资源的会话就会被阻塞导致系统响应变慢甚至完全卡死。这种情况在OLTP系统中尤为常见特别是在高并发写入场景下。重要提示阻塞不同于死锁(Deadlock)。死锁是PostgreSQL能够自动检测并解决的而阻塞需要DBA人工干预才能解除。我最近处理过一个典型的生产案例某电商平台的订单提交接口在促销期间频繁超时经排查发现是因为库存扣减操作被长时间运行的报表查询阻塞。通过本文介绍的方法我们快速定位并解决了问题。2. 阻塞检测的核心技术2.1 PostgreSQL锁机制基础PostgreSQL采用多版本并发控制(MVCC)机制配合多种锁类型来管理并发访问表级锁ACCESS SHARE最弱、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE最强行级锁FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE咨询锁应用层控制的特殊锁锁冲突矩阵决定了哪些锁可以共存。例如当一个会话持有ROW EXCLUSIVE锁通常由UPDATE语句获取时其他会话尝试获取相同行的FOR UPDATE锁就会被阻塞。2.2 阻塞查询检测方法2.2.1 使用pg_stat_activity视图SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.GRANTED;这个查询会返回所有被阻塞的会话及其阻塞源的关键信息包括被阻塞会话的PID和用户名阻塞会话的PID和用户名被阻塞的SQL语句导致阻塞的SQL语句2.2.2 使用pg_blocking_pids函数PostgreSQL 9.6SELECT pid, usename, query, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;这个更简洁的查询会列出所有被阻塞的会话及其阻塞源的PID。2.3 高级阻塞分析技巧2.3.1 锁等待超时设置-- 设置锁等待超时单位毫秒 SET lock_timeout 5000; -- 5秒后自动取消被阻塞的查询这个参数可以在会话级别设置避免查询无限期等待。2.3.2 查看锁详情SELECT lock.locktype, lock.relation::regclass, lock.mode, lock.virtualtransaction, lock.pid, stat.usename, stat.query, stat.query_start, age(now(), stat.query_start) AS query_age FROM pg_catalog.pg_locks lock JOIN pg_catalog.pg_stat_activity stat ON lock.pid stat.pid WHERE NOT lock.granted;这个查询提供了更详细的锁信息包括锁类型、锁模式、持有时间等。3. 阻塞问题解决方案3.1 即时解决方案3.1.1 终止阻塞会话-- 谨慎操作先确认会话是否可以安全终止 SELECT pg_terminate_backend(blocking_pid) FROM ( SELECT blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid WHERE NOT blocked_locks.GRANTED LIMIT 1 ) blockers;警告直接终止生产会话可能导致数据不一致应作为最后手段使用。3.1.2 优化阻塞查询有时可以通过优化查询来减少锁持有时间添加适当的索引加速查询减少事务范围避免在事务中执行耗时操作使用更弱的锁模式如FOR KEY SHARE替代FOR UPDATE3.2 长期预防措施3.2.1 应用设计优化短事务原则保持事务尽可能短小锁顺序确保所有事务以相同顺序获取锁重试机制对可能被阻塞的操作实现自动重试3.2.2 监控告警设置-- 创建阻塞监控视图 CREATE VIEW blocking_queries AS SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement, now() - blocked_activity.query_start AS blocked_duration, now() - blocking_activity.query_start AS blocking_duration FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.GRANTED;可以定期查询此视图或设置监控系统在阻塞超过阈值时告警。4. 实战案例解析4.1 案例1长事务阻塞DDL操作现象ALTER TABLE操作被阻塞无法完成。分析SELECT pid, usename, query, query_start, state FROM pg_stat_activity WHERE state idle in transaction;发现有一个已空闲但未提交的事务持有锁。解决方案联系应用团队确认是否可以提交/回滚该事务如无法联系评估风险后终止该会话4.2 案例2批量更新阻塞关键业务查询现象订单支付接口响应变慢发现被后台批量作业阻塞。解决方案将批量作业拆分为小批次处理在业务低峰期执行批量作业为批量作业设置较低的lock_timeout4.3 案例3应用连接池配置不当现象连接池中的连接长时间保持事务打开状态。解决方案配置连接池的max_idle_time和idle_in_transaction_session_timeout确保应用正确关闭事务5. 高级技巧与工具5.1 使用pg_stat_statements分析问题查询-- 先启用扩展 CREATE EXTENSION pg_stat_statements; -- 查看消耗资源最多的查询 SELECT query, calls, total_time, rows, 100.0 * total_time / NULLIF(sum(total_time) OVER(), 0) AS percentage FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;5.2 使用auto_explain记录慢查询执行计划-- 在postgresql.conf中设置 shared_preload_libraries auto_explain auto_explain.log_min_duration 1s auto_explain.log_analyze on5.3 使用pgBadger分析日志pgBadger可以分析PostgreSQL日志生成包含阻塞查询统计的HTML报告。6. 性能优化建议索引优化确保频繁查询的字段有适当索引查询重构避免在事务中执行不必要的操作连接管理使用连接池并正确配置超时设置合理配置statement_timeout和lock_timeout监控告警建立完善的监控体系在实际生产环境中我通常会设置一个定期任务每小时检查一次阻塞情况并记录到日志表中这样可以帮助识别长期存在的阻塞模式。同时对于关键业务系统建议实现实时监控当阻塞超过一定阈值时立即告警。 PostgreSQL阻塞查询检测与解决方案
1. PostgreSQL阻塞查询问题概述在PostgreSQL数据库运维过程中阻塞查询(Blocked Queries)是最常见的性能瓶颈之一。当某个会话持有锁资源而长时间不释放时其他需要相同锁资源的会话就会被阻塞导致系统响应变慢甚至完全卡死。这种情况在OLTP系统中尤为常见特别是在高并发写入场景下。重要提示阻塞不同于死锁(Deadlock)。死锁是PostgreSQL能够自动检测并解决的而阻塞需要DBA人工干预才能解除。我最近处理过一个典型的生产案例某电商平台的订单提交接口在促销期间频繁超时经排查发现是因为库存扣减操作被长时间运行的报表查询阻塞。通过本文介绍的方法我们快速定位并解决了问题。2. 阻塞检测的核心技术2.1 PostgreSQL锁机制基础PostgreSQL采用多版本并发控制(MVCC)机制配合多种锁类型来管理并发访问表级锁ACCESS SHARE最弱、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE最强行级锁FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE咨询锁应用层控制的特殊锁锁冲突矩阵决定了哪些锁可以共存。例如当一个会话持有ROW EXCLUSIVE锁通常由UPDATE语句获取时其他会话尝试获取相同行的FOR UPDATE锁就会被阻塞。2.2 阻塞查询检测方法2.2.1 使用pg_stat_activity视图SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.GRANTED;这个查询会返回所有被阻塞的会话及其阻塞源的关键信息包括被阻塞会话的PID和用户名阻塞会话的PID和用户名被阻塞的SQL语句导致阻塞的SQL语句2.2.2 使用pg_blocking_pids函数PostgreSQL 9.6SELECT pid, usename, query, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;这个更简洁的查询会列出所有被阻塞的会话及其阻塞源的PID。2.3 高级阻塞分析技巧2.3.1 锁等待超时设置-- 设置锁等待超时单位毫秒 SET lock_timeout 5000; -- 5秒后自动取消被阻塞的查询这个参数可以在会话级别设置避免查询无限期等待。2.3.2 查看锁详情SELECT lock.locktype, lock.relation::regclass, lock.mode, lock.virtualtransaction, lock.pid, stat.usename, stat.query, stat.query_start, age(now(), stat.query_start) AS query_age FROM pg_catalog.pg_locks lock JOIN pg_catalog.pg_stat_activity stat ON lock.pid stat.pid WHERE NOT lock.granted;这个查询提供了更详细的锁信息包括锁类型、锁模式、持有时间等。3. 阻塞问题解决方案3.1 即时解决方案3.1.1 终止阻塞会话-- 谨慎操作先确认会话是否可以安全终止 SELECT pg_terminate_backend(blocking_pid) FROM ( SELECT blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid WHERE NOT blocked_locks.GRANTED LIMIT 1 ) blockers;警告直接终止生产会话可能导致数据不一致应作为最后手段使用。3.1.2 优化阻塞查询有时可以通过优化查询来减少锁持有时间添加适当的索引加速查询减少事务范围避免在事务中执行耗时操作使用更弱的锁模式如FOR KEY SHARE替代FOR UPDATE3.2 长期预防措施3.2.1 应用设计优化短事务原则保持事务尽可能短小锁顺序确保所有事务以相同顺序获取锁重试机制对可能被阻塞的操作实现自动重试3.2.2 监控告警设置-- 创建阻塞监控视图 CREATE VIEW blocking_queries AS SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement, now() - blocked_activity.query_start AS blocked_duration, now() - blocking_activity.query_start AS blocking_duration FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.GRANTED;可以定期查询此视图或设置监控系统在阻塞超过阈值时告警。4. 实战案例解析4.1 案例1长事务阻塞DDL操作现象ALTER TABLE操作被阻塞无法完成。分析SELECT pid, usename, query, query_start, state FROM pg_stat_activity WHERE state idle in transaction;发现有一个已空闲但未提交的事务持有锁。解决方案联系应用团队确认是否可以提交/回滚该事务如无法联系评估风险后终止该会话4.2 案例2批量更新阻塞关键业务查询现象订单支付接口响应变慢发现被后台批量作业阻塞。解决方案将批量作业拆分为小批次处理在业务低峰期执行批量作业为批量作业设置较低的lock_timeout4.3 案例3应用连接池配置不当现象连接池中的连接长时间保持事务打开状态。解决方案配置连接池的max_idle_time和idle_in_transaction_session_timeout确保应用正确关闭事务5. 高级技巧与工具5.1 使用pg_stat_statements分析问题查询-- 先启用扩展 CREATE EXTENSION pg_stat_statements; -- 查看消耗资源最多的查询 SELECT query, calls, total_time, rows, 100.0 * total_time / NULLIF(sum(total_time) OVER(), 0) AS percentage FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;5.2 使用auto_explain记录慢查询执行计划-- 在postgresql.conf中设置 shared_preload_libraries auto_explain auto_explain.log_min_duration 1s auto_explain.log_analyze on5.3 使用pgBadger分析日志pgBadger可以分析PostgreSQL日志生成包含阻塞查询统计的HTML报告。6. 性能优化建议索引优化确保频繁查询的字段有适当索引查询重构避免在事务中执行不必要的操作连接管理使用连接池并正确配置超时设置合理配置statement_timeout和lock_timeout监控告警建立完善的监控体系在实际生产环境中我通常会设置一个定期任务每小时检查一次阻塞情况并记录到日志表中这样可以帮助识别长期存在的阻塞模式。同时对于关键业务系统建议实现实时监控当阻塞超过一定阈值时立即告警。