pg阻塞 + pg_top -- select * from pg_stat_activity where pid= pid; = postgresql 运维常用脚本 杀掉进程 - kill VACUUM full bi_dws_kpi_agency

1.查下超过10 s的查询语句

select 
*
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
;

2. 超过10s的查询会被干掉

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
;

 

3. 查询现在的sql

SELECT
pid,
usename,
datname,
query_start,
STATE,
query
FROM
pg_stat_activity
WHERE
query_start >= '2021-08-25 17:46:00'
AND query_start <= '2021-08-25 17:47:50'
AND STATE NOT IN ( 'idle' )
AND pid <> pg_backend_pid ( )
AND query ILIKE'select%'
ORDER BY
query_start;

 

wenji发布的那些文档要找时间多看看了

 

5. 查看当前top SQL

### 先reset

select pg_stat_statements_reset();

　　

SELECT
round(
(
100 * A .total_time / SUM (A .total_time) OVER ()
) :: NUMERIC,
2
) percent,
--a.dbid,
b.datname,
round(A .total_time :: NUMERIC, 2) AS total,
A .calls,
round(A .mean_time :: NUMERIC, 2) AS mean,
A .query
FROM
pg_stat_statements A
INNER JOIN pg_stat_database b ON A .dbid = b.datid
ORDER BY
total_time DESC
LIMIT 5;