PostgreSQL CPU占用高分析,查询IO消耗最高的SQL,查询耗时最长的SQL,查询被锁住的session会话 转发 https://blog.csdn.net/sunny_day_day/article/details/112001481
查看当前活跃的DB session 正在运行的SQL语句(运行时间超过10秒)
SELECT now() - query_start as "runtime", usename,application_name, client_hostname, datname, state, query
FROM pg_stat_activity
WHERE now() - query_start > '10 seconds'::interval
and state!='idle'
ORDER BY runtime DESC;
按 total_time 列出查询,并查看哪个查询在数据库中花费的时间最多
SELECT round(total_time*1000)/1000 AS total_time,query
FROM pg_stat_statements
ORDER BY total_time DESC limit 5;
查看哪些table未及时做vacuum,以及未及时收集统计信息
SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM pg_stat_all_tables
ORDER BY last_autovacuum;
查看有没有被锁的session
select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
推荐创建如下session
pg_stat_tables
CREATE OR REPLACE VIEW pg_stat_tables
AS
WITH s AS (
SELECT *, cast((n_tup_ins + n_tup_upd + n_tup_del) AS numeric) AS total
FROM pg_stat_user_tables
)
SELECT s.schemaname, s.relname, s.relid,
s.seq_scan, s.idx_scan,
CASE WHEN s.seq_scan + s.idx_scan = 0 THEN 'NaN'::double precision
ELSE round(100 * s.idx_scan/(s.seq_scan+s.idx_scan),2) END AS idx_scan_ratio,
s.seq_tup_read, s.idx_tup_fetch,
sio.heap_blks_read, sio.heap_blks_hit,
CASE WHEN sio.heap_blks_read = 0 THEN 0.00
ELSE round(100*sio.heap_blks_hit/(sio.heap_blks_read+sio.heap_blks_hit),2) END AS hit_ratio,
n_tup_ins, n_tup_upd, n_tup_del,
CASE WHEN s.total = 0 THEN 0.00
ELSE round((100*cast(s.n_tup_ins AS numeric)/s.total) ,2) END AS ins_ratio,
CASE WHEN s.total = 0 THEN 0.00
ELSE round((100*cast(s.n_tup_upd AS numeric)/s.total) ,2) END AS upd_ratio,
CASE WHEN s.total = 0 THEN 0.00
ELSE round((100*cast(s.n_tup_del AS numeric)/s.total) ,2) END AS del_ratio,
s.n_tup_hot_upd,
CASE WHEN s.n_tup_upd = 0 THEN 'NaN'::double precision
ELSE round(100*cast(cast(n_tup_hot_upd as numeric)/n_tup_upd as numeric), 2) END AS hot_upd_ratio,
pg_size_pretty(pg_relation_size(sio.relid)) AS "table_size",
pg_size_pretty(pg_total_relation_size(sio.relid)) AS "total_size",
s.last_vacuum, s.last_autovacuum,
s.vacuum_count, s.autovacuum_count,
s.last_analyze, s.last_autoanalyze,
s.analyze_count, s.autoanalyze_count
FROM s, pg_statio_user_tables AS sio