pg_profile: PostgreSQL 历史工作负载报告

三月 12, 2024

摘要pg_profile扩展可帮助您找出 PostgreSQL 数据库中最消耗资源的活动。

本文包含以下部分:

  1. 概念
  2. 扩展架构
  3. 前提条件
  4. 安装
  5. 权限
  6. 目录

概念

扩展基于 PostgreSQL 的统计视图和几个 contrib 扩展。它是用纯 PL/pgSQL 编写的,不依赖任何外部程序库或软件,只需要 PostgreSQL 数据库本身,以及执行定期任务的类似 cron 的工具。它最初在 PostgreSQL 9.6 上开发和测试,扩展可能会与早期版本不兼容。

pg_profile 会使用到 pg_stat_kcache 数据,提供有关语句 CPU 使用率和文件系统负载的信息(如果安装了该扩展)。

此扩展会在您的数据库中创建历史存储库。此存储库将保存 PostgreSQL 实例的统计信息“样本”。样本是通过调用 take_sample() 函数获取的。PostgreSQL 没有任何作业相关的引擎,因此您需要使用 cron

定期样本可以帮助您找出过去最耗费资源的活动。假设你收到报告,在几个小时前出现了性能下降。要处理此类问题,可以限定性能问题发生的时间段,在两个样本之间生成一个报告,以查看数据库的负载概要。您可以使用提供的 grafana 仪表板,来定位发生性能问题的确切时间。

您可以在运行任何批处理任务的开始和结束,采集显式样本。

在您每次采集样本时,都会调用 pg_stat_statements_reset(),确保不会因语句数量达到 pg_stat_statements.max 而丢失语句。此外,报告中会包含章节,告知您的任何样本中捕获的语句计数,是否达到了 pg_stat_statements.max 的 90%。重置 pg_stat_statements 数据会影响监控系统。

如果安装了扩展 pg_wait_samplingpg_profile 会收集 PostgreSQL 实例的概要等待统计信息,并重置 pg_wait_sampling_profile 视图数据。这种行为也会影响监控系统。

安装在一个实例中 pg_profile,可以从其他实例收集统计信息,这些实例被称为服务器。您只需要提供名称和连接字符串,定义一些服务器,并确保可以与所有服务器的所有数据库建立连接。比如,现在您可以,从主服务器或任何其他服务器,来跟踪备用服务器的统计信息。安装扩展后,会自动创建一个本地服务器 - 这是一个 pg_profile 所在的实例的服务器。

扩展架构

扩展由四个部分组成:

  • 历史存储库 是一个采样数据的存储库。存储库中有一组扩展相关的表。
  • 样本管理引擎 是一组函数,用于采集样本,删除存储库中过时的样本数据以管理存储库。
  • 报告引擎 是一组函数,用于根据历史存储库中的数据生成报告。
  • 管理函数 允许您创建和管理服务器基线

前提条件

虽然 pg_profile 通常安装在目标实例中,但它也可以从其他实例收集性能数据。因此,我们有一些 pg_profile 数据库和服务器的前提条件。

pg_profile 数据库前提条件

pg_profile 扩展名依赖于扩展 plpgsqldblink

服务器前提条件

服务器实例的唯一强制性要求是,能够使用提供的服务器连接字符串,从 pg_profile 数据库进行连接。所有其他要求都是可选的,但它们可以提高收集的统计信息的完整性。

请考虑设置以下统计信息收集器的相关参数:

track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on      # Since PostgreSQL 14
track_functions = all/pl

如果需要在报告中生成语句统计信息,则在服务器连接字符串中提到的数据库,必须配置好 pg_stat_statements 扩展。设置 pg_stat_statements 参数,以满足您的需求(请参阅 PostgreSQL 文档):

  • pg_stat_statements.max - 此参数设置偏低,可能会导致某些语句统计信息在采样之前被清除掉。如果您的 pg_stat_statements.max 有点设置过小,报告会显示警告。
  • pg_stat_statements.track = ’top’ - 在 PostgreSQL 14 之前,设为 all 值会影响报告中语句相关部分的 %Total 字段的准确性。

安装

步骤 1 安装扩展文件

将扩展文件(请访问 Releases 页面)解压缩到 PostgreSQL 扩展安装位置,如

# tar xzf pg_profile-<version>.tar.gz --directory $(pg_config --sharedir)/extension

只要确保您使用的是适当的 pg_config 实用程序即可。

