使用 pg_profile 在 Postgres 中生成性能报告
下载安装 pg_profile
从github链接下载pg_profile: https://github.com/zubkov-andrei/pg_profile/releases
解压缩到以下位置
# cd $PGHOME/share/extension/
# su - postgress
$ psql -h 127.0.0.1 -d postgres -U postgress
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE EXTENSION pg_profile;
验证参数设置如下：
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'top'
pg_stat_statements.save = on
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all/pl
track_wal_io_timing = on # Since Postgres 14
注意：如果需要生成远程服务器的性能报告，就需要有 dblink 扩展：
postgres=# CREATE EXTENSION dblink;
验证如下扩展是否已经存在：
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
pg_profile | 4.6 | public | PostgreSQL load profile repository and report builder
pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(4 rows)
验证服务器信息：默认情况下，本地服务器将添加到服务器列表中
postgres=# select * from show_servers();
server_name | connstr | enabled | max_sample_age | description
-------------+---------------------------+---------+----------------+-------------
local | dbname=postgres port=5432 | t | |
(1 row)
开始采样：
要生成报告，您至少需要两个快照
postgres=# select show_samples();
show_samples
--------------
(0 rows)
postgres=# select take_sample();
take_sample
------------------------
(local,OK,00:00:01.36)
(1 row)
postgres=# select show_samples();
show_samples
-----------------------------------
(1,"2024-08-28 16:29:34+08",t,,,)
(1 row)
跑5分钟压力测试然后在采样：
$ pgbench -i postgres -- 创建压测用表
$ pgbench -M prepared -r -c 8 -j 4 -T 300 -U postgress -p 5432 -d postgres -l
... ...
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 4
maximum number of tries: 1
duration: 300 s
number of transactions actually processed: 311830
number of failed transactions: 0 (0.000%)
latency average = 7.696 ms
initial connection time = 9.861 ms
tps = 1039.447761 (without initial connection time)
statement latencies in milliseconds and failures:
0.019 0 \set aid random(1, 100000 * :scale)
0.016 0 \set bid random(1, 1 * :scale)
0.015 0 \set tid random(1, 10 * :scale)
0.015 0 \set delta random(-5000, 5000)
0.158 0 BEGIN;
0.226 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.184 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2.476 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
3.702 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.197 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.662 0 END;
压测完成后采样：
postgres=# select take_sample();
take_sample
-----------------------
(local,OK,00:00:01.8)
(1 row)
postgres=# select * from show_samples();
sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+------------------------+-----------------+---------------+----------------+-----------------
1 | 2024-08-28 16:29:34+08 | t | | |
2 | 2024-08-28 16:42:23+08 | t | | |
(2 rows)
生成性能报告：
1 和 2 是快照 ID。
$ psql -d postgres -Aqtc "SELECT get_report('local',1,2)" -o 2024-1-2_report.html
采样生成的报告如下：
[postgress@obocp ~]$ ll
total 10424
-rw-rw-r-- 1 postgress postgress 397055 Aug 28 16:44 2024-1-2_report.html
打开生成的性能报告如下，可以看到包含如下图所示的几方面信息：
