Skip to main content
 首页 » 数据库

使用pg_stat_activity视图监控Postgresql

2022年07月19日146fff_TT

当监控或检查Postgresql时,我通常先查看一些系统视图,然后再进行深入分析。本文带你了解pg_stat_activity视图,通过监控信息掌握系统慢查询问题。

pg_stat_activity视图

首先要讨论的是pg_stat_activity,其目的是让你掌握系统此刻正在做什么。

postgres=# \d pg_stat_activity; 
                      视图 "pg_catalog.pg_stat_activity" 
       栏位       |           类型           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+--------- 
 datid            | oid                      |           |          | 
 datname          | name                     |           |          | 
 pid              | integer                  |           |          | 
 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 |           |          | 
 wait_event_type  | text                     |           |          | 
 wait_event       | text                     |           |          | 
 state            | text                     |           |          | 
 backend_xid      | xid                      |           |          | 
 backend_xmin     | xid                      |           |          | 
 query            | text                     |           |          | 
 backend_type     | text                     |           |          | 

pg_stat_activity 主要字段

pg_stat_activity对每个连接返回一行信息。包括数据库对象ID(datid),连接数据库名称,服务该连接的进程ID(pid);此外还包括连接信息(usename,注意没有r)以及用户的内部对象ID(usesysid).

有个字段application_name,有必要注释下,该字段终端用户可自行设置。

postgres=# set application_name to 'tommy_client'; 
SET 
 
postgres=# show application_name; 
 application_name 
------------------ 
 tommy_client 
(1 行记录) 

通过该字段可以解决:对于单个IP的多个连接,作为管理员可以了解具体特定连接正在做什么,仅凭记忆很知道所有SQL。一些客户端友好地设置应用名称,从而很容易看到该连接的目的。一般可设置为应用对应域名,方便识别连接、判断可能的问题。

下面三个字段(client_)表明连接来自哪里。Postgresql展示IP地址、甚至主机名称(如果配置了主机名称)。

backend_start表明连接什么时间启动的;xact_start为事务开始时间,接着是query_start 和 state_change,之前版本仅显示活动查询,如果监控花了很长时间的查询是有意义的。对于现代硬件,OLTP查询通常仅需占用毫秒级时间,则很难破获这些查询的问题。解决办法是同时查看活动查询和连接执行的之前查询。

postgres=# select pid ,query_start ,state_change ,state ,query 
postgres-# from pg_catalog.pg_stat_activity ; 
  pid  |        ,state_change         | state  |                                                  query 
-------+-------------------------------+--------+---------------------------------------------------------- 
  8236 | 2021-04-14 14:55:43.28492+08  | active | select pg_sleep(1000) 
... 
(2 行记录) 

可以看到pg_sleep正在执行,当执行终止时,状态为idle。state_change和query_start两个字段时间差即查询执行时间。pg_stat_activity视图可以查看当前系统正在执行的任务概览,其中新的state_change字段辨别哪些时慢查询。

终止连接

问题是:你发现了慢查询或问题查询,如何结束它?PostgreSQL提供了两个函数:pg_cancel_backend 和 pg_terminate_backend。pg_cancel_backend 函数终止查询任务但保留连接,pg_terminate_backend更直接,直接结束查询对应连接。

结束所有其他用户连接:

select pg_terminate_backend(pid)  
from pg_catalog.pg_stat_activity  
where pid <> pg_backend_pid() and backend_type = 'client backend'; 

如果连接被踢掉,会显示消息:服务器意外地关闭了联接。。。

总结

本文学习了pg_stat_activity视图,详细解释了其中主要字段,以及如何利用视图信息判断当前查询状态,最后解释终止连接的函数用法。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/115708588
阅读延展