postgresql 使用 pg_stat_activity获取链接数

最新推荐文章于 2024-06-02 14:46:34 发布

majinbo111

最新推荐文章于 2024-06-02 14:46:34 发布

阅读量531

点赞数

分类专栏： postgreSQL 文章标签： postgresql 获取数据库链接数

本文链接：https://blog.csdn.net/u011944141/article/details/111291112

版权

postgreSQL 专栏收录该内容

64 篇文章 7 订阅

订阅专栏

本文介绍了如何使用`pg_stat_activity`视图来获取PostgreSQL数据库的状态信息，包括后台进程PID、用户、应用名、查询等。同时，展示了如何统计当前数据库的活动连接数，以及通过`EXPLAIN ANALYZE`解析查询计划。这些信息对于数据库性能管理和优化至关重要。

摘要由CSDN通过智能技术生成

pg_stat_activity 获取数据库的状态

 \d+ pg_stat_activity
                                  View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default | Storage  | Description
------------------+--------------------------+-----------+----------+---------+----------+-------------
 datid            | oid                      |           |          |         | plain    |
 datname          | name                     |           |          |         | plain    |
 pid              | integer                  |           |          |         | plain    |
 usesysid         | oid                      |           |          |         | plain    |
 usename          | name                     |           |          |         | plain    |
 application_name | text                     |           |          |         | extended |
 client_addr      | inet                     |           |          |         | main     |
 client_hostname  | text                     |           |          |         | extended |
 client_port      | integer                  |           |          |         | plain    |
 backend_start    | timestamp with time zone |           |          |         | plain    |
 xact_start       | timestamp with time zone |           |          |         | plain    |
 query_start      | timestamp with time zone |           |          |         | plain    |
 state_change     | timestamp with time zone |           |          |         | plain    |
 wait_event_type  | text                     |           |          |         | extended |
 wait_event       | text                     |           |          |         | extended |
 state            | text                     |           |          |         | extended |
 backend_xid      | xid                      |           |          |         | plain    |
 backend_xmin     | xid                      |           |          |         | plain    |
 query            | text                     |           |          |         | extended |
 backend_type     | text                     |           |          |         | extended |
View definition:
 SELECT s.datid,
    d.datname,
    s.pid,
    s.usesysid,
    u.rolname AS usename,
    s.application_name,
    s.client_addr,
    s.client_hostname,
    s.client_port,
    s.backend_start,
    s.xact_start,
    s.query_start,
    s.state_change,
    s.wait_event_type,
    s.wait_event,
    s.state,
    s.backend_xid,
    s.backend_xmin,
    s.query,
    s.backend_type
   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
     LEFT JOIN pg_database d ON s.datid = d.oid
     LEFT JOIN pg_authid u ON s.usesysid = u.oid;

获取数据库链接数

统计sql

SELECT count(1) FROM pg_stat_activity WHERE pid != pg_backend_pid();

解析过程

explain analyze SELECT count(1) FROM pg_stat_activity WHERE pid != pg_backend_pid();
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1.75..1.76 rows=1 width=8) (actual time=0.471..0.471 rows=1 loops=1)
   ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.50 rows=99 width=0) (actual time=0.435..0.455 rows=115 loops=1)
         Filter: (pid <> pg_backend_pid())
         Rows Removed by Filter: 1
 Planning Time: 0.189 ms
 Execution Time: 0.515 ms
(6 rows)

Time: 8.618 ms