pg_profile: 用法

三月 11, 2024

摘要：在本节中，您将学习如何在 PostgreSQL 中使用 pg_profile。

目录

设置扩展参数

您可以在 postgresql.conf 中定义扩展参数。默认值：

  • pg_profile.topn = 20 - 在每个排序的报告表中，要报告的排列靠前的对象（语句、关系等）的数量。此外，该参数还会影响样本的大小 - 要在报表中显示的对象越多，我们需要在样本中保留的对象也越多。
  • pg_profile.max_sample_age = 7 - 样本的保留时间（以天为单位）。保留时长超出 pg_profile.max_sample_age 天数的样本，会在下一次 take_sample() 调用时被自动删除。
  • pg_profile.track_sample_timings = off - 当此参数设为 on 时，pg_profile 会跟踪详细的采样时间。
  • pg_profile.max_query_length = 20000 - 报表中的查询长度限制。报表中的所有查询都会截断为此长度。该设置不会影响查询文本收集 - 在采样期间会收集完整的查询文本，因此可被获取。

管理服务器对象

在安装后，扩展会创建一个启用的本地服务器 - 这是针对安装了 pg_profile 扩展的整个实例。

服务器管理函数：

  • create_server(server name, server_connstr text, server_enabled boolean = TRUE, max_sample_age integer = NULL, description text = NULL) 创建一个新的服务器描述。 函数参数：

    • server - 服务器名称（必须是唯一的）
    • server_connstr - 服务器连接字符串
    • enabled - 服务器启用标志。如果设置为 TRUE，则服务器将包含在普通的 take_sample() 调用中
    • max_sample_age - 服务器样本保留参数，会覆盖服务器中全局的 pg_profile.max_sample_age 设置
    • description - 服务器描述文本。会包含在报告中

  • drop_server(server name) 删除一个服务器及其所有样本。

  • enable_server(server name) 在普通的 take_sample() 调用中包含服务器。

  • disable_server(server name) 从普通的 take_sample() 调用中排除服务器。

  • rename_server(server name, new_name name) 重命名一个服务器。

  • set_server_max_sample_age(server name, max_sample_age integer) 为一个服务器设置新的保留期（以天为单位）。max_sample_age 是整数值。要重置一个服务器的 max_sample_age 设置，可将其设置为 NULL。

  • set_server_db_exclude(server name, exclude_db name[]) 给一个服务器设置排除数据库列表。可在无法连接到实例中的某些数据库的情况下使用（例如 RDS 实例）。

  • set_server_connstr(server name, new_connstr text) 为一个服务器设置新的连接字符串。

  • set_server_description(server name, description text) 设置新的服务器描述。

  • show_servers() 列出现有的服务器。

服务器创建示例：

SELECT profile.create_server('omega','host=name_or_ip dbname=postgres port=5432');

关系大小的稀散收集

PostgreSQL 关系大小函数可能需要相当长的时间，来收集数据库中所有关系的大小。此外，这些函数还需要获取关系上的 AccessExclusiveLock。但是，每日进行的关系大小收集，对您来说可能已经足够了。在进行采样的过程中，pg_profile 能够根据服务器大小收集策略的设置，跳过关系大小收集。策略定义为一个每日时间窗口，以允许关系大小的收集，这是两次采样之间的一个最小间隙，用来收集关系大小。因此，当定义了大小收集策略时，只有当采样发生在一个时间窗口内，并且之前带有有大小的样本早于间隙时，采样函数才会收集关系大小。仅当报告是以收集了大小的样本为界时，报告中才会提供关于增长最快的表/索引的部分。进一步参考，请参阅 get_report 函数描述中的 with_growth 参数。函数 set_server_size_sampling 定义大小收集策略：

  • set_server_size_sampling(server name, window_start time with time zone = NULL, window_duration interval hour to second = NULL, sample_interval interval day to minute = NULL)
    • server - 服务器名称
    • window_start - 大小收集窗口的开始时间
    • window_duration - 大小收集窗口的持续时长
    • sample_interval - 两个收集大小的样本之间的最小时间间隔

示例：

SELECT set_server_size_sampling('local','23:00+03',interval '2 hour',interval '8 hour');

函数 show_servers_size_sampling 显示所有服务器定义的大小收集策略：

