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 可以看到这个表的所有字段信息

主要字段包括如下:

PostgreSQL_96  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‘

PostgreSQL_96 pg_stat_activity

上一篇:mysql explain的extra


下一篇:深入理解MySQL 中文高清PDF版下载