三月 12, 2024
摘要：pg_profile扩展可帮助您找出 PostgreSQL 数据库中最消耗资源的活动。
目录
概念
扩展基于 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_sampling，pg_profile 会收集 PostgreSQL 实例的概要等待统计信息，并重置 pg_wait_sampling_profile 视图数据。这种行为也会影响监控系统。
安装在一个实例中 pg_profile，可以从其他实例收集统计信息，这些实例被称为服务器。您只需要提供名称和连接字符串，定义一些服务器，并确保可以与所有服务器的所有数据库建立连接。比如，现在您可以，从主服务器或任何其他服务器，来跟踪备用服务器的统计信息。安装扩展后，会自动创建一个本地服务器 - 这是一个 pg_profile 所在的实例的服务器。
扩展架构
扩展由四个部分组成：
- 历史存储库 是一个采样数据的存储库。存储库中有一组扩展相关的表。
- 样本管理引擎 是一组函数，用于采集样本，删除存储库中过时的样本数据以管理存储库。
- 报告引擎 是一组函数，用于根据历史存储库中的数据生成报告。
- 管理函数 允许您创建和管理服务器和基线。
前提条件
虽然 pg_profile 通常安装在目标实例中，但它也可以从其他实例收集性能数据。因此，我们有一些 pg_profile 数据库和服务器的前提条件。
pg_profile 数据库前提条件
pg_profile 扩展名依赖于扩展 plpgsql 和 dblink。
服务器前提条件
服务器实例的唯一强制性要求是，能够使用提供的服务器连接字符串，从 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 角色的成员可用。无论如何，报告角色无法访问服务器连接字符串，因此它无法获取服务器的密码。