平时遇到数据库,查询慢,或者数据库故障问题,怎么办,可以看看数据库相关的一些状态视图,快速定位问题
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,那么该怎么取消,有两种方法
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'