pg_stat_activity

视图字段说明

The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.

\d pg_stat_activity;
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers 
------------------+--------------------------+-----------
 datid            | oid                      | 
 datname          | name                     | 
 pid              | bigint                   | 
 sessionid        | bigint                   | 
 usesysid         | oid                      | 
 usename          | name                     | 
 application_name | text                     | 
 client_addr      | inet                     | 
 client_hostname  | text                     | 
 client_port      | integer                  | 
 backend_start    | timestamp with time zone | 
 xact_start       | timestamp with time zone | 
 query_start      | timestamp with time zone | 
 state_change     | timestamp with time zone | 
 waiting          | boolean                  | 
 enqueue          | text                     | 
 state            | text                     | 
 resource_pool    | name                     | 
 query_id         | bigint                   | 
 query            | text                     | 
 connection_info  | text                     | 
 unique_sql_id    | bigint                   | 
 trace_id         | text                     | 

select * from pg_stat_activity;

select datname, backend_start, connection_info from pg_stat_activity;

pg9.6
pg14
理解 pg_stat_activity

在 PostgreSQL 中，pg_stat_activity 是一个系统视图，它提供了当前数据库服务器上所有活动会话的实时快照。这个视图包含了关于每个会话的各种信息，包括它们的状态、正在执行的查询、客户端地址、连接时间等。

以下是 pg_stat_activity 视图中一些常用列的解释：

• pid: 会话的进程ID，这是操作系统级别的进程标识符。
• usename: 启动会话的数据库用户名称。
• datname: 会话当前连接的数据库名称。
• state: 会话的状态，如 active（正在执行查询）、idle（空闲）、idle in transaction（在事务中空闲）等。
• query: 当前会话正在执行的 SQL 查询。如果会话处于空闲状态，则此列为空。
• query_start: 查询开始的时间戳。
• xact_start: 事务开始的时间戳。如果会话不在事务中，则此列为空。
• backend_start: 后端进程启动的时间戳。
• client_addr: 连接到数据库服务器的客户端的IP地址。
• client_port: 客户端使用的端口号。
• virtual transaction id: 虚拟事务ID，用于标识事务，即使在崩溃后也能保持一致。
• transaction id: 当前事务的ID。

pg_stat_activity 视图对于监控数据库性能、诊断问题和理解数据库负载非常有用。例如，您可以使用它来识别长时间运行的查询、空闲会话或锁定资源的会话。

常用命令

针对您提供的查询语句，以下是优化建议：

1. 查询每个数据库的连接数：

   SELECT 
     datname AS "Database",
     COUNT(*) AS "Number of connections"
   FROM 
     pg_stat_activity
   WHERE 
     datid IS NOT NULL
   GROUP BY 
     datname
   ORDER BY 
     "Number of connections" DESC;
   

   优化建议：

   • 避免使用 datid IS NOT NULL：pg_stat_activity 视图中的 datid 列始终不为 NULL，因此此条件多余。
   • 简化查询：直接查询 datname 和连接数即可。
     优化后的查询：

   SELECT 
     datname AS "Database",
     COUNT(*) AS "Number of connections"
   FROM 
     pg_stat_activity
   GROUP BY 
     datname
   ORDER BY 
     "Number of connections" DESC;
   

2. 查询客户端地址：

   SELECT datname, usename, client_addr 
   FROM pg_stat_activity 
   WHERE datname != 'postgres' AND usename != 'postgres';
   

   优化建议：

   • 避免硬编码数据库和用户名：硬编码 'postgres' 可能导致遗漏其他系统用户。
   • 使用 NOT IN 替代多个 !=：NOT IN 更适合用于排除多个值。
     优化后的查询：

   SELECT datname, usename, client_addr 
   FROM pg_stat_activity 
   WHERE (datname, usename) NOT IN (('postgres', 'postgres'));
   

3. 查询当前非空闲状态的活动：

   SELECT pid, wait_event_type, wait_event 
   FROM pg_stat_activity 
   WHERE datname != 'postgres' AND usename != 'postgres' AND state != 'idle';
   

   优化建议：

   • 避免硬编码数据库和用户名：同上，避免遗漏其他系统用户。
   • 使用 NOT IN 替代多个 !=：同上，NOT IN 更适合用于排除多个值。
     优化后的查询：

   SELECT pid, wait_event_type, wait_event 
   FROM pg_stat_activity 
   WHERE (datname, usename) NOT IN (('postgres', 'postgres')) 
     AND state != 'idle';
   

4. 查询每个数据库和用户名的连接数：

   SELECT datname, usename, COUNT(*) 
   FROM pg_stat_activity 
   WHERE datname != 'postgres' AND usename != 'postgres' 
   GROUP BY datname, usename;
   

   优化建议：

   • 避免硬编码数据库和用户名：同上，避免遗漏其他系统用户。
   • 使用 NOT IN 替代多个 !=：同上，NOT IN 更适合用于排除多个值。
     优化后的查询：

   SELECT datname, usename, COUNT(*) 
   FROM pg_stat_activity 
   WHERE (datname, usename) NOT IN (('postgres', 'postgres')) 
   GROUP BY datname, usename;
   

其他优化建议：

• 使用 pg_stat_statements 插件：该插件可以提供更详细的查询统计信息，帮助识别性能瓶颈。
• 定期清理连接：长时间未使用的连接可能导致资源浪费，定期清理空闲连接有助于提升性能。
• 监控系统资源：使用系统监控工具（如 top、htop）监控 CPU、内存和磁盘 I/O 使用情况，确保系统资源充足。

通过以上优化，您可以更高效地监控和管理 PostgreSQL 数据库的连接和活动。