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

数据库性能在很大程度上取决于有效的资源管理和及时识别瓶颈。对于 PostgreSQL 管理员和开发人员来说，内置的系统视图 pg_stat_activity 是进行实时监控和即时性能调优的最重要工具。

本指南将探讨如何利用 pg_stat_activity 来检查所有活动的后端进程、识别长时间运行的查询、诊断连接问题以及排查锁争用，从而帮助您维护一个健康且响应迅速的数据库环境。

理解 `pg_stat_activity` 视图

pg_stat_activity 是一个动态系统视图，为连接到数据库集群的每个服务器进程（后端）提供一行数据。这包括运行查询的客户端、后台工作进程以及当前空闲但保持连接打开的进程。

监控此视图可以帮助您准确了解数据库当前正在做什么，这对于调试突发的性能下降或诊断典型日志文件难以有效捕获的瞬态争用问题具有不可估量的价值。

性能分析的关键列

尽管 pg_stat_activity 包含数十个列，但在诊断性能问题时，以下这些列至关重要：

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

实际监控用例

pg_stat_activity 的真正强大之处在于通过筛选数据来回答特定的性能问题。

1. 查看所有活动查询

要仅查看当前正在运行语句（非空闲）的进程，请按 state 列筛选视图。

-- View all currently executing queries
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

2. 识别长时间运行和慢速查询

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

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

-- Find queries running longer than 5 seconds
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;

提示： 根据您的典型工作负载自定义阈值（5 seconds）。在 OLTP 环境中，超过 1 秒的任何操作都可能被视为慢速操作。

3. 诊断事务中空闲会话（Idle In Transaction）

处于 idle in transaction 状态的连接意味着它已经开始了一个事务块（BEGIN），但尚未提交或回滚，并且当前正在等待客户端应用程序发出下一个命令。这些会话是危险的，因为它们通常会持有锁并阻止 Vacuum 操作，从而导致表膨胀和事务 ID 耗尽。

-- Find sessions that are idle but holding an open transaction
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;

如果您发现会话持有开放事务数分钟或数小时，则客户端应用程序可能存在逻辑错误（例如，在异常发生后未能提交）或配置错误（例如，连接池问题）。

4. 分析锁争用和阻塞

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

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

-- Identify processes currently blocked by a 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;

要进行完整的锁分析（谁在等待谁），需要将 pg_stat_activity 与 pg_locks 进行连接，关联持有锁（granted = true）的进程和正在等待锁（granted = false）的进程。

管理有问题的会话

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

1. 取消查询（`pg_cancel_backend`）

此函数尝试以优雅的方式停止特定查询的执行。会话本身保持连接，可用于未来的查询。

-- Example: Cancel the query running on PID 12345
SELECT pg_cancel_backend(12345);

2. 终止会话（`pg_terminate_backend`）

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

-- Example: Forcefully terminate the session with PID 54321
SELECT pg_terminate_backend(54321);

⚠️ 警告：谨慎使用终止操作

始终首先尝试使用 pg_cancel_backend。强制终止会话（pg_terminate_backend）应保留给无响应或占用大量资源的会话，因为回滚大型事务有时会消耗大量的 I/O 资源并需要时间。

监控的最佳实践

积极筛选

切勿在具有数千个连接的生产服务器上运行 SELECT * FROM pg_stat_activity。输出通常是压倒性的，并且查询本身可能会增加轻微的开销。始终使用 WHERE 子句（例如 WHERE state = 'active'）来集中您的调查范围。

使用工具进行自动化监控

虽然手动检查对于故障排除至关重要，但应将 pg_stat_activity 数据集成到您的标准监控工具中（如 Prometheus、DataDog 或专门的 PostgreSQL 仪表板），以跟踪查询持续时间、平均活动连接数和 idle in transaction 计数的长期趋势。

配置语句日志记录

将实时监控与历史数据相结合。配置诸如 log_min_duration_statement 之类的参数，以记录超出特定阈值的查询，从而即使在查询执行完成后也能提供分析数据。

结论

pg_stat_activity 是 PostgreSQL DBA 了解服务器实时操作的重要窗口。通过定期查询和筛选此视图，您可以立即获得诊断性能问题、识别低效 SQL 和及时解决阻塞情况所需的可见性。掌握对 state、duration 和 wait_event 的解释，可以将性能调优从被动应对转变为主动管理过程。

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

理解 pg_stat_activity 视图