postgresql-postgresql中检查慢查询的原因

在 PostgreSQL 数据库中，查询变慢的原因多种多样，其中锁等待和长时间未提交的事务是两种常见但容易被忽视的因素。本文将介绍如何利用 pg_stat_activity(SQL语句执行视图) 和 pg_locks(PG锁视图) 这两个系统视图，系统地排查锁与事务引起的问题。

事务与状态#

在 PostgreSQL 中，事务 是一组原子性的 SQL 操作，要么全部成功，要么全部回滚。会话状态（pg_stat_activity.state）则描述了当前连接在某一时刻正在做什么。一个会话可能处于以下主要状态：

• active：正在执行查询。
• idle：空闲，没有事务，等待客户端发送命令。
• idle in transaction：事务已开启，但当前没有执行查询，正在等待客户端发送下一条 SQL 命令。
• idle in transaction (aborted)：事务已开启，但遇到错误，等待客户端发送 ROLLBACK 或 COMMIT。
• fastpath function call：正在执行快速路径函数（很少见）。

事务与状态的关系：事务可以影响状态，例如开启事务后若未执行查询，状态即为 idle in transaction；但状态并不总代表事务的存在，例如 idle 状态时可能没有事务（xact_start 为 NULL）。因此，排查问题时应同时关注状态和事务的开始时间。

常用字段及含义#

pg_stat_activity 视图关键字段#

• pid：后端进程 ID。
• datname：数据库名。
• usename：用户名。
• application_name：客户端应用名称（可在连接串中设置）。
• client_addr：客户端 IP 地址。
• backend_start：连接建立时间。
• xact_start：当前事务开始时间（若没有事务则为 NULL）。
• query_start：当前查询开始时间（若状态为 idle 则为上一条查询的开始时间）。
• state_change：状态改变的时间（例如从 active 变为 idle 的时刻）。
• state：会话当前状态。
• wait_event_type：等待事件类型（如 Lock、Client、IO、LWLock 等）。表示会话正在等待的资源类型或子系统。
• wait_event：表示该wait_event_type下的具体等待点，具体的等待事件名称（如 ClientRead、transactionid、relation 等）。
• query：当前或最近执行的 SQL 语句。

时间间隔转换示例：
SELECT pid, now() - xact_start AS xact_duration FROM pg_stat_activity WHERE xact_start IS NOT NULL;

pg_locks 视图关键字段#

• locktype：锁的类型，包括 relation（表锁）、transactionid（事务 ID 锁）、virtualxid、tuple（行级锁）、advisory（咨询锁）、object、page 等。
• database：数据库 OID（可用 datname 关联）。
• relation：关系 OID，当 locktype = 'relation' 时有效，可用 relation::regclass 转换为表名。
• page / tuple：分别表示页号或元组号，用于行级锁。
• transactionid：事务 ID，用于事务级锁。
• classid / objid / objsubid：用于标识特定对象（如咨询锁的键）。
• mode：锁的模式，例如 AccessShareLock、RowShareLock、RowExclusiveLock、ShareLock、ExclusiveLock、AccessExclusiveLock 等。
• granted：布尔值，true 表示已获得锁，false 表示正在等待锁。
• pid：持有或等待该锁的进程 ID。
• fastpath：是否通过快速路径获取锁。

排查思路#

1. 观察会话状态分布
   通过 pg_stat_activity 查看各状态的会话数量，重点关注 active（长时间运行的查询）和 idle in transaction（长期未提交的事务）。
2. 识别长时间运行的活动查询
   对于 active 状态的会话，检查 query_start 与当前时间的差值。如果查询运行时间异常，进一步查看 wait_event_type 和 wait_event，判断是否在等待锁。
3. 检查锁等待与阻塞关系
   利用 pg_blocking_pids() 函数找出阻塞者，或直接查询 pg_locks 中 granted = false 的记录，关联 pg_stat_activity 获取等待者的查询和状态。
4. 分析空闲事务
   idle in transaction 状态的事务会持有已获得的锁，并占用连接资源。应查看其持续时间、客户端地址、最后执行的查询，判断是否是应用逻辑问题（如未及时提交或异常处理不当）。
5. 定位具体客户端或应用
   通过 client_addr、application_name 等字段，将问题会话与具体的应用实例关联起来，尤其在分布式环境中能快速定位故障节点。

常用查询示例#

1. 查看会话状态分布及最长持续时间#

SELECT state,
       count(*) AS session_count,
       max(now() - xact_start) AS max_tx_age,
       max(now() - state_change) AS max_state_age
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state
ORDER BY session_count DESC;

判读：若 idle in transaction 状态的数量较多且 max_tx_age 很大，说明有事务长时间未提交，可能持有锁或导致连接耗尽。此时可进一步查看这些会话的 wait_event，通常为 ClientRead，表示数据库已返回结果，正等待客户端发送下一条命令。

2. 检查锁等待（是否存在阻塞）#

SELECT blocked.pid AS blocked_pid,
       blocked.usename AS blocked_user,
       blocking.pid AS blocking_pid,
       blocking.usename AS blocking_user,
       blocked.query AS blocked_query,
       blocking.query AS blocking_query,
       blocked.wait_event_type,
       blocked.wait_event
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid;

判读：如果查询无结果，说明当前没有会话正在等待锁。若有结果，每一行都表示一个阻塞关系，可以进一步终止阻塞进程（SELECT pg_terminate_backend(pid);）或分析其语句。

pg_blocking_pids(pid) 是 PostgreSQL 提供的一个系统函数，它接受一个进程 ID，返回所有直接阻塞该进程的进程 ID 数组。

3. 获取所有未被授予的锁请求#

SELECT l.pid,
       a.usename,
       a.state,
       a.query,
       l.locktype,
       l.mode,
       l.granted,
       l.relation::regclass AS table_name,
       l.transactionid,
       l.classid, l.objid
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

该查询直接列出了所有等待中的锁请求，结合锁类型和模式可以判断等待的是什么资源。

4. 定位 idle in transaction 的会话来源#

SELECT client_addr,
       application_name,
       count(*) FILTER (WHERE state = 'idle in transaction') AS stuck_sessions,
       max(now() - xact_start) AS longest_tx
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY client_addr, application_name
ORDER BY stuck_sessions DESC;

此查询按客户端地址和应用名称分组，显示空闲事务的分布，有助于快速找到问题应用实例（如某个 pod 或服务节点）。

5. 检查咨询锁（Advisory Lock）的持有情况#

某些应用使用咨询锁（pg_advisory_lock / pg_try_advisory_lock）来实现应用级互斥。如果这些锁未被正确释放，也会阻塞其他会话。以下查询可以列出所有咨询锁及其持有者状态：

SELECT l.pid,
       l.granted,
       ((l.classid::bigint << 32) | (l.objid::bigint & 0xFFFFFFFF)) AS lock_key,
       a.state,
       now() - a.state_change AS held_for,
       now() - a.xact_start AS xact_age,
       a.wait_event_type,
       a.wait_event,
       a.application_name,
       a.client_addr,
       left(coalesce(a.query, ''), 60) AS last_query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'advisory'
ORDER BY a.state_change;

判读要点：

• held_for（now() - state_change）反映了锁被持有的时长，比连接年龄（conn_age）更准确，因为连接可能被连接池复用。
• last_query 若恒为 SELECT pg_try_advisory_lock($1)，表明这是专用的持锁连接，用于应用层锁管理。
• granted = false 表示有会话在等待该咨询锁，但使用 try_ 系列函数时通常不会排队，因此这里多数为 true。

总结与注意事项#

• 锁等待 通常表现为 active 状态且 wait_event_type = 'Lock'，这时应重点分析 pg_locks 和阻塞关系。
• 空闲事务 是另一个常见问题，它不会立即表现为查询慢，但会逐渐积累锁和连接资源，最终拖垮数据库。
• 连接池 可能掩盖真实的问题来源，因为一个后端进程可能被不同的应用请求复用。此时需结合 state_change 和 query 的内容来判断当前锁是何时、由哪个逻辑操作产生的。
• 咨询锁 容易遗漏，特别是当应用使用 ORM 或自定义锁管理时，应养成定期检查的习惯。
• 在实际生产环境中，建议建立监控告警，对长时间运行的查询、idle in transaction 会话数量、锁等待事件进行持续跟踪

其它常见文档#

连接池与连接管理问题#

连接池耗尽#

• 现象：应用无法获取数据库连接，请求排队甚至超时，表现为数据库 pg_stat_activity 中连接数接近 max_connections，但大量连接处于 idle 或 idle in transaction。
• 排查：

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;

• 解决：优化应用连接释放逻辑，合理设置连接池大小，避免连接泄漏。

连接复用不当#

• 连接池复用的连接可能残留上一个请求的事务状态、临时表、search_path 变更、预备语句等，导致异常行为或性能下降。
• 排查：注意 pg_stat_activity 中 xact_start 非空但 state 为 idle 的连接，检查其 query 是否为上一条命令。

硬件资源瓶颈#

CPU 饱和#

• 现象：查询变慢，系统 CPU 使用率高（用户态或系统态），pg_stat_activity 中大量 active 会话，wait_event 可能为 CPU（较少见）或无等待事件。
• 排查：使用 top、vmstat、mpstat 查看 CPU 使用；结合 pg_stat_statements 找出消耗 CPU 最多的 SQL。
• 解决：优化查询（索引、重写），增加 CPU 资源，限制并发。

内存不足与交换#

• 现象：数据库性能急剧下降，操作系统开始使用 swap，pg_stat_activity 中等待事件可能出现 IO 或 DataFileRead 增加。
• 排查：free -h、vmstat 1 观察 swap 使用；检查 shared_buffers、work_mem、maintenance_work_mem 等参数是否过大导致内存压力。
• 解决：调整内存参数，确保 PostgreSQL 共享内存和每个后端的内存需求不超过物理内存，避免过度分配 work_mem。

I/O 瓶颈#

• 现象：磁盘 I/O 利用率高，查询大量扫描数据，等待事件为 DataFileRead、WALWrite、CheckpointWrite 等。
• 排查：使用 iostat -x 1 查看磁盘利用率、平均等待时间；pg_stat_statements 中 blk_read_time 高的语句。
• 解决：优化索引减少扫描，增加 shared_buffers 提高缓存命中，使用更快的存储（SSD、NVMe），调整 checkpoint 和 bgwriter 参数。

PostgreSQL 内部争用（非普通锁）#

轻量级锁（LWLock）#

• 现象：等待事件为 LWLock 或具体名称如 buffer_content、lock_manager、wal_insert 等。
• 原因：并发访问共享内存结构（缓冲池、锁表、WAL 缓冲区）产生争用。
• 排查：

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1,2 ORDER BY 3 DESC;

• 解决：减少热点（如高频更新同一行、大量并发短事务），调整 max_wal_size、wal_buffers，使用分区、连接池控制并发。

参考文档#

1. postgresql-postgresql中的锁
2. postgresql-记一次因数据库锁导致的接口超时问题排查

外链#