pg_blocking pg_monitor (转) postgresql表死锁问题的排查方式 阻塞分析 慢SQL

-1.  pg_blocking # 找出精确时间段阻塞的SQL## 在navicat执行，复制结果，第一个就是最开始出现阻塞的SQL

select  * from pg_blocking  where d_time >'2022-01-26 07:50:02+08'  and d_time < '2022-01-26 07:53:02+08' ;    

 

select --*
pg_terminate_backend(pid)
From pg_stat_activity where query_start<=now()- interval'10 sec' and state not in ('idle') 
and pid<>pg_backend_pid() and query ilike 'select%'
order by query_start
;

 

0. pg_monitor # 查出这段时间超过10秒的SQL

---- 查出这段时间超过10秒的SQL
SELECT  ts,
        datname,
        usename,
        query,
        COUNT (*)
FROM
        pg_monitor
WHERE
        ts >= '2022-01-26 07:50:00'
AND ts <= '2022-01-26 07:53:00'
AND query NOT LIKE '%autovacuum%'
GROUP BY
        datname,
        usename,
        query,
                ts
ORDER BY
        COUNT (*) DESC;


----查询top 耗时
SELECT
        *, mean / 1000 AS "单次平均时间（秒）"
FROM
        pg_top_day
WHERE
        ts >= '2021-06-09 08:00:00'
AND ts <= '2021-06-09 22:00:00'
ORDER BY
        percent DESC;                

 

 

1.查询激活的执行中的sql,查看有哪些更新update的sql。

例如查询执行时间超过1秒的SQL  

select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ; 

postgres=# select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start;

select *from pg_stat_activity where pid<>pg_backend_pid() and state not in ('idle');

select *
from pg_stat_activity
where state = 'active';

2. 查询表中存在的锁

select a.locktype, a.database, a.pid, a.mode, a.relation, b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
where lower(b.relname) = 'h5_game';

3. 杀掉死锁进程

select pg_terminate_backend(pid)
from pg_stat_activity
where state = 'active'
and pid != pg_backend_pid()
--and pid = 14172
and pid in (select a.pid
from pg_locks a
join pg_class b on a.relation = b.oid
where lower(b.relname) = 'news_content')

参考： postgresql表死锁问题的排查方式 - PostgreSQL - 服务器之家 (zzvips.com)

 

4.  看cpu指标  iowait  top  --  你看看io有降下来么  去数据库服务器看

 

 

ps -ef|grep postgres|grep -I wait

iotop              ---------------------------      看一下哪个进程比较耗费io

表膨胀

数据量大

VACUUM full bi_dws_kpi_agency      --   VACUUM full 表名

 vacuum full（阻塞读写)