步骤 2 创建扩展

最简单的方法是,将所有扩展安装到数据库的 public 模式中:

postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE EXTENSION pg_profile;

如果要将 pg_profile 安装到其他模式中,只需创建好模式,然后在该模式中安装扩展:

postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE SCHEMA profile;
postgres=# CREATE EXTENSION pg_profile SCHEMA profile;

所有对象都会创建在由 SCHEMA 子句定义的模式中。安装到专有模式中是一种推荐的方式 - 扩展会创建自己的表、视图、序列和函数。将它们分离开是个好主意。如果不想在使用模块时指定模式限定符,请考虑更改 search_path 设置。

步骤 3 更新到新版本

新版本的 pg_profile 会包含有迁移脚本(如果可能)。因此,在更新的情况下,您只需要安装好扩展文件(请参阅步骤 1),并更新扩展,如下所示:

postgres=# ALTER EXTENSION pg_profile UPDATE;

权限

在使用 pg_profile 的过程中,最多会涉及三种角色:

  • pg_profile 所有者pg_profile 扩展的所有者。
  • 收集角色 是在 pg_profile 连接到数据库时使用的。
  • 报告角色 用于生成报告。如果使用超级用户角色 postgres,执行 pg_profile 的所有操作,则可以跳过以下大部分步骤。

pg_profile 所有者

此角色可用于执行与 pg_profile 扩展相关的所有操作。此角色可以有权获取到服务器连接字符串,其中可能包含密码。您应该使用此角色来运行 take_sample() 函数。此用户需要 dblink 扩展。下面是一个复杂的示例,假定每个扩展都在自己的模式中:

\c postgres postgres
CREATE SCHEMA dblink;
CREATE EXTENSION dblink SCHEMA dblink;
CREATE USER profile_usr with password 'profile_pwd';
GRANT USAGE ON SCHEMA dblink TO profile_usr;
CREATE SCHEMA profile AUTHORIZATION profile_usr;
\c postgres profile_usr
CREATE EXTENSION pg_profile SCHEMA profile;

收集角色

pg_profile 会使用此角色连接到数据库,并执行统计信息收集。非特权用户无法在没有密码的情况下,使用 dblink 扩展打开连接,因此您必须在每个服务器的连接字符串中提供密码。此角色应当有权访问所有支持的统计信息扩展。此外,它应该能够重置扩展中的统计信息。最复杂的一个例子:

\c postgres postgres
CREATE SCHEMA pgss;
CREATE SCHEMA pgsk;
CREATE SCHEMA pgws;
CREATE EXTENSION pg_stat_statements SCHEMA pgss;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;
CREATE EXTENSION pg_wait_sampling SCHEMA pgws;
CREATE USER profile_collector with password 'collector_pwd';
GRANT pg_read_all_stats TO profile_collector;
GRANT USAGE ON SCHEMA pgss TO profile_collector;
GRANT USAGE ON SCHEMA pgsk TO profile_collector;
GRANT USAGE ON SCHEMA pgws TO profile_collector;
GRANT EXECUTE ON FUNCTION pgsk.pg_stat_kcache_reset TO profile_collector;
GRANT EXECUTE ON FUNCTION pgss.pg_stat_statements_reset TO profile_collector;
GRANT EXECUTE ON FUNCTION pgws.pg_wait_sampling_reset_profile TO profile_collector;

现在,您应该设置一个连接字符串,指向安装了统计信息扩展的数据库:

\c postgres profile_usr
SELECT profile.set_server_connstr('local','dbname=postgres port=5432 host=localhost user=profile_collector password=collector_pwd');

必须在 pg_hba.conf 文件中,为 profile_collector 用户配置密码身份验证。

显然,应在被 pg_profile 观察的所有服务器上正确配置收集角色。

您现在应该能够使用 profile_usr 角色执行 take_sample() 调用:

\c postgres profile_usr
SELECT * FROM take_sample();

是时候配置调度程序了,在我们的示例中,用的是 postgres 用户的 crontab:

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

请注意,您可以使用 postgres 密码文件来存储密码。

报告角色

任何 postgres 用户都可以构建 pg_profile 报告。生成 pg_profile 报告所需的最低权限,是要授予 public 角色。但是,包含查询文本的完整报告,仅供 pg_read_all_stats 角色的成员可用。无论如何,报告角色无法访问服务器连接字符串,因此它无法获取服务器的密码。

目录