pg_monitor 查询 慢sql + 压测导致CPU高的的解决思路

1. ---查超10秒 汇总 sql

SELECT
datname,
usename,
query,
COUNT (*)
FROM
pg_monitor
WHERE
ts >= '2021-08-07 14:00:00'
AND ts <= '2021-08-07 14:30:00'
AND query NOT LIKE '%autovacuum%'
GROUP BY
datname,
usename,
query
ORDER BY
COUNT (*) DESC;

 

2. ----查询top 耗时

SELECT
*, mean / 1000 AS "单次平均时间（秒）"
FROM
pg_top_day
WHERE
ts >= '2021-08-07 14:00:00'
AND ts <= '2021-08-07 14:30:00'
ORDER BY
percent DESC;

 

3. 杀超过10秒的sql

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
;

 4. 压测导致CPU高的的解决思路

top  -c -> 拿到pid select
explain analyze
增加索引or 修改代码