PostgreSQL 并行查询监控实战：深入 pg_stat_activity

PostgreSQL 并行查询监控实战：深入 pg_stat_activity

大家好，我是你们的数据库老 বন্ধু，码农老王。

PostgreSQL 的并行查询特性，相信大家都不陌生。它能充分利用多核 CPU 的优势，显著提升查询性能，特别是在处理大数据量时，效果尤为明显。但是，并行查询也不是“银弹”，如果使用不当，或者监控不到位，反而可能导致性能下降，甚至引发系统故障。

今天，咱们就来聊聊 PostgreSQL 并行查询的监控，特别是如何利用 pg_stat_activity 视图来掌控全局，运筹帷幄。

为什么需要监控并行查询？

在深入 pg_stat_activity 之前，咱们先来明确一下，为什么要监控并行查询？主要有以下几个原因：

1. 资源利用率：并行查询会消耗更多的 CPU、内存和 I/O 资源。如果并行度设置过高，或者查询本身存在问题，可能会导致资源耗尽，影响其他查询的正常执行。
2. 性能瓶颈：并行查询的性能受到多个因素的影响，比如数据分布、表结构、索引、配置参数等。通过监控，我们可以及时发现性能瓶颈，并进行针对性优化。
3. 锁冲突：并行查询可能会增加锁冲突的概率。如果多个并行工作进程同时访问同一数据块，就可能发生锁等待，甚至死锁。
4. 异常情况：并行查询的执行过程可能出现各种异常情况，比如工作进程崩溃、查询超时等。通过监控，我们可以及时发现并处理这些异常情况。

总而言之，监控并行查询是为了确保其高效、稳定地运行，避免出现各种问题，最终提升整体数据库性能。

pg_stat_activity 视图：并行查询监控的利器

pg_stat_activity 是 PostgreSQL 中一个非常重要的系统视图，它提供了当前数据库活动连接的详细信息，包括每个连接的状态、执行的 SQL 语句、执行时间、等待事件等。对于并行查询，pg_stat_activity 更是提供了关键的监控指标。

咱们先来看看 pg_stat_activity 中与并行查询相关的几个关键字段：

• pid: 进程 ID。对于并行查询，每个工作进程都有一个独立的 pid。
• backend_type: 后端类型。对于并行查询，我们可以看到以下几种类型：
  • leader: 并行查询的领导者进程，负责协调各个工作进程的执行。
  • parallel worker: 并行查询的工作进程，实际执行查询的一部分。
  • background worker: 后台工作进程（可能和并行查询无关，这里不多讨论）。
• query: 当前正在执行的 SQL 语句。对于并行查询，我们可以看到每个工作进程正在执行的 SQL 片段。
• state: 当前连接的状态。对于并行查询，我们可以看到以下几种状态：
  • active: 正在执行。
  • idle: 空闲。
  • idle in transaction: 在事务中空闲。
  • waiting: 等待某个事件（比如锁）。
• wait_event_type 和 wait_event: 如果 state 为 waiting，这两个字段会显示具体的等待事件类型和事件。
• backend_xid 和 backend_xmin: 当前事务的 ID 和最小事务 ID。这些字段可以帮助我们识别长时间运行的事务，以及可能导致“膨胀”的事务。
• query_start 和 state_change: 查询开始时间和状态改变时间。这些字段可以帮助我们计算查询的执行时间和等待时间。

实战演练：如何利用 pg_stat_activity 监控并行查询

理论知识讲完了，接下来咱们进入实战环节。我会通过几个具体的例子，来演示如何利用 pg_stat_activity 监控并行查询。

1. 查看当前有多少个并行查询正在执行

SELECT COUNT(*) 
FROM pg_stat_activity 
WHERE backend_type = 'parallel worker';

这条 SQL 语句会统计当前 backend_type 为 parallel worker 的进程数量，也就是正在执行的并行工作进程的数量。如果这个数字过高，可能意味着并行度设置过高，或者存在大量的并行查询。

2. 查看某个特定并行查询的详细信息

如果我们想查看某个特定并行查询的详细信息，可以先找到它的领导者进程的 pid，然后用这个 pid 来过滤 pg_stat_activity。

