PostgreSQL_96 pg_stat_activity

平时遇到数据库，查询慢，或者数据库故障问题，怎么办，可以看看数据库相关的一些状态视图，快速定位问题

pg_stat_activity 是postgrsql 实例维护的一个进程相关的视图，是实时变化的。

1、pg_stat_activity表（9.6 版本之后 pg_stat_activity 视图的 waiting 字段被 wait_event_type 和 wait_event 字段取代，这两个字段分别代表等待事件的类型、等待事件名称）

使用 SELECT * FROM pg_stat_activity 可以看到这个表的所有字段信息

主要字段包括如下：

 

 

 一般关注的属性主要包括：datname，pid，usename，application_name，client_addr，client_port，backend_start，xact_start，query_start，state_change，wait_event，state ，query

datname：数据库名称，比如：postgres

pid：进程id

usename:数据库登录账号

application_name:登录客户端的类型，比如：DBeaver 6.2.0 - Main 客户端 或者 PostgreSQL JDBC Driver 数据库驱动代码客户端

client_addr：客户端ip

client_port：客户端端口

backend_start：连接创建时间

xact_start：事务开始时间，null表示没有用到事务，如果当前查询是其第一个事务，那么就是query_start一致

query_start：如果state是active,当前查询开始时间点;如果state不是active，那么就是上一次查询开始时间

state_change：state上次改变时间

wait_event和wait_event_type 

wait_event_type 主要分类四类：

• LWLockNamed：表示backend后台进程等待某种特定的轻量级锁；
• LWLockTranche：表示backend后台进程等待一组相关轻量级锁；
• Lock：表示backend后台进程等待重量级的锁，通常是指 relation、tuple、page、transactionid 等子类型锁；
• BufferPin：表示server process 后台进程等待 buffer pin，手册上解释为 Waiting to acquire a pin on a buffer

官网地址：https://www.postgresql.org/docs/9.6/monitoring-stats.html#WAIT-EVENT-TABLE

 

state ：active：表示当前用户正在执行查询等操作；idle：表示当前用户空闲； idle in transaction:表示当前用户在事务中；idle in transaction (aborted)： 表示当前用户在事务中，但是已经发生错误；

query：当前执行状态关联的sql

 

使用 查询慢sql语句：

select 'select pg_cancel_backend('|| a0.pid || ');' as cancel_pid,
       'select pg_terminate_backend('|| a0.pid || ');' as terminate_pid,
       xact_start,round(extract(epoch FROM (now()-xact_start))::numeric,1) as xact_second,
       query_start,round(extract(epoch FROM (now()-query_start))::numeric,1) as query_second,
       datname, pid, usename, application_name, client_addr, client_port, 
       xact_start, query_start, 
       state_change,wait_event_type,wait_event, state, backend_xid, backend_xmin, query
  from pg_stat_activity a0
 where 1=1  
   and a0.state<>'idle'
   and (a0.backend_xid is not null or a0.backend_xmin is not null) 
 order by now()-xact_start; 

now() - xact_start 是指事务截至当前已运行时间。
now() - query_start 是指query截至当前已运行时间。

慢sql 可以使用explian 查看下原因

如果遇到长时间执行的sql，那么该怎么取消，有两种方法

有两个函数可以完成这个功能：

1）pg_cancel_backend(pid):取消一个正在执行的SQL；

2）pg_terminate_backend(pid):终止一个后台服务进程，同时释放此后台服务进程的资源。

这两个函数的区别是：pg_cancel_backend()函数实际上是给正在执行的SQL任务配置一个取消标志，正在执行的任务是在合适的时候检测到此标志后主动退出；但如果这个任务没有主动检测到这个标志，则该任务就无法正常退出，这时需要使用pg_terminate_backend()命令在终止SQL的执行。

 

2.进程事件查看：

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;

 pid  | wait_event_type |  wait_event
------+-----------------+---------------
 2540 | Lock            | relation
 6644 | LWLockNamed     | ProcArrayLock
(2 rows)

3、查询是否锁表

select oid from pg_class where relname='可能锁表了的表'
select pid from pg_locks where relation='上面查出的oid'