监控 PostgreSQL 活动查询：发现长时间运行的 SQL 语句

PostgreSQL 数据库管理员经常需要监控当前正在执行的查询，特别是识别那些长时间运行的 SQL 语句，这些可能是性能瓶颈的来源。本文将介绍一个实用的监控查询，并展示如何扩展它以获取更多有用信息。

监控查询

以下 SQL 查询可以显示当前所有活动查询的基本信息：

SELECT 
    pid,
    usename,
    datname as database_name,  
    application_name,
    client_addr,
    state,
    query,
    now() - query_start as duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

这个查询从 PostgreSQL 的系统视图 pg_stat_activity 中获取数据，返回以下信息：

• pid：进程 ID，可用于后续管理操作
• usename：执行查询的用户名
• application_name：客户端应用程序名称
• client_addr：客户端 IP 地址
• state：会话状态（这里只筛选 ‘active’ 状态的会话）
• query：正在执行的 SQL 语句
• duration：查询已运行的时间

结果按持续时间降序排列，让长时间运行的查询排在前面，便于快速发现问题。

更全面的监控选项

为了获得更全面的监控信息，可以考虑添加以下字段：

SELECT 
    pid,
    usename,
    datname as database_name,
    application_name,
    client_addr,
    state,
    query,
    now() - query_start as duration,
    backend_start,          -- 后台进程启动时间
    xact_start,            -- 当前事务开始时间
    wait_event_type,       -- 等待事件类型
    wait_event             -- 具体等待事件
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

实际应用场景

这个监控查询特别适用于以下场景：

1. 性能问题排查：快速发现并终止长时间运行的查询
2. 连接管理：识别占用资源的会话
3. 审计跟踪：了解当前数据库活动情况
4. 容量规划：分析查询模式和资源使用情况

注意事项

• 查询 pg_stat_activity 视图本身需要一定的权限
• 在生产环境中频繁运行此查询可能会对性能产生轻微影响
• 对于大型系统，可以考虑将结果限制为最长的几个查询

通过这个简单但强大的查询，数据库管理员可以快速了解数据库的活动状态，及时发现并解决潜在的性能问题。