使用 pg_profile 在 Postgres 中生成性能报告

使用 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

打开生成的性能报告如下，可以看到包含如下图所示的几方面信息：