在PostgreSQL使用pg_profile生成数据库性能报告

本文链接：https://blog.youkuaiyun.com/xudajian/article/details/148676724

pg_profile是一款用于 PostgreSQL 数据库性能分析的工具，它可以提供详细的数据库活动信息，帮助用户深入了解数据库的运行状态，找出性能瓶颈并进行优化。pg_profile插件在指定时间间隔内生成快照，可以针对任意两个快照的间隔时间进行性能分析，生成html格式报告汇总展示。

1. 概念

pg_profile扩展基于 PostgreSQL 的统计视图以及 pg_stat_statements 和 pg_stat_kcache 的 contrib 扩展。它是用纯 pl/pgsql 编写的，不需要任何外部库或软件，而是 PostgreSQL 数据库本身和一个执行定期任务的类似 cron 的工具。

pg_profile 将使用pg_stat_kcache数据，提供有关语句 CPU 使用率和文件系统负载（如果此扩展可用）的信息。

pg_profile扩展将在您的数据库中创建 Historic 存储库。此存储库将保存 postgres 集群的统计数据“样本”。通过调用 take_sample（）函数获取样本。PostgreSQL 没有任何类似 Job 的引擎，因此您需要使用cron。

定期样本可以帮助您查找过去大多数资源密集型活动。假设您在几个小时前收到了性能下降的报告。解决此类问题，您可以在两个样本之间构建一个报告，以确定性能问题期，以查看数据库的负载配置文件。您可以使用提供的 grafana 控制面板来查找性能问题发生的确切时间。您可以在运行任何批处理之前和完成之后获取显式样本。

任何时候抽样时，都会调用 pg_stat_statements_reset（），确保不会因为达到 pg_stat_statements.max而丢失语句。此外，report 将包含 section，通知您任何样本中捕获的语句计数是否达到 90% pg_stat_statements.max。重置 pg_stat_statements 数据将影响监控系统。

如果pg_wait_sampling扩展可用，pg_profile将收集PostgresSQL实例的摘要等待统计信息，并重置pg_wait_siampling_profile视图数据。这种行为也会影响监控系统。

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

2. 主要功能

查询性能分析：能够收集和分析查询的执行时间、执行计划、资源消耗等信息，帮助用户确定哪些查询存在性能问题，以及问题产生的原因。

数据库活动监控：实时监控数据库的各种活动，包括连接数、事务处理、锁的使用情况等，以便及时发现异常活动和潜在的性能风险。

资源使用统计：对数据库的资源使用情况进行统计，如 CPU、内存、磁盘 I/O 等，让用户了解数据库对资源的需求和利用效率。

性能指标报告：生成详细的性能报告，包含各种性能指标的统计数据和图表，直观展示数据库的性能状况。

3. 扩展体系结构

扩展由四个部分组成：

历史存储库是用于采样数据的存储。Repository 是一组扩展表。
样本管理引擎是一组函数，用于采集样本并通过从中删除过时的样本数据来支持存储库。
报表引擎是一组函数，用于根据历史存储库中的数据生成报表。
管理功能允许您创建和管理服务器和基准。

4. 先决条件

尽管 pg_profile 通常安装在目标集群中，但它也可以从其他集群收集性能数据。因此，我们有 pg_profile 数据库和服务器的先决条件。

4.1 pg_profile 数据库先决条件

pg_profile扩展依赖于扩展 plpgsql 和 dblink。

4.2 服务器先决条件

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

考虑设置以下 Statistics Collector 参数：

track_activities = on

track_counts = on

track_io_timing = on

track_wal_io_timing = on # Since Postgres 14

track_functions = all/pl

create table test(id int, name text, info text, create_time timestamp, primary key(id));

如果需要报表中的语句统计信息，则服务器连接字符串中提到的 database 必须配置pg_stat_statements扩展。设置pg_stat_statements参数以满足您的需求（请参阅 PostgreSQL 文档）：

● pg_stat_statements.max - 此参数的低设置可能会导致在采样之前清除某些 statements 统计信息。如果您的 pg_stat_statements.max 似乎尺寸过小，报告会警告您。

● pg_stat_statements.track = 'top' - 在 Postgres 14 之前，所有值都会影响报表中与语句相关的部分的 %Total 字段的准确性。

5. 安装与配置

5.1 安装包下载

https://github.com/powa-team/pg_stat_kcache

5.2 修改postgresql.conf文件

[postgres@pgserver01 ~]$ vi $PGDATA/postgresql.conf

shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'

#pg_stat_statements配置

pg_stat_statements.max = 10000

pg_stat_statements.track = top

pg_stat_statements.save = on

pg_stat_statements.track_utility = on

track_io_timing = on

track_activity_query_size = 2048

#pg_profile配置

track_activities = on

track_counts = on

track_io_timing = on

track_wal_io_timing = on # Since Postgres 14

track_functions = all

5.3 提取到 PostgreSQL 扩展位置

[postgres@pgserver01 ~]$ tar zxvf pg_profile--4.7.tar.gz --directory $(pg_config --sharedir)/extension

[postgres@pgserver01 ~]$ ls /opt/pgsql/share/extension/

5.4 重新启动数据库

[postgres@pgserver01 ~]$ pg_ctl -D $PGDATA -l logfile start

5.5 创建扩展

postgres=# CREATE EXTENSION dblink;

postgres=# CREATE EXTENSION pg_stat_statements;

postgres=# CREATE EXTENSION pg_profile;

如果你想在其他 schema 中安装 pg_profile，只需创建它，然后在该 schema 中安装 extension。所有对象都将在 schema 中创建，由 SCHEMA 子句定义。推荐使用专用模式进行安装 - 该扩展将创建自己的表、视图、序列和函数。将它们分开是个好主意。如果不想在使用 module 时指定 schema 限定符，请考虑更改 search_path 设置。