PostgreSQL 性能调优实战：pg_stat_activity 深度解析与问题排查

你好，我是“爱偷懒的 DBA”。今天咱们来聊聊 PostgreSQL 数据库性能调优中一个非常重要的视图：pg_stat_activity。相信你作为一名 PostgreSQL 开发者，一定遇到过慢查询、锁等待这些让人头疼的问题。pg_stat_activity 就像数据库的“监控摄像头”，可以帮你实时了解数据库中各个连接（会话）的状态，是排查性能问题的利器。

为什么需要 pg_stat_activity？

在生产环境中，数据库的性能直接关系到应用的响应速度和用户体验。当应用出现卡顿、响应慢等问题时，我们首先要做的就是找出“罪魁祸首”。pg_stat_activity 提供了以下关键信息，帮助我们快速定位问题：

• 哪些连接正在执行 SQL？
• 执行了哪些 SQL 语句？
• SQL 语句执行了多长时间？
• 连接的状态是什么（idle、active、idle in transaction 等）？
• 是否存在锁等待？
• 连接的来源（IP 地址、应用程序）？

通过这些信息，我们可以迅速判断出是否存在慢查询、锁冲突、连接数过多等问题，进而采取相应的优化措施。

pg_stat_activity 核心字段解读

要用好 pg_stat_activity，首先要理解其各个字段的含义。下面我挑选一些最常用的字段进行详细解读，并结合实际场景进行说明。

-- 查看 pg_stat_activity 视图
SELECT * FROM pg_stat_activity;

• datid/datname: 连接的数据库 OID 和数据库名称。通常用于区分不同数据库的连接。
• pid: 连接对应的后端进程 ID。在操作系统层面，可以使用 ps 命令查看该进程的详细信息。
• usesysid/usename: 连接的用户 OID 和用户名。用于识别连接的用户身份。
• application_name: 应用程序设置的连接名称。可以用于区分不同应用的连接，方便问题定位。建议在连接字符串中设置 application_name。
• client_addr/client_hostname/client_port: 客户端的 IP 地址、主机名和端口号。用于识别连接的来源。
• backend_start/xact_start/query_start/state_change: 连接开始时间、事务开始时间、查询开始时间、状态变更时间。这些时间戳字段对于分析查询的执行时间和判断连接状态非常重要。
• state: 连接的当前状态。常见状态包括：
  • active: 正在执行 SQL 语句。
  • idle: 空闲状态，等待客户端发送新的命令。
  • idle in transaction: 处于事务中，但没有执行任何 SQL 语句。
  • idle in transaction (aborted): 事务已出错，但未回滚。
  • fastpath function call:执行一个快速路径函数
  • disabled: 此后端已禁用跟踪。
• query: 当前正在执行的 SQL 语句（如果 state 为 active）。如果 state 为 idle，则显示上一次执行的 SQL 语句。对于长时间运行的查询，该字段非常有用。
• wait_event_type/wait_event: 锁等待事件类型和锁等待事件。如果连接正在等待某个锁，这两个字段会显示锁的类型和具体信息。关于锁等待的详细分析，我们会在后面展开。

实战案例：利用 pg_stat_activity 排查问题

光说不练假把式，下面我们通过几个实际案例来演示如何使用 pg_stat_activity 排查问题。

案例 1：找出慢查询

假设你发现应用响应变慢，怀疑是数据库出现了慢查询。你可以执行以下 SQL 语句找出执行时间最长的查询：

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    query_start,
    state,
    query
FROM
    pg_stat_activity
WHERE
    state = 'active'
    AND query_start < now() - interval '1 minute' -- 找出执行时间超过 1 分钟的查询
ORDER BY
    query_start;

这条 SQL 语句会列出所有状态为 active 且执行时间超过 1 分钟的查询。你可以根据 query 字段中的 SQL 语句进行分析，找出慢查询的原因。常见的慢查询原因包括：

• 缺少索引： 对查询条件的字段没有建立合适的索引，导致全表扫描。
• SQL 语句编写不当： 使用了复杂的子查询、关联查询，或者没有使用合适的查询条件。
• 数据量过大： 表的数据量太大，即使有索引也可能导致查询变慢。
• 统计信息过期: 统计信息不是最新的，查询计划不准确。

案例 2：定位锁等待

如果你的应用出现了卡顿，并且数据库 CPU 使用率不高，很可能是出现了锁等待。你可以执行以下 SQL 语句找出正在等待锁的连接：

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    wait_event_type,
    wait_event,
    state,
    query
FROM
    pg_stat_activity
WHERE
    wait_event_type IS NOT NULL;

这条 SQL 语句会列出所有 wait_event_type 不为空的连接，即正在等待锁的连接。wait_event_type 和 wait_event 字段会显示锁的类型和具体信息。常见的锁类型包括：

• Lock: 表级锁、行级锁等。
• LWLock: 轻量级锁，用于保护 PostgreSQL 内部数据结构。
• BufferLock:表示一个进程正在等待访问一个共享缓冲区

