pg_wait_sampling 插件使用手册

shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling'

pg_wait_sampling.history_size = 5000
pg_wait_sampling.history_period = 10
pg_wait_sampling.profile_period = 10
pg_wait_sampling.profile_pid = on
pg_wait_sampling.profile_queries = top
pg_wait_sampling.profile_max_entries = 100000
pg_wait_sampling.sample_cpu = on

pg_ctl -D $PGDATA restart

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_wait_sampling;

CREATE SCHEMA pgws;
CREATE EXTENSION pg_wait_sampling SCHEMA pgws;

GRANT pg_monitor TO monitor_user;

SELECT c.pid, a.usename, a.state,
       coalesce(c.event_type, 'ON CPU') AS event_type,
       coalesce(c.event, 'ON CPU') AS event,
       substring(a.query, 1, 80) AS query
FROM pg_wait_sampling_current c
LEFT JOIN pg_stat_activity a ON a.pid = c.pid
ORDER BY c.event_type NULLS LAST;

SELECT coalesce(event_type, 'ON CPU') AS event_type,
       coalesce(event, 'ON CPU') AS event,
       sum(count) AS samples,
       round(100.0 * sum(count) / sum(sum(count)) OVER (), 2) AS pct
FROM pg_wait_sampling_profile
GROUP BY 1, 2
ORDER BY samples DESC
LIMIT 15;

SELECT ts, pid,
       coalesce(event_type, 'ON CPU') AS event_type,
       coalesce(event, 'ON CPU') AS event,
       queryid
FROM pg_wait_sampling_history
WHERE ts > now() - interval '30 seconds'
ORDER BY ts DESC, pid
LIMIT 50;

SELECT coalesce(p.event_type, 'ON CPU') AS event_type,
       coalesce(p.event, 'ON CPU') AS event,
       sum(p.count) AS samples,
       substring(s.query, 1, 80) AS query
FROM pg_wait_sampling_profile p
LEFT JOIN pg_stat_statements s ON s.queryid = p.queryid
WHERE p.queryid <> 0
GROUP BY 1, 2, 4
ORDER BY samples DESC
LIMIT 15;

SELECT pg_wait_sampling_reset_profile();
-- 等待或运行负载
SELECT pg_sleep(60);
SELECT coalesce(event_type, 'ON CPU'), coalesce(event, 'ON CPU'), sum(count)
FROM pg_wait_sampling_profile
GROUP BY 1, 2
ORDER BY 3 DESC;

SELECT pg_reload_conf();

DROP EXTENSION pg_wait_sampling;