postgres=# SELECT * FROM show_servers_size_sampling();
 server_name | window_start | window_end  | window_duration | sample_interval
-------------+--------------+-------------+-----------------+-----------------
 local       | 23:00:00+03  | 01:00:00+03 | 02:00:00        | 08:00:00

当您在缺少关系大小数据的样本之间构建报表时，报表中的关系增长部分将基于 pg_class.relpages 的数据。但是，报告生成函数的 with_growth 参数，可将报告边界扩展到最接近的收集了关系大小数据的样本，以让增长数据更加准确。

样本

每个样本会包含自上一个样本以来，关于数据库业务负载情况的统计信息。

样本函数

  • take_sample() 函数 take_sample() 会为所有启用的服务器收集一个样本。服务器样本会被串行地逐个采集。函数会返回一个表：

    server      name,
result      text,
elapsed     interval

    其中：

    • server 是一个服务器的名称
    • result 是采样的结果。如果采样成功，则可能是 ‘OK’，而在采样出现异常时，会包含错误的信息
    • elapsed 是为服务器进行采样所花费的时间。这个返回值使得，可以容易地通过 SQL 查询控制样本创建。

  • take_sample_subset([sets_cnt integer], [current_set integer]) 由于串行化采样的机制，处理 take_sample() 函数可能需要相当长的时间。函数 take_sample_subset() 可为已启用的服务器列表中的子集进行采样。这让并行采样变得很方便。sets_cnt 是子集中的服务器数量。current_set 是要处理的子集，取 0 到 sets_cnt - 1（包含上边界）之间的值。函数返回一个表：

    server      name,
result      text,
elapsed     interval

    其中：

    • server 是一个服务器的名称
    • result 是采样的结果。如果采样成功，则可能是 ‘OK’，而在采样出现异常时，会包含错误的信息
    • elapsed 是为服务器进行采样所花费的时间

  • take_sample(server name [, skip_sizes boolean]) 将为指定的服务器收集一个样本。例如，当您想要使用不同的采样频率，或者想要在一个服务器上进行显式采样时，可使用它。

    • server - 要采样的服务器的名称
    • skip_sizes - 覆盖服务器的关系大小收集策略。仅当 skip_size 参数省略或设置为 null 时，策略才适用。skip_size 参数设置为 false 时，会让采集的样本带有关系大小，而设为 true 会导致在采样期间跳过关系大小的收集。

  • show_samples([server name,] [days integer]) 返回一个表，其中包含一个服务器（如果省略 server，则会假定为本地服务器），在最近 days 天数范围内的现有样本。如果省略 days，则会包含现有的所有样本：

    sample            integer,
sample_time       timestamp (0) with time zone,
sizes_collected   boolean,
dbstats_reset     timestamp (0) with time zone,
clustats_reset    timestamp (0) with time zone,
archstats_reset   timestamp (0) with time zone

    其中：

    • sample 是一个样本标识符
    • sample_time 是此样本的采集时间
    • sizes_collected 表示此样本中是否收集了所有关系的大小
    • dbstats_reset、clustats_reset 和 archstats_reset 通常为 null，但是，如果自上一次采样以来，pg_stat_database、pg_stat_bgwriter 和 pg_stat_archiver 的统计信息发生过重置，这些字段将会包含重置时间。样本收集函数还支持服务器存储库 - 它会按照保留策略的设置，删除过时的样本和基线。

  • delete_samples([server_name name] [, start_id integer, end_id integer]) 函数 delete_samples() 可用于手动删除指定服务器的样本。

    • server_name - 要删除样本的服务器的名称。如果省略，则使用名为 “local” 的服务器
    • start_id, end_id - 要删除样本的时间范围间隔（包含边界）的边界标识。如果省略边界标识，此函数会删除服务器的所有样本

  • delete_samples(server_name name, time_range tstzrange) 您可以使用此函数来删除指定时间范围内包含的所有样本

    • server_name - 要删除样本的服务器的名称。
    • time_range - 要删除的样本的时间范围

采样

您必须创建至少 2 个样本，才能在第 1 个和第 2 个样本之间构建第一个报告。要采集所有已启用服务器的样本，可以调用 take_sample() 函数。无需频繁地创建样本 - 通常的基本频率是每小时一到两个样本。您可以使用类似 cron 的工具来安排样本的创建。下面是一个采用 30 分钟周期的示例：

