You need to enable JavaScript to run this app.
导航
资源密集活动统计(pg_profile)
最近更新时间:2025.03.11 10:25:16首次发布时间:2025.02.21 00:18:19
我的收藏
有用
有用
无用
无用

pg_profile 是一款可以统计数据库中资源使用情况,用以实现帮助用户来分析优化数据库的目的。

背景信息

pg_profile 基于 PostgreSQL 的自带的统计视图(pg_stat_statements)和社区插件(pg_stat_kcache),完全使用 PL/pgSQL 编写,无需任何外部库或软件。pg_profile 依赖 dblink、pg_stat_statements 和 pg_stat_kcache 插件,其中 pg_stat_kcache 为可选项,启用 pg_stat_kcache 可以提供更多信息收集功能。

前提条件

  • 将 pg_stat_statements 加入到 shared_preload_libraries 参数中。

  • (可选)pg_stat_kcache 加入到 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 库的表中。

1. 准备 Server 数据库。

  1. 创建 Server 数据库。

    CREATE DATABASE server;
    
  2. 连接 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
    ...
    

2. 准备 profile 数据库。

  1. 创建 profile 数据库。

    CREATE DATABASE profile;
    
  2. 连接 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
    ...
    

3. 创建 Server 连接。

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 数据库所在实例的连接地址: |\
||| - 如果 profile 数据库与 server 数据库在同一实例中,则此参数固定配置为 127.0.0.1。 |\
||| - 如果 profile 数据库与 server 数据库在不同实例中,则此参数配置为 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 的连接。

4. 修改 profile 数据库连接的信息,配置密码。

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)

5. 初次进行数据采集。

profile=> SELECT take_sample();
       take_sample       -------------------------
 (local,OK,00:00:01.03)
 (server,OK,00:00:00.99)
(2 rows)

6. 对 server 数据库进行压测后,再次进行采集。

说明

  • 以下命令需要在客户端命令行窗口执行,请确保客户端已安装 PostgreSQL 客户端。
  • pgbench 是在 PostgreSQL 上运行基准测试的简单程序。该命令的更多用法,请参见 PostgreSQL官方文档
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 个规模因子
参数示例值参数说明
-Utest_accountserver 数据库所在实例的高权限账号。
-hpostgresxxxxxx.rds-pg.ivolces.comserver 数据库所在实例的连接地址。
-p5432实例端口。
-s100100表示在pgbench_accounts表中创建 10,000,000 行数据。
-i dbnameserver待进行测试的数据库。
-T300压测 300s
-c100客户端并发进程 100 个
-j2每个客户端线程数

连接 profile 数据库所在实例,再次进行数据采集。

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)

7. 将采集结果生成报告。

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

  • 方式 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