pg_profile 是一款可以统计数据库中资源使用情况，用以实现帮助用户来分析优化数据库的目的。
pg_profile 基于 PostgreSQL 的自带的统计视图（pg_stat_statements）和社区插件（pg_stat_kcache）， 完全使用 PL/pgSQL 编写，无需任何外部库或软件。该插件依赖于 pg_stat_statements 和 pg_stat_kcache。pg_profile 为必需，使用 pg_profile 必须启用 pg_stat_statements 插件；pg_stat_kcache 为可选项，启用 pg_stat_kcache 可以提供更多信息收集功能。
将 pg_stat_statements 加入到 shared_preload_libraries 参数中。
（可选）将 pg_hint_plan 加入到 shared_preload_libraries 参数中。
存储采样信息将会占用磁盘空间。pg_profile 具备自动清理机制，需注意数据保存。
采样较为耗时，无需频繁采样，推荐采样频率为每小时 1~2 次。
创建插件
CREATE EXTENSION pg_profile;
删除插件
DROP EXTENSION pg_profile;
pg_profile 插件在使用过程中需要两个数据库进行协同，一个数据库为被采样的数据库，一个数据库用于存储采集的统计信息。 这两个数据库可以创建在不同的 PostgreSQL 实例中，也可创建在同一 PostgreSQL 实例中。下文以 server 数据库指代被采样的数据库，以 profile 数据库指代存储统计信息的数据库，对 pg_profile 插件的使用过程进行说明。
在采样时，profile 数据库会向 server 数据库发出一个请求，server 数据库收到请求后采样自己的信息，再将采样结果返回给 profile，profile 将收到的结果存储在 profile 库的表中。
创建 Server 数据库。
CREATE DATABASE server;
连接 Server 数据库并创建插件。
\c server CREATE EXTENSION pg_stat_statements; CREATE EXTENSION pg_stat_kcache; server=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pg_stat_kcache | 2.2.3 | public | Kernel statistics gathering pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language ...
创建 profile 数据库。
CREATE DATABASE profile;
连接 profile 数据库并创建插件。
\c profile CREATE EXTENSION plpgsql; CREATE EXTENSION dblink; CREATE EXTENSION pg_profile; postgres=> \c profile You are now connected to database "profile" as user "test_account". profile=> \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.4 | public | PostgreSQL load profile repository and report builder plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language ...
profile=> SELECT create_server('server', 'host=$server数据库所在实例的连接地址 dbname=server port=$server数据库所在实例的端口号 user=$server数据库的高权限账号 password=$账号对应的密码'); create_server --------------- 2 (1 row)
| 参数 | 示例值 | 参数说明 |
|---|---|---|
| server | server | 连接名，自定义。 |
host | 127.0.0.1 | Server 数据库所在实例的连接地址：
|
| port | 5432 | Server 数据库所在实例的端口。 |
| dbname | server | Server 端的数据库名。 |
| user | test_account | Server 数据库所在实例的高权限账号。 |
| password | xxxxxx | 账号对应的密码。 |
结果示例：
profile=> select show_servers(); show_servers --------------------------------------------------------------------------------------- (local,"dbname=profile port=5432",t,,) (server,"host=127.0.0.1 dbname=server port=5432 user=test_account password=xxxxxx",t,,) (2 rows)
说明
创建 server 连接后，会自动创建一个 profile 数据库的连接，即上述结果示例中的名为 local 的连接。
profile=> SELECT set_server_connstr('local','host=127.0.0.1 dbname=profile port=5432 user=test_account password=xxxxxx'); set_server_connstr -------------------- 1 (1 row) profile=> select show_servers(); show_servers ------------------------------------------------------------------------------------------------ (server,"host=127.0.0.1 dbname=server port=5432 user=test_account password=xxxxxx",t,,) (local,"host=127.0.0.1 dbname=profile port=5432 user=test_account password=xxxxxx",t,,) (2 rows)
profile=> SELECT take_sample(); take_sample ------------------------- (local,OK,00:00:01.03) (server,OK,00:00:00.99) (2 rows)
说明
pgbench -h postgresxxxxxx.rds-pg.ivolces.com -p 5432 -U test_account -c 100 -j 2 -T 300 -s 100 -i server --压测 100 客户端，每个客户端 2 个线程，压测 300s, 数据量为 100 个规模因子
| 参数 | 示例值 | 参数说明 | | :-- | :-- | :-- | | \-U | test\_account | server 数据库所在实例的高权限账号。 | | \-h | postgresxxxxxx.rds-pg.ivolces.com | server 数据库所在实例的连接地址。 | | \-p | 5432 | 实例端口。 | | \-s | 100 | 100表示在pgbench\_accounts表中创建 10,000,000 行数据。 | | \-i dbname | server | 待进行测试的数据库。 | | \-T | 300 | 压测 300s | | \-c | 100 | 客户端并发进程 100 个 | | \-j | 2 | 每个客户端线程数 | 连接 profile 数据库所在实例，再次进行数据采集。 ```sql profile=> SELECT take_sample(); take_sample ------------------------ (server,OK,00:00:00.5) (local,OK,00:00:00.39) (2 rows) profile=> SELECT * FROM show_samples('server'); sample | sample_time | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset --------+------------------------+-----------------+---------------+-----------------+----------------- 1 | 2024-11-17 22:14:45+08 | t | | | 2 | 2024-11-17 22:15:08+08 | t | | | 3 | 2024-11-18 19:27:40+08 | t | | | (3 rows) ```
您可以通过如下两种方式将采集结果导出到本地。
方式 1：通过 psql 在 profile 数据库中执行如下命令：
\o report_1_2.html SELECT get_report('server',1,2);
方式 2：在本地客户端命令行中执行如下命令：
$psql -Aqtc "SELECT get_report('server',1,2)" -o report_server_1_2.html -d profile -h xxxxxx -p 5432 -U test_account Password for user test_account: $ ls -l total 316 -rw-r--r-- 1 postgres postgres 322748 Nov 17 22:16 report_server_1_2.html
报告样例：report_server_1_2.html。
更多信息，请参见 GitHub - zubkov-andrei/pg_profile at 4.4。