*/30 * * * *   psql -c 'SELECT profile.take_sample()' > /dev/null 2>&1

但是，这类调用不会对 take_sample() 函数的结果进行错误检查。可以考虑下更加智能地使用 take_sample() 函数，将结果提供给您的监控系统。

对于成功完成采样的所有服务器，函数会返回 “OK”，而对于失败的服务器，会显示错误信息：

select * from take_sample();
  server   |                                   result                                    |   elapsed
-----------+-----------------------------------------------------------------------------+-------------
 ok_node   | OK                                                                          | 00:00:00.48
 fail_node | could not establish connection                                             +| 00:00:00
           | SQL statement "SELECT dblink_connect('server_connection',server_connstr)"  +|
           | PL/pgSQL function take_sample(integer) line 69 at PERFORM                  +|
           | PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+|
           | SQL function "take_sample" statement 1                                     +|
           | FATAL:  database "nodb" does not exist                                      |
(2 rows)

样本数据的保留

我们不能永远存储样本数据，因此我们有一个保留策略。您可以在三种层级上定义保留期：

  • 在 postgresql.conf 文件中设置参数 pg_profile.max_sample_age。这是一种常见的保留设置，如果没有定义其他保留设置，则有效。
  • 在创建服务器时定义服务器的 max_sample_age 设置，或对现有服务器使用 set_server_max_sample_age() 函数。此设置会覆盖特定服务器中全局的 pg_profile.max_sample_age 设置。
  • 创建一个基线（见下文）。基线会以最高优先级，覆盖纳入的样本的保留期。

列出样本

使用 show_samples() 函数可以获取存储库中现有样本的列表。此函数会显示出检测到的统计信息重置时间。

采样计时

当参数 pg_profile.track_sample_timings 设置为 on 时，pg_profile 会收集详细的采样计时统计信息。可以从 v_sample_timings 视图获得结果。v_sample_timings 字段的说明如下：

  • server_name - 采样的服务器名称
  • sample_id - 样本标识符
  • sample_time - 采样时间
  • event - 采样阶段
  • time_spent - 在事件中花费的时间

事件描述：

  • total - 采样（所有阶段）。
  • connect - 与服务器建立 dblink 连接。
  • get server environment - 获取服务器 GUC 参数、可用的扩展等。
  • collect database stats - 查询 pg_stat_database 视图，获取数据库的统计信息。
  • calculate database stats - 计算自上一个样本以来，数据库的差异统计信息。
  • collect tablespace stats - 在数据库中查询 pg_tablespace 视图，获取相关统计信息。
  • collect statement stats - 使用 pg_stat_statements 和 pg_stat_kcache 扩展，收集语句的统计信息。
  • query pg_stat_bgwriter - 使用 pg_stat_bgwriter 视图，收集实例统计信息。
  • query pg_stat_wal - 使用 pg_stat_wal 视图，收集实例的 WAL 统计信息。
  • query pg_stat_archiver - 使用 pg_stat_archiver 视图，收集实例统计信息。
  • collect object stats - 收集有关数据库对象的统计信息。包括以下事件：
    • db:dbname collect tables stats - 收集 dbname 数据库中表的统计信息。
      • db:dbname collect limited table sizes - 禁用大小收集时，收集 VACUUM 过或顺序扫描过的表的大小。
    • db:dbname collect indexes stats - 收集 dbname 数据库中索引的统计信息。
      • db:dbname collect limited index sizes - 禁用大小收集时，收集 VACUUM 过的表的索引大小。
    • db:dbname collect functions stats - 收集 dbname 数据库中函数的统计信息。
  • maintain repository - 执行支持的过程。
  • calculate tablespace stats - 计算表空间的差异统计信息。
  • calculate object stats - 计算数据库对象的差异统计信息。包括以下事件：
    • calculate tables stats - 计算所有数据库中表的差异统计信息。
    • calculate indexes stats - 计算所有数据库中索引的差异统计信息。
    • calculate functions stats - 计算所有数据库中函数的差异统计信息。
  • calculate cluster stats - 计算实例的差异统计信息。
  • calculate archiver stats - 计算归档器的差异统计信息。
  • delete obsolete samples - 删除过时的基线和样本。

基线

