如何使用pg_profile插件,有哪些注意事项和示例 - 云数据库 RDS

RDS PostgreSQL支持使用pg_profile插件来统计数据库中资源密集型活动，用于分析优化数据库。

插件简介

此扩展基于PostgreSQL的统计视图（pg_stat_statements）和社区插件（pg_stat_kcache），完全使用PL/pgSQL编写，无需任何外部库或软件，配合pg_cron插件即可实现资源密集活动的统计。

前提条件

RDS PostgreSQL实例需满足以下要求：

实例大版本为RDS PostgreSQL 10或以上版本。
说明
暂不支持RDS PostgreSQL 17。
实例内核小版本为20230830或以上。
重要
20230830内核小版本之前已支持此插件，但为了规范插件管理，提升RDS PostgreSQL在插件侧的安全防护，RDS计划在内核版本迭代中陆续对部分存在安全风险的插件进行优化，部分插件在低内核小版本无法创建，更多信息，请参见【产品/功能变更】RDS PostgreSQL限制创建插件说明。
- 如果您的实例内核小版本低于20230830，且已经使用了此插件，则不影响使用。
- 如果您首次创建或重新创建此插件，请升级内核小版本到最新。
已创建高权限账号。

注意事项

该插件必须先启用pg_stat_statements插件，同时可以启用pg_stat_kcache插件来提供更多信息收集功能。
存储采样信息将会占用磁盘空间，该插件有自动清理机制，请注意数据保存。
一次采样较为耗时，无需频繁采样，推荐频率为每小时1~2次。

创建和删除插件

说明

推荐使用高权限账号可以执行如下命令。

创建插件
```
CREATE EXTENSION pg_profile;
```
删除插件
```
DROP EXTENSION pg_profile;
```

使用示例

本示例需要分别创建server数据库和profile数据库。

在采样时，profile数据库会向server数据库发出一个请求，server数据库收到请求后采样自己的信息，再将采样结果返回给profile，profile将收到的结果存储在表中。这样就可以从profile数据获取其他数据库的统计信息。

说明

profile数据库和server数据库可以创建在不同RDS PostgreSQL实例中，也可创建在同一实例中。

server数据库

创建server数据库。
```
CREATE DATABASE server;
```
连接server数据库并创建插件。
```
\c server
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;
```
说明
系统读写信息统计（pg_stat_kcache）插件依赖pg_stat_statements插件，这两个插件均需要在数据库启动时加载，请确保pg_stat_statements和pg_stat_kcache已加入到shared_preload_libraries参数中。修改shared_preload_libraries参数方法请参见设置实例参数。

执行\dx查看插件创建详情。

        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_kcache     | 2.2.1   | public     | Kernel statistics gathering
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 ...

profile数据库

创建profile数据库。
```
CREATE DATABASE profile;
```

连接profile数据库并创建插件。

\c profile
CREATE EXTENSION plpgsql;
CREATE EXTENSION dblink;
CREATE EXTENSION pg_profile;

执行\dx查看插件创建详情。

    Name    | Version |   Schema   |                         Description
------------+---------+------------+--------------------------------------------------------------
 dblink     | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_profile | 4.0     | public     | PostgreSQL load profile repository and report builder
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 ...

创建server连接。

SELECT create_server('server', 'host=<server数据库所在实例的连接地址> dbname=server port=<端口号> user=<server数据库的高权限账号> password=<账号对应的密码>');

参数	示例值	参数说明
server	server	连接名，自定义。
host	127.0.0.1	server数据库所在实例的连接地址。如果profile数据库与server数据库在同一实例中，则此参数固定配置为`127.0.0.1`。如果profile数据库与server数据库在相同VPC下的不同实例中，则此参数配置为server数据库所在实例的内网地址。如果profile数据库与server数据库在不同VPC下的不同实例中： profile数据库所在实例需要配置NAT网关并绑定公网IP。 server数据库所在实例需要开通外网地址，此参数配置为server数据库所在实例的外网地址。说明如何获取实例的内外网地址，请参见查看或修改连接地址和端口。
dbname	server	数据库名。
port	3002	server数据库所在实例的端口。如果profile数据库与server数据库在同一实例，此参数通过SQL命令`SHOW PORT;`查询获取。如果profile数据库与server数据库在相同VPC下的不同实例中，则此参数配置为server数据库所在实例的内网端口。如果profile数据库与server数据库在不同VPC下的不同实例中，则此参数配置server数据库所在实例的公网端口。
user	test_user	server数据库所在实例的高权限账号。
password	T123456!	账号对应的密码。

结果示例：

                                     show_servers
---------------------------------------------------------------------------------------
 (local,"dbname=profile port=3002",t,)
 (server,"host=127.0.0.1 dbname=server port=3002 user=test_user password=****",t,)
(2 rows)

说明

创建server连接后，会自动创建一个profile数据库的连接，即上述结果示例中的名为local的连接。

修改profile数据库连接信息，配置密码。

SELECT set_server_connstr('local','host=127.0.0.1 dbname=profile port=3002 user=<profile数据库的高权限账号> password=<账号对应的密码>');

参数	示例值	参数说明
server	local	连接名，无需修改。
host	127.0.0.1	固定为127.0.0.1。
dbname	profile	数据库名。
port	3002	此参数通过SQL命令`SHOW PORT;`查询获取。
user	test_user	profile数据库所在实例的高权限账号。
password	T123456!	账号对应的密码。

初次进行数据采集。

SELECT take_sample();

结果示例：

       take_sample
-------------------------
 (server,OK,00:00:00.52)
 (local,OK,00:00:00.51)
(2 rows)

对server数据库进行压测后，再次进行采集。

在命令行窗口执行如下命令，对server数据库进行压测。

说明

如下命令需要在命令行窗口执行，请确保已安装PostgreSQL客户端，pgbench是在PostgreSQL上运行基准测试的简单程序。该命令的更多用法，请参见PostgreSQL官方文档。

pgbench -s 100 -i server -U test_user -h pgm-****.pg.rds.aliyuncs.com -p 5432

参数	示例值	参数说明
-s	100	100表示在pgbench_accounts表中创建10,000,000行数据。
-i dbname	server	待进行测试的数据库。
-U	test_user	server数据库所在实例的高权限账号。
-h	pgm-****.pg.rds.aliyuncs.com	server数据库所在实例的连接地址。
-p	5432	实例端口。

连接profile数据库所在实例，再次进行数据采集。
```
SELECT take_sample();
```

查看采集数据。

SELECT * FROM show_samples('server');

结果示例如下：

 sample |      sample_time       | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+------------------------+-----------------+---------------+-----------------+-----------------
      1 | 2022-09-30 02:14:07+00 | t               |               |                 |
      2 | 2022-09-30 02:16:59+00 | t               |               |                 |
      3 | 2022-09-30 02:17:58+00 | t               |               |                 |

将采集结果生成报告。

您可以通过如下两种方式将采集结果导出到本地。

通过psql在profile数据库中执行如下命令：
```
\o report_2_3.html
SELECT get_report('server',2,3);
```

在本地命令行中执行如下命令：

psql -Aqtc "SELECT profile.get_report('server',2,3)" -o report_server_2_3.html -d profile -h <profile数据库所在实例的连接地址> -p 5432 -U <profile数据库所在实例的高权限账号>