-- 假设领导者进程的 pid 为 12345
SELECT * 
FROM pg_stat_activity 
WHERE pid = 12345 OR leader_pid = 12345;

增加了leader_pid列显示所有工作进程。

这条 SQL 语句会显示领导者进程（pid = 12345）和所有属于这个并行查询的工作进程（leader_pid = 12345）的详细信息，包括它们的状态、执行的 SQL 语句、等待事件等。

3. 查找执行时间最长的并行查询

SELECT pid, query, (now() - query_start) as duration
FROM pg_stat_activity
WHERE backend_type = 'leader'
  AND state = 'active'
ORDER BY duration DESC
LIMIT 10;

这条 SQL 语句会找出当前正在执行的、执行时间最长的 10 个并行查询的领导者进程。通过查看它们的 query 字段，我们可以了解这些查询的具体内容，进而分析它们为什么执行时间这么长。

4. 查找导致锁等待的并行查询

SELECT pid, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type IN ('leader', 'parallel worker')
  AND state = 'waiting'
  AND wait_event_type = 'Lock';

这条 SQL 语句会找出所有处于等待状态、且等待事件类型为 Lock 的并行查询进程（包括领导者进程和工作进程）。通过查看它们的 query 字段和 wait_event 字段，我们可以了解这些查询正在等待哪个锁，以及是什么原因导致了锁冲突。

5. 监控并行查询的资源消耗

虽然 pg_stat_activity 本身不提供 CPU、内存等资源消耗的直接信息，但我们可以结合其他工具（比如 top、iostat、vmstat 等）来监控并行查询的资源消耗情况。

例如，我们可以通过 top 命令查看每个 PostgreSQL 进程的 CPU 和内存使用情况，然后根据 pid 将这些信息与 pg_stat_activity 中的信息关联起来，从而了解每个并行查询的资源消耗情况。

进阶技巧：自定义监控脚本

上面介绍的都是一些基本的监控方法。在实际应用中，我们可能需要更灵活、更强大的监控工具。这时，我们可以考虑自定义监控脚本。

我们可以使用任何支持连接 PostgreSQL 的编程语言（比如 Python、Shell、Perl 等）来编写监控脚本。脚本的核心逻辑就是定期查询 pg_stat_activity 视图，并根据查询结果进行分析、报警、记录日志等操作。

下面是一个简单的 Python 脚本示例，用于监控并行查询的数量和执行时间：

import psycopg2
import time
 
# 连接数据库
conn = psycopg2.connect(database="your_database", user="your_user", password="your_password", host="your_host", port="your_port")
cur = conn.cursor()
 
# 循环监控
while True:
    # 查询并行工作进程数量
    cur.execute("SELECT COUNT(*) FROM pg_stat_activity WHERE backend_type = 'parallel worker'")
    worker_count = cur.fetchone()[0]
 
    # 查询执行时间最长的并行查询
    cur.execute("SELECT pid, query, (now() - query_start) as duration FROM pg_stat_activity WHERE backend_type = 'leader' AND state = 'active' ORDER BY duration DESC LIMIT 1")
    longest_query = cur.fetchone()
 
    # 打印监控信息
    print(f"Time: {time.strftime('%Y-%m-%d %H:%M:%S')}")
    print(f"Parallel worker count: {worker_count}")
    if longest_query:
        print(f"Longest query: pid={longest_query[0]}, duration={longest_query[2]}, query={longest_query[1]}")
    print("-" * 20)
 
    # 休眠一段时间
    time.sleep(60)
 
# 关闭连接
cur.close()
conn.close()

这个脚本会每隔 60 秒查询一次 pg_stat_activity 视图，并打印当前并行工作进程的数量和执行时间最长的并行查询的信息。你可以根据自己的需求修改这个脚本，添加更多的监控指标和报警逻辑。

总结

PostgreSQL 的并行查询是一把“双刃剑”，用好了可以提升性能，用不好反而会带来问题。通过 pg_stat_activity 视图，我们可以实时监控并行查询的执行情况，及时发现并解决问题，确保数据库的高效、稳定运行。

希望今天的分享对大家有所帮助。如果你有任何问题或者建议，欢迎在评论区留言，咱们一起交流学习。

最后，还是那句话：数据库优化，永无止境！