基线是一个命名的样本序列，具有自己的保留设置。基线可以在报表构建函数中用作采样间隔。未定义基线保留期意味着无限保留。 您可以使用基线来保存在特定时间段内的数据库业务负载的信息。例如，在系统上的负载测试期间，或者正常负载期间，您可能希望保存收集的样本，以供进一步参考。 基线管理函数：

  • create_baseline([server name,] baseline_name varchar(25), start_id integer, end_id integer [, days integer]) - 创建一个基线

    • server - 服务器名称。如果省略，则假定为本地服务器
    • name - 基线名称。每个基线在一个服务器中必须具有唯一的名称。
    • start_id, end_id - 包含在基线中的第一个和最后一个样本。
    • days - 基线保留时间。定义为自 now() 以来的整数天数。可以省略此参数（或设置为 null），以表示永久保留。

  • create_baseline([server name,] baseline_name varchar(25), time_range tstzrange [, days integer]) - 创建一个基线

    • server - 服务器名称。如果省略，则假定为本地服务器
    • name - 基线名称。每个基线在一个服务器中必须具有唯一的名称。
    • time_range - 基线时间间隔。基线会包括覆盖此间隔内的所有可用样本。
    • days - 基线保留时间。定义为自 now() 以来的整数天数。可以省略此参数（或设置为 null），以表示永久保留。

  • drop_baseline([server name,] name varchar(25)) - 删除一个基线

    • server - 服务器名称。如果省略，则假定为本地服务器
    • name - 要删除的基线名称。删除一个基线，并不意味着会立即删除其所有样本，它们只是被排除在基线之外，因此不再会受基线保留期的保护。

  • keep_baseline([server name,] name varchar(25) [, days integer]) - 更改基线的保留期

    • server - 服务器名称。如果省略，则假定为本地服务器
    • name - 基线名称。可以省略此参数（或设置为 null），以更改所有现有基线的保留期。
    • days - 基线的保留时间，自 now() 以来的整数天数。此外，可以省略（或设置为 null）以设为永久保留。

  • show_baselines([server name]) - 显示现有的基线。调用此函数，可获取有关现有基线的信息（名称、采样间隔和保留时间）

    • server - 服务器名称。如果省略，则假定为本地服务器
    postgres=# SELECT * FROM profile.baseline_show('local');

数据导出和导入

收集的样本可以从安装 pg_profile 扩展的实例导出，然后加载到另一个实例中。当您想要将服务器从一个实例移动到另一个实例时，或者当您需要将收集的数据发送给您的支持团队时，此功能非常有用。

数据导出

数据通过函数 export_data() 导出为一个常规表。您可以使用任何方法从数据库中导出此表。例如，您可以使用 psql 的 copy 命令获得单个 .csv 文件：

postgres=# \copy (select * from export_data()) to 'export.csv'

默认情况下，export_data() 函数会导出所有已配置的服务器的所有样本。但是，您可以将导出限制为仅一个服务器，并进一步限制样本范围：

  • export_data([server name, [min_sample_id integer,] [max_sample_id integer]] [, obfuscate_queries boolean]) - 导出收集的数据
    • server 是一个服务器名称。如果省略，则假定为所有服务器
    • min_sample_id 和 max_sample_id - 导出边界样本的标识符（包含边界）。min_sample_id 边界值为空值，会导出直到 max_sample_id 边界的所有样本，max_sample_id 边界值为空值，会导出自 min_sample_id 边界以来的所有样本。
    • obfuscate_queries - 仅当您想要隐藏查询文本时，才使用此参数 - 这些文本将导出为 MD5 哈希值。

数据导入

数据只能从本地表导入，因此需要先加载之前导出的数据。在我们的例子中，使用 copy 命令：

postgres=# CREATE TABLE import (section_id bigint, row_data json);
CREATE TABLE
postgres=# \copy import from 'export.csv'
COPY 6437

现在我们可以执行数据导入，将此表提供给 import_data() 函数：

postgres=# SELECT * FROM import_data('import');

尽管也会导入服务器说明，但名称匹配的本地 pg_profile 服务器会禁止导入操作。请考虑临时对这些服务器重命名，或使用 server_name_prefix 参数以避免冲突。如果您需要为之前导入的服务器导入新数据，它们将按系统标识符进行匹配，因此您可以根据需要重命名导入的服务器。所有服务器都会导入为禁用状态。 import_data() 函数仅接受已导入的表：

  • import_data(data regclass, server_name_prefix text)
    • data - 包含导出数据的表
    • server_name_prefix - 用于导入服务器的前缀。此函数返回扩展表中实际加载的行数。导入操作成功后，您可以删除导入表。

