1、慢查询
开启慢查询日志
log_min_duration_statement = 1000 # 执行超过1000ms的语句记录日志
log_statement = 'none' # 可选：不记录所有语句，只记录慢查询
log_duration = off # 可选：不记录每条语句的执行时间
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on # 记录锁等待
log_temp_files = 0 # 记录所有临时文件使用
log_autovacuum_min_duration = 0 # 记录所有autovacuum
auto_explain 插件
-- 1. 加载扩展
shared_preload_libraries = 'auto_explain'
-- 2. 配置参数
auto_explain.log_min_duration = '1s' -- 记录超过1秒的查询计划
auto_explain.log_analyze = on -- 包含实际执行统计
auto_explain.log_buffers = on -- 记录缓冲区使用
auto_explain.log_timing = on -- 记录时间统计
auto_explain.log_triggers = on -- 记录触发器
auto_explain.log_verbose = on -- 详细模式
auto_explain.log_nested_statements = on -- 记录嵌套语句
-- 查看当前运行的慢查询
SELECT
pid,
usename,
application_name,
client_addr,
state,
query_start,
now() - query_start as duration,
query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;
-- 查看历史慢查询统计
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE mean_exec_time > 100 -- 平均执行时间超过100ms
ORDER BY total_exec_time DESC
LIMIT 20;
-- 1. 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2. 配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on
-- 3. 查询慢SQL
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
stddev_exec_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY total_exec_time DESC
LIMIT 20;
2、死锁
postgresql.conf
log_lock_waits = on -- 记录锁等待
deadlock_timeout = 1s -- 死锁检测间隔（默认1s）
log_min_duration_statement = 0 -- 记录所有语句
log_statement = 'none'
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u '
-- 1. 查看当前锁等待
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement,
now() - blocked_activity.query_start AS blocked_duration,
now() - blocking_activity.query_start AS blocking_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- 2. 查看锁等待树
WITH RECURSIVE lock_wait AS (
SELECT
waiting.pid as waiting_pid,
waiting.query as waiting_query,
blocking.pid as blocking_pid,
blocking.query as blocking_query,
1 as depth
FROM pg_locks l1
JOIN pg_stat_activity waiting ON l1.pid = waiting.pid
JOIN pg_locks l2 ON l1.locktype = l2.locktype
AND l1.DATABASE IS NOT DISTINCT FROM l2.DATABASE
AND l1.relation IS NOT DISTINCT FROM l2.relation
AND l1.page IS NOT DISTINCT FROM l2.page
AND l1.tuple IS NOT DISTINCT FROM l2.tuple
AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
AND l1.classid IS NOT DISTINCT FROM l2.classid
AND l1.objid IS NOT DISTINCT FROM l2.objid
AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
AND l1.pid != l2.pid
JOIN pg_stat_activity blocking ON l2.pid = blocking.pid
WHERE NOT l1.granted
UNION ALL
SELECT
w.waiting_pid,
w.waiting_query,
l.pid as blocking_pid,
a.query as blocking_query,
w.depth + 1
FROM lock_wait w
JOIN pg_locks l ON w.blocking_pid = l.pid
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
)
SELECT * FROM lock_wait ORDER BY depth;