1.安装
Postgres用户：
tar zxvf pg_profile--0.3.6.tar.gz -C /usr/pgsql-12/share/extension


注：dblink和pg_stat_statements扩展包安装方式相同，pg_stat_statements生效需重启库。
2.参数配置
vi postgresql.conf
###pg_stat_statements
shared_preload_libraries = 'pg_stat_statements' ##如果该参数需要重启数据库
pg_stat_statements.max = 10000
pg_stat_statements.track = top
track_io_timing = on
track_activity_query_size = 2048
pg_stat_statements.save = on
pg_stat_statements.track_utility = off

###pg_profile
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all

3.创建扩展
postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE SCHEMA profile;
postgres=# CREATE EXTENSION pg_profile SCHEMA profile;

4 创建快照

postgres=# SELECT profile.snapshot() ;
snapshot
----------------------------------------------------------------------------------------------------------------
(local,"bigint out of range +
SQL statement ""UPDATE last_stat_tables ulst +
SET in_sample = true +
FROM ( +
SELECT +
cur.server_id AS server_id, +
cur.sample_id AS sample_id, +
cur.datid AS datid, +
cur.relid AS relid, +
tcur.relid AS toastrelid, +
-- Seq. scanned blocks rank +
row_number() OVER (ORDER BY +
(cur.seq_scan - COALESCE(lst.seq_scan,0)) * cur.relsize + +
(tcur.seq_scan - COALESCE(tlst.seq_scan,0)) * tcur.relsize DESC) scan_rank, +
row_number() OVER (ORDER BY cur.n_tup_ins + cur.n_tup_upd + cur.n_tup_del - +
COALESCE(lst.n_tup_ins + lst.n_tup_upd + lst.n_tup_del, 0) + +
COALESCE(tcur.n_tup_ins + tcur.n_tup_upd + tcur.n_tup_del, 0) - +
COALESCE(tlst.n_tup_ins + tlst.n_tup_upd + tlst.n_tup_del, 0) DESC) dml_rank, +
row_number() OVER (ORDER BY cur.n_tup_upd+cur.n_tup_del - +
COALESCE(lst.n_tup_upd + lst.n_tup_del, 0) + +
COALESCE(tcur.n_tup_upd + tcur.n_tup_del, 0) - +
COALESCE(tlst.n_tup_upd + tlst.n_tup_del, 0) DESC) vacuum_dml_rank, +
row_number() OVER (ORDER BY +
cur.n_dead_tup / NULLIF(cur.n_live_tup+cur.n_dead_tup, 0) +
DESC NULLS LAST) dead_pct_rank, +
row_number() OVER (ORDER BY +
cur.n_mod_since_analyze / NULLIF(cur.n_live_tup, 0) +
DESC NULLS LAST) mod_pct_rank, +
-- Read rank +
row_number() OVER (ORDER BY +
cur.heap_blks_read - COALESCE(lst.heap_blks_read,0) + +
cur.idx_blks_read - COALESCE(lst.idx_blks_read,0) + +
cur.toast_blks_read - COALESCE(lst.toast_blks_read,0) + +
cur.tidx_blks_read - COALESCE(lst.tidx_blks_read,0) DESC) read_rank, +
-- Page processing rank +
row_number() OVER (ORDER BY cur.heap_blks_read+cur.heap_blks_hit+cur.idx_blks_read+cur.idx_blks_hit++
cur.toast_blks_read+cur.toast_blks_hit+cur.tidx_blks_read+cur.tidx_blks_hit- +
COALESCE(lst.heap_blks_read+lst.heap_blks_hit+lst.idx_blks_read+lst.idx_blks_hit+ +
lst.toast_blks_read+lst.toast_blks_hit+lst.tidx_blks_read+lst.tidx_blks_hit, 0) DESC) gets_rank, +
-- Vacuum rank +
row_number() OVER (ORDER BY cur.vacuum_count - COALESCE(lst.vacuum_count, 0) + +
cur.autovacuum_count - COALESCE(lst.autovacuum_count, 0) DESC) vacuum_rank, +
row_number() OVER (ORDER BY cur.analyze_count - COALESCE(lst.analyze_count,0) + +
cur.autoanalyze_count - COALESCE(lst.autoanalyze_count,0) DESC) analyze_rank +
FROM +
-- main relations diff +
last_stat_tables cur JOIN last_stat_database dbcur USING (server_id, sample_id, datid) +
LEFT OUTER JOIN last_stat_database dblst ON +
(dblst.server_id, dblst.datid, dblst.sample_id, dblst.stats_reset) = +
(dbcur.server_id, dbcur.datid, dbcur.sample_id - 1, dbcur.stats_reset) +
LEFT OUTER JOIN last_stat_tables lst ON +
(lst.server_id, lst.sample_id, lst.datid, lst.relid) = +
(dblst.server_id, dblst.sample_id, dblst.datid, cur.relid) +
-- toast relations diff +
LEFT OUTER JOIN last_stat_tables tcur ON +
(tcur.server_id, tcur.sample_id, tcur.datid, tcur.relid) = +
(dbcur.server_id, dbcur.sample_id, dbcur.datid, cur.reltoastrelid) +
LEFT OUTER JOIN last_stat_tables tlst ON +
(tlst.server_id, tlst.sample_id, tlst.datid, tlst.relid) = +
(dblst.server_id, dblst.sample_id, dblst.datid, lst.reltoastrelid) +
WHERE +
(cur.server_id, cur.sample_id, cur.in_sample) = +
(sserver_id, s_id, false) +
AND cur.relkind IN ('r','m')) diff +
WHERE +
least( +
scan_rank, +
dml_rank, +
dead_pct_rank, +
mod_pct_rank, +
vacuum_dml_rank, +
read_rank, +
gets_rank, +
vacuum_rank, +
analyze_rank +
) <= topn +
AND (ulst.server_id, ulst.sample_id, ulst.datid, ulst.in_sample) = +
(diff.server_id, diff.sample_id, diff.datid, false) +
AND (ulst.relid = diff.relid OR ulst.relid = diff.toastrelid)"" +
PL/pgSQL function sample_dbobj_delta(jsonb,integer,integer,integer,boolean) line 123 at SQL statement +
PL/pgSQL function take_sample(integer,boolean) line 984 at assignment +
PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment +
SQL function ""take_sample"" statement 1 +
SQL function ""snapshot"" statement 1 +
",00:00:07.24)
(1 row)