报告

报告由报告函数以 HTML 标记语言生成。pg_profile 中提供两种类型的报告：

  • Regular reports，包含报告间隔内关于实例业务负载情况的统计信息
  • Differential reports，包含来自两个区间的数据，这些数据对应了相同对象的统计数值，彼此相邻，以便于比较业务负载

常规报告函数

  • get_report([server name,] start_id integer, end_id integer [, description text [, with_growth boolean]]) - 按样本标识符生成报告
  • get_report([server name,] time_range tstzrange [, description text [, with_growth boolean]]) - 生成涵盖提供的 time_range 时间范围的最短样本间隔的报告。
  • get_report([server name], baseline varchar(25) [, description text [, with_growth boolean]]) - 使用基线作为样本间隔，生成报告
  • get_report_latest([server name]) - 为最近两个样本生成报告。 函数参数：
    • server - 服务器名称。如果省略，则假定为本地服务器
    • start_id - 间隔开始的样本标识符
    • end_id - 间隔结束的样本标识符
    • time_range - 时间范围（tstzrange 类型）
    • baseline - 基线名称
    • with_growth - 一个标志，请求将样本间隔扩展到带有关系增长数据的最接近边界。默认值为 false
    • description - 一个文本备忘录，它会作为一个报告描述包含在报告中

差异报告函数

您可以使用样本标识符、基线和时间范围作为间隔边界，来生成差异报告：

  • get_diffreport([server name,] start1_id integer, end1_id integer, start2_id integer, end2_id integer [, description text [, with_growth boolean]]) - 基于样本标识符定义的两个间隔，生成差异报告
  • get_diffreport([server name,] baseline1 varchar(25), baseline2 varchar(25) [, description text [, with_growth boolean]]) - 基于基线名称定义的两个间隔，生成差异报告
  • get_diffreport([server name,] time_range1 tstzrange, time_range2 tstzrange [, description text [, with_growth boolean]]) - 基于时间范围定义的两个间隔，生成差异报告
    • server 是服务器名称。如果省略，则假定为本地服务器
    • start1_id, end1_id - 第一个间隔的样本标识符
    • start2_id, end2_id - 第二个间隔的样本标识符
    • baseline1 - 第一个间隔的基线名称
    • baseline2 - 第二个间隔的基线名称
    • time_range1 - 第一个间隔的时间范围
    • time_range2 - 第二个间隔的时间范围
    • description 是一个文本备忘录 - 它会作为一个报告描述包含在报告中
    • with_growth 是一个标志，请求将两个间隔扩展到带有关系增长数据的最接近边界。默认值为 false

此外，您还可以使用一些对上面函数的组合：

  • get_diffreport([server name,] baseline varchar(25), time_range tstzrange [, description text [, with_growth boolean]])
  • get_diffreport([server name,] time_range tstzrange, baseline varchar(25) [, description text [, with_growth boolean]])
  • get_diffreport([server name,] start1_id integer, end1_id integer, baseline varchar(25) [, description text [, with_growth boolean]])
  • get_diffreport([server name,] baseline varchar(25), start2_id integer, end2_id integer [, description text [, with_growth boolean]])

报告生成示例：

$ psql -Aqtc "SELECT profile.get_report(480,482)" -o report_480_482.html

对于任何其他服务器，请使用其名称：

$ psql -Aqtc "SELECT profile.get_report('omega',12,14)" -o report_omega_12_14.html

使用时间范围生成报告：

psql -Aqtc "select profile.get_report(tstzrange('2020-05-13 11:51:35+03','2020-05-13 11:52:18+03'))" -o report_range.html

此外，时间范围对于生成定期报告也很有用。让我们构建最近 24 小时的报告：

psql -Aqtc "select profile.get_report(tstzrange(now() - interval '1 day',now()))" -o last24h_report.html

现在，您可以使用任何 Web 浏览器来查看报告文件。

了解更多

pg_profile: PostgreSQL 历史工作负载报告

搜索

分类

标签