性能监控插件 pg_stat_monitor

SHOW shared_preload_libraries;

CREATE EXTENSION pg_stat_monitor;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_stat_monitor';

SELECT pg_stat_monitor_version();

SELECT name, setting, unit, context, vartype, source, min_val, max_val, enumvals
FROM pg_settings
WHERE name LIKE 'pg_stat_monitor.%'
ORDER BY name;

SET pg_stat_monitor.pgsm_track = 'all';
SET pg_stat_monitor.pgsm_normalized_query = on;
SET pg_stat_monitor.pgsm_track_planning = on;

SELECT bucket,
       bucket_start_time,
       datname,
       username,
       application_name,
       queryid,
       pgsm_query_id,
       calls,
       rows,
       mean_exec_time,
       query
FROM pg_stat_monitor
ORDER BY bucket_start_time DESC, calls DESC
LIMIT 20;

SELECT datname,
       username,
       calls,
       total_exec_time,
       mean_exec_time,
       max_exec_time,
       rows,
       query
FROM pg_stat_monitor
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT datname,
       username,
       calls,
       mean_exec_time,
       min_exec_time,
       max_exec_time,
       stddev_exec_time,
       query
FROM pg_stat_monitor
WHERE calls > 0
ORDER BY mean_exec_time DESC
LIMIT 20;

SELECT datname,
       username,
       calls,
       total_exec_time,
       relations,
       query
FROM pg_stat_monitor
WHERE relations @> ARRAY['public.orders']
ORDER BY total_exec_time DESC;

SELECT query, relations
FROM pg_stat_monitor
WHERE relations IS NOT NULL
ORDER BY query;

SELECT datname,
       username,
       query,
       decode_error_level(elevel) AS error_level,
       sqlcode,
       message,
       calls
FROM pg_stat_monitor
WHERE elevel IS NOT NULL
  AND elevel <> 0
ORDER BY bucket_start_time DESC;

SET pg_stat_monitor.pgsm_enable_query_plan = on;
SELECT * FROM your_table WHERE id = 1;

SELECT query, planid, query_plan
FROM pg_stat_monitor
WHERE query_plan IS NOT NULL
ORDER BY bucket_start_time DESC
LIMIT 10;

SET pg_stat_monitor.pgsm_extract_comments = on;

SELECT 1 /* { "application": "order-service", "trace_id": "demo" } */;

SELECT query, comments
FROM pg_stat_monitor
WHERE comments IS NOT NULL
ORDER BY bucket_start_time DESC;

SELECT range();

SELECT bucket, queryid, query
FROM pg_stat_monitor
ORDER BY calls DESC
LIMIT 1;

SELECT *
FROM histogram(<bucket>, <queryid>) AS h(range text, freq int, bar text);

SELECT bucket,
       bucket_start_time,
       count(*) AS sql_count,
       sum(calls) AS total_calls,
       sum(total_exec_time) AS total_exec_time
FROM pg_stat_monitor
GROUP BY bucket, bucket_start_time
ORDER BY bucket_start_time DESC;

SET pg_stat_monitor.pgsm_normalized_query = on;

SELECT * FROM orders WHERE id = 1;
SELECT * FROM orders WHERE id = 2;

SELECT query, calls
FROM pg_stat_monitor
WHERE query LIKE '%orders%'
ORDER BY calls DESC;

SET pg_stat_monitor.pgsm_track = 'all';

SET pg_stat_monitor.pgsm_track = 'top';

SET pg_stat_monitor.pgsm_track_planning = on;

SELECT query,
       plans,
       total_plan_time,
       mean_plan_time,
       calls,
       total_exec_time,
       mean_exec_time
FROM pg_stat_monitor
ORDER BY total_plan_time DESC
LIMIT 20;

SELECT pg_stat_monitor_reset();

GRANT SELECT ON pg_stat_monitor TO PUBLIC;
GRANT EXECUTE ON FUNCTION pg_stat_monitor_internal TO PUBLIC;
REVOKE ALL ON FUNCTION pg_stat_monitor_reset FROM PUBLIC;

REVOKE SELECT ON pg_stat_monitor FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_stat_monitor_internal(boolean) FROM PUBLIC;
GRANT SELECT ON pg_stat_monitor TO pg_read_all_stats;

pg_stat_monitor.pgsm_max = 256MB
pg_stat_monitor.pgsm_query_shared_buffer = 20MB
pg_stat_monitor.pgsm_query_max_len = 2048
pg_stat_monitor.pgsm_max_buckets = 10
pg_stat_monitor.pgsm_bucket_time = 60

pg_stat_monitor.pgsm_track = 'top'
pg_stat_monitor.pgsm_track_utility = on
pg_stat_monitor.pgsm_track_planning = off
pg_stat_monitor.pgsm_normalized_query = on
pg_stat_monitor.pgsm_enable_query_plan = off
pg_stat_monitor.pgsm_extract_comments = off

pg_stat_monitor.pgsm_enable_overflow = off
pg_stat_monitor.pgsm_enable_query_plan = off
pg_stat_monitor.pgsm_extract_comments = off
pg_stat_monitor.pgsm_track = 'top'

SET pg_stat_monitor.pgsm_track_planning = on;
SET pg_stat_monitor.pgsm_enable_query_plan = on;
SET pg_stat_monitor.pgsm_track = 'all';

DROP EXTENSION pg_stat_monitor;

SHOW shared_preload_libraries;

GRANT pg_read_all_stats TO monitor_user;

SELECT name, context, pending_restart
FROM pg_settings
WHERE name LIKE 'pg_stat_monitor.%'
ORDER BY name;

SET pg_stat_monitor.pgsm_enable_query_plan = on;

SET pg_stat_monitor.pgsm_extract_comments = on;