监控活动查询：使用 pg_stat_activity 进行性能调优

当数据库突然变慢时，你需要知道 PostgreSQL 当前正在做什么。pg_stat_activity 显示活动查询、空闲会话、锁等待和未提交的事务，帮助你区分慢查询和被阻塞的查询。

在故障期间使用它，但也应保留一些常用查询用于日常检查。以下示例适用于你有权限查看所需会话活动的 PostgreSQL 系统。

理解 pg_stat_activity 视图

pg_stat_activity 是一个动态系统视图，每个连接到数据库集群的服务器进程对应一行。这包括客户端后端、后台工作进程以及空闲但仍在连接的会话。

监控此视图可以让你准确了解数据库 当前 正在做什么，这对于调试突发性能下降或诊断典型日志文件难以有效捕获的瞬时争用问题非常宝贵。

用于性能分析的关键列

虽然 pg_stat_activity 包含数十列，但以下列在诊断性能问题时至关重要：

列名	描述	调优相关性
pid	后端的进程 ID。	用于取消或终止会话。
datname	此后端连接的数据库名称。	有助于在多数据库环境中限定监控范围。
usename	发起连接的用户。	识别特定应用或用户活动。
application_name	连接的应用程序名称（如果客户端设置）。	非常适合识别来自特定微服务的连接。
state	当前活动状态（例如 active、idle、idle in transaction）。	后端正在做什么的核心指标。
query	当前查询，或空闲会话的最后一次查询。可见性可能受权限和设置限制。	识别涉及的 SQL 语句。
query_start	当前查询执行开始的时间戳。	用于计算查询持续时间。
wait_event_type 和 wait_event	进程正在等待什么（例如锁获取、I/O）的详细信息。	对于诊断争用和阻塞至关重要。

实际监控用例

pg_stat_activity 的真正威力在于过滤数据以回答特定的性能问题。

查看所有活动查询

要仅查看当前正在执行语句（而非空闲）的进程，请按 state 列过滤视图。

-- 查看所有当前正在执行的查询
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

识别长时间运行的查询

识别运行时间超过预期的查询通常是性能调优的第一步。这些查询可能消耗资源、导致 I/O 峰值或持有锁。

要识别运行时间超过特定阈值（例如 5 秒）的查询，请使用 now() 和 query_start 进行间隔减法。

-- 查找运行时间超过 5 秒的查询
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

使用适合你工作负载的阈值。在 OLTP 应用中，一个五秒的结账查询可能很严重，而一个五分钟的报告查询如果在非高峰时间运行可能很正常。

诊断事务中空闲的会话

处于 idle in transaction 状态的连接已启动事务但未提交或回滚。它正在等待客户端发送下一个命令。这些会话可能持有锁并保持旧行版本可见，这会延迟 autovacuum 的清理并导致表膨胀。

-- 查找处于空闲状态但持有未提交事务的会话
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

如果发现会话持有事务数分钟或数小时，请检查打开事务的应用程序路径。常见原因包括异常后缺少回滚处理、清理前返回连接池的连接、或交互式管理会话保持打开。

分析锁争用和阻塞

当查询挂起时，通常是在等待另一个进程持有的锁。pg_stat_activity 视图结合 pg_locks 对于诊断争用至关重要。

要查找当前正在等待资源（锁、I/O 等）的会话，请查看 wait_event 列。如果会话被阻塞，其 wait_event_type 通常为 Lock。

-- 识别当前被锁阻塞的进程
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

要快速查看“谁阻塞了谁”，PostgreSQL 还提供了 pg_blocking_pids()。

-- 显示被阻塞的会话以及阻塞它们的会话
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

如果返回一个被阻塞的 Web 请求等待长时间运行的管理更新，取消管理查询可能比终止应用程序会话更安全。

管理有问题的会话

一旦通过进程 ID (pid) 识别出有问题的查询或会话，PostgreSQL 提供了两个函数来管理它：

使用 pg_cancel_backend 取消查询

此函数尝试优雅地停止特定查询的执行。会话本身保持连接并可用于后续查询。

-- 示例：取消在 PID 12345 上运行的查询
SELECT pg_cancel_backend(12345);

使用 pg_terminate_backend 终止会话

此函数强制断开后端进程与服务器的连接。如果会话正在事务中，PostgreSQL 将自动回滚事务。

-- 示例：强制终止 PID 为 54321 的会话
SELECT pg_terminate_backend(54321);

当会话只是运行一个糟糕的查询时，首先尝试 pg_cancel_backend。对于卡住、废弃或持有无法正常清理的未提交事务的会话，使用 pg_terminate_backend。回滚大事务可能需要时间并增加 I/O 负载，因此请谨慎操作。

监控最佳实践

积极过滤

避免在生产环境中默认使用 SELECT * FROM pg_stat_activity。输出很嘈杂，如果应用程序发送文字值而不是绑定参数，query 文本可能暴露敏感值。选择你需要的列，并按 state、datname、application_name 或持续时间进行过滤。

使用工具进行自动监控

手动检查在故障期间很有用，但趋势应属于监控。在 PostgreSQL 仪表板中跟踪活动会话、等待会话、长事务和 idle in transaction 计数。

配置语句日志记录

将实时监控与历史数据结合。配置 log_min_duration_statement 等参数，记录超过特定阈值的查询，即使在查询执行完成后也能提供分析数据。

要点

保留三个常用检查：按持续时间排序的活动查询、按事务年龄排序的空闲事务、以及被阻塞的会话及其阻塞者。当 PostgreSQL 感觉缓慢时，这些视图会告诉你是否需要调优 SQL、修复事务处理或清除阻塞会话。