要进一步分析锁等待的原因，我们需要结合 pg_locks 视图。pg_locks 视图提供了更详细的锁信息，包括锁的类型、锁的模式、持有锁的进程 ID、等待锁的进程 ID 等。

SELECT
    pl.locktype,
    pl.database,
    pl.relation::regclass,
    pl.page,
    pl.tuple,
    pl.virtualxid,
    pl.transactionid,
    pl.classid,
    pl.objid,
    pl.objsubid,
    pl.pid,
    pl.mode,
    pl.granted,
    ps.query
FROM
    pg_locks pl
    JOIN pg_stat_activity ps ON pl.pid = ps.pid
WHERE
    NOT pl.granted;

这条 SQL 语句会列出所有未被授予的锁（即正在等待的锁），并关联 pg_stat_activity 视图显示持有锁的进程正在执行的 SQL 语句。通过分析这些信息，我们可以找出锁冲突的根源。

案例 3：发现空闲事务

长时间的空闲事务（idle in transaction）可能会导致一些问题，例如：

• 无法回收资源： 事务持有的锁和资源无法释放，可能导致其他连接阻塞。
• autovacuum 无法清理： 事务可能阻止 autovacuum 清理过期的行版本，导致表膨胀。

我们可以通过以下 SQL 语句找出空闲事务：

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    xact_start,
    state,
    query
FROM
    pg_stat_activity
WHERE
    state = 'idle in transaction'
    AND xact_start < now() - interval '10 minutes'; -- 找出空闲时间超过 10 分钟的事务

找出空闲事务后，我们需要分析其原因。常见的原因包括：

• 应用代码问题： 开启了事务但忘记提交或回滚。
• 客户端连接异常： 客户端连接断开，但事务未关闭。

对于长时间的空闲事务，我们可以考虑使用 pg_terminate_backend() 函数终止连接：

-- 终止 PID 为 12345 的连接
SELECT pg_terminate_backend(12345);

注意： 终止连接可能会导致数据丢失或不一致，请谨慎操作。在终止连接之前，最好先联系应用开发人员，了解事务的用途和状态。

高级技巧

除了上述基本用法，pg_stat_activity 还有一些高级技巧，可以帮助我们更深入地分析问题。

1. 结合 pg_stat_statements

pg_stat_statements 是 PostgreSQL 的一个扩展模块，用于跟踪 SQL 语句的执行统计信息，包括执行次数、总执行时间、平均执行时间、最长执行时间等。我们可以将 pg_stat_activity 和 pg_stat_statements 结合起来，找出执行频率高、耗时长的 SQL 语句，进行针对性优化。

首先，确保已经安装并启用了 pg_stat_statements 扩展：

-- 创建扩展
CREATE EXTENSION pg_stat_statements;
 
-- 查看扩展是否启用
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

然后，我们可以使用以下 SQL 语句找出执行次数最多或总执行时间最长的 SQL 语句：

-- 找出执行次数最多的 SQL 语句
SELECT
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM
    pg_stat_statements
ORDER BY
    calls DESC
LIMIT 10;
 
-- 找出总执行时间最长的 SQL 语句
SELECT
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 10;

2. 使用 auto_explain

auto_explain 是 PostgreSQL 的一个扩展模块，可以在日志中自动记录慢查询的执行计划。通过分析执行计划，我们可以找出慢查询的瓶颈所在。

首先，确保已经安装并启用了 auto_explain 扩展。然后，修改 postgresql.conf 配置文件，设置相关参数：

# 启用 auto_explain
shared_preload_libraries = 'auto_explain'
 
# 设置 auto_explain 的参数
auto_explain.log_min_duration = '1s'  -- 记录执行时间超过 1 秒的查询
auto_explain.log_analyze = true      -- 记录执行计划
auto_explain.log_buffers = true      -- 记录缓冲区使用情况
auto_explain.log_timing = true       -- 记录执行时间

重启 PostgreSQL 服务使配置生效。之后，当有查询的执行时间超过 auto_explain.log_min_duration 设置的阈值时，PostgreSQL 会自动将执行计划记录到日志文件中。你可以查看日志文件，分析执行计划，找出慢查询的原因。

3. 监控和告警

为了及时发现数据库性能问题，我们可以建立监控和告警机制。可以使用一些开源的监控工具，例如 Prometheus、Grafana、Zabbix 等，监控 pg_stat_activity 的各项指标，例如连接数、活动连接数、慢查询数量、锁等待数量等。当指标超过阈值时，触发告警，通知 DBA 或开发人员及时处理。

总结

pg_stat_activity 是 PostgreSQL 性能调优的必备工具。通过深入理解其各个字段的含义，结合实际案例进行分析，并掌握一些高级技巧，我们可以快速定位和解决各种数据库性能问题。记住，性能调优是一个持续的过程，需要不断学习和实践，才能更好地掌握 PostgreSQL 的性能优化技巧。希望今天的分享对你有所帮助！如果你有任何问题或想法，欢迎在评论区留言交流。