文档

资源密集活动统计(pg_profile)

更新时间:

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

插件简介

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

前提条件

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

  • 实例大版本为PostgreSQL 10或以上版本。

  • 实例内核小版本为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数据库

  1. 创建server数据库。

    CREATE DATABASE server;
  2. 连接server数据库并创建插件。

    \c server
    CREATE EXTENSION pg_stat_statements;
    CREATE EXTENSION pg_stat_kcache;
    说明

    系统读写信息统计(pg_stat_kcache)插件依赖pg_stat_statements插件,这两个插件均需要在数据库启动时加载,请确保pg_stat_statementspg_stat_kcache已加入到shared_preload_libraries参数中。修改shared_preload_libraries参数方法请参见设置实例参数

  3. 执行\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数据库

  1. 创建profile数据库。

    CREATE DATABASE profile;
  2. 连接profile数据库并创建插件。

    \c profile
    CREATE EXTENSION plpgsql;
    CREATE EXTENSION dblink;
    CREATE EXTENSION pg_profile;
  3. 执行\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
     ...
  4. 创建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下的不同实例中:

    说明

    如何获取实例的内外网地址,请参见查看或修改连接地址和端口

    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的连接。

  5. 修改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!

    账号对应的密码。

  6. 初次进行数据采集。

    SELECT take_sample();

    结果示例:

           take_sample
    -------------------------
     (server,OK,00:00:00.52)
     (local,OK,00:00:00.51)
    (2 rows)
  7. 对server数据库进行压测后,再次进行采集。

    1. 在命令行窗口执行如下命令,对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

      实例端口。

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

      SELECT take_sample();
    3. 查看采集数据。

      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               |               |                 |
  8. 将采集结果生成报告。

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

    • 通过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数据库所在实例的高权限账号> 
  • 本页导读 (1)
文档反馈