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

如果使用过 oracle，就必须了解 AWR 报告，该报告用于生成特定持续时间的数据库的工作负载性能报告。
在 postgres 中没有生成性能报告的内置函数。 但是有一个名为 pg_profile 的扩展可以用来生成类似的性能报告。
在本文中，我们将解释如何实现这一目标
1.下载安装pg_profile 从github链接下载扩展文件-> https://github.com/zubkov-andrei/pg_profile/releases
复制到服务器并解压缩到以下位置

[root@~]# cd /usr/edb/as12/share/extension/
[root@extension]# ls -ltr *gz
-rw-r--r-- 1 root root 186808 Jun 1 14:06 pg_profile--0.3.6.tar.gz
[root@extension]# tar xzf pg_profile--0.3.6.tar.gz --directory /usr/share/postgresql/10/extension
[root@extension]# ls -ltr
total 3928
-rw-r--r-- 1 root root 2259 Nov 24 2019 pldbgapi--unpackaged--1.1.sql


--Connect with super user and create the extension:

postgres=# CREATE EXTENSION pg_profile;

2.验证参数设置如下：

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

3.如果需要生成远程服务器的性能报告，就需要有 dblink 扩展：

CREATE EXTENSION dblink;

4.验证如下扩展是否已经存在：

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         | 0.3.6   | public     | PostgreSQL load profile repository and report builder
 pg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executed
(14 rows)

5. 验证服务器信息：
   默认情况下，本地服务器将添加到服务器列表中

postgres=# select * from show_servers();
 server_name |          connstr          | enabled | description
-------------+---------------------------+---------+-------------
 local       | dbname=postgres port=5444 | t       |
(1 row)

6. 采样：
   要生成报告，您至少需要两个快照

postgres=# select * from show_samples;
 sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+-------------+-----------------+---------------+----------------+-----------------
(0 rows)

postgres=# select * from take_sample();
 server | result |   elapsed
--------+--------+-------------
 local  | OK     | 00:00:00.48
(1 row)





postgres=# select * from show_samples();
 sample |        sample_time        | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+----------------+-----------------
      1 | 01-JUN-22 14:13:23 +03:00 | t               |               |                |
(1 row)


--- after doing some transaction.


postgres=#  select * from take_sample();
 server | result |   elapsed
--------+--------+-------------
 local  | OK     | 00:00:00.44
(1 row)

postgres=#  select * from show_samples();
 sample |        sample_time        | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+----------------+-----------------
      1 | 01-JUN-22 14:13:23 +03:00 | t               |               |                |
      2 | 01-JUN-22 14:13:56 +03:00 | t               |               |                |
(2 rows)

7. 生成性能报告：
   1 和 2 是快照 ID。

-bash-4.2$ psql -d postgres  -Aqtc "SELECT  get_report('local',1,2)" -o 1st_report.html

如下是产生的性能报告截图

8. 为远程服务器创建性能报告：
如果要在远程服务器上生成 postgres 集群的报告，则需要将其添加到 server list 中。

First you need to add the server.

postgres=# SELECT create_server('elmtest','host=10.20.30.131 dbname=postgres port=5444');
 create_server
---------------
             2
(1 row)

update home目录下.pgpass file，

-bash-4.2$ cat .pgpass
10.20.30.131:5444:*:enterprisedb:elm#912345


postgres=# select * from show_servers();
 server_name |                   connstr                    | enabled | description
-------------+----------------------------------------------+---------+-------------
 elmtest     | host=10.20.30.131 dbname=postgres port=5444  | t       |
 local       | dbname=postgres port=5444                    | t       |
(2 rows)


Now take sample

-- 直接run take_sample(),不带参数，将为所有服务器生成样本。
postgres=#  select * from take_sample();
 server  | result |   elapsed
---------+--------+-------------
 elmtest | OK     | 00:00:00.63
 local   | OK     | 00:00:00.51
(2 rows)

-- 如果需要为特定服务器生成快照，则在take_sample() 中传递该值，例如，

postgres=#  select * from take_sample('elmtest');
 server  | result |   elapsed
---------+--------+-------------
 elmtest | OK     | 00:00:00.63
(1 rows)


-- 显示远程服务器 elmtest 的快照ID：
postgres=#  select * from show_samples('elmtest');
 sample |        sample_time        | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+-----------------+-----------------
      1 | 03-JUN-22 21:55:05 +03:00 | t               |               |                 |
(1 row)


--- 显示所有server的快照ID:

postgres=# select * from show_sample();
 sample |        sample_time        | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+----------------+-----------------
      1 | 01-JUN-22 14:13:23 +03:00 | t               |               |                |
      2 | 01-JUN-22 14:13:56 +03:00 | t               |               |                |
      3 | 01-JUN-22 14:55:35 +03:00 | t               |               |                |
      4 | 01-JUN-22 22:41:08 +03:00 | t               |               |                |
      5 | 01-JUN-22 22:42:18 +03:00 | t               |               |                |
      6 | 03-JUN-22 19:02:35 +03:00 | t               |               |                |
      7 | 03-JUN-22 21:55:05 +03:00 | t               |               |                |
(7 rows)

9.设置快照的保留期：
我们不可能永远保留快照 ID，因为它会占用存储空间。 所以最好将保留期设置为 30 天/60 天。
因此，可以使用以下命令为服务器设置 max_sample_age。

- 30 means 30 days.

postgres=# select set_server_max_sample_age('elmtest',30);
set_server_max_sample_age
---------------------------
1
(1 row)

还有另一个概念称为基线(baseline)，支持在特定持续时间内保留特定范围的快照，然后可以按如下方式创建基线。
注意- - 基线保留将覆盖 max_sample_age 设置

-- Create baseline,

elmtest - server_name,
plm_base1 - baseline_name,
1 and 2 are start and end sample range.
30 - retention period.

postgres=# select create_baseline('elmtest','plm_base1',1,2,30);
create_baseline
-----------------
5
(1 row)

-- Show baselines:


postgres=# select * from show_baselines();
 baseline | min_sample | max_sample |      keep_until_time
----------+------------+------------+---------------------------
 local2   |          1 |          4 | 01-JUL-22 22:44:44 +03:00
 local    |          1 |          3 | 05-JUN-22 15:15:39 +03:00
(2 rows)

postgres=# select * from show_baselines('elmtest');
 baseline  | min_sample | max_sample |      keep_until_time
-----------+------------+------------+---------------------------
 plm_base1 |          1 |          1 | 05-JUL-22 17:59:33 +03:00
(1 row)
-- Modify baseline retention period:

postgres=# select keep_baseline('elmtest','plm_base1',50);
 keep_baseline
---------------
             1
(1 row)

postgres=# select * from show_baselines('elmtest');
 baseline  | min_sample | max_sample |      keep_until_time
-----------+------------+------------+---------------------------
 plm_base1 |          1 |          1 | 25-JUL-22 18:00:52 +03:00
(1 row)
-- Drop a baseline:

postgres=#  select drop_baseline('elmtest','plm_base1');
 drop_baseline
---------------
             1
(1 row)