PostgreSQL数据库最强大的信息收集工具—pg_profile

原创沈陽²⁰²² ᯤ⁶ᴳ All China Database Union 2024-07-07

1514

一、pg_profile插件功能概述

1.1、pg_profile插件功能概述

pg_profile是适用于PostgreSQL的一个功能非常强大的高性能信息收集、分析工具。pg_profile能够帮助用户深入了解数据库资源的消耗情况，进而发现性能瓶颈并进行优化。pg_profile基于PostgreSQL的标准统计视图，并结合pg_stat_statements和pg_stat_kcache这两个社区插件来实现其功能。它的工作原理是通过捕获数据库中被任意服务器进程执行的命令、收集关于表和索引访问的统计信息、监控对块的读写次数以及对用户定义函数的使用情况进行跟踪，从而进行性能分析汇总展示。pg_profile类似于Oracle的AWR（Automatic Workload Repository）架构，能够在指定时间间隔内生成快照，并通过HTML格式的报告来解释这些快照之间的统计数据差异。

1.2、扩展架构

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

二、环境搭建

2.1、pg_profile 扩展先决条件

pg_profile扩展依赖于扩展plpgsql和dblink，如果您需要报告中的语句统计信息，则服务器连接字符串中提到的数据库必须配置pg_stat_statements扩展。因此要使用pg_profile扩展，必须同时安装dblink，pg_stat_statements扩展。

2.2、安装扩展pg_profile

说明：pg_profile扩展下载地址： https://github.com/zubkov-andrei/pg_profile/releases/download/4.6/pg_profile--4.6.tar.gz

代码如下：

 
###### 步骤1：下载pg_profile

[postgres@Server download]$ wget https://github.com/zubkov-andrei/pg_profile/releases/download/4.6/pg_profile--4.6.tar.gz
--2024-07-06 15:15:30--  https://github.com/zubkov-andrei/pg_profile/releases/download/4.6/pg_profile--4.6.tar.gz
正在解析主机 github.com (github.com)... 20.205.243.166
正在连接 github.com (github.com)|20.205.243.166|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://objects.githubusercontent.com/github-production-release-asset-2e65be/102480763/d0d6c93b-343f-4bc5-9204-50d7d7cbadbe?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20240706%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240706T071530Z&X-Amz-Expires=300&X-Amz-Signature=ef827100c27bec5216997ea7fe9b95dd1f83abd9f0f6ad0419daa68961bcd632&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=102480763&response-content-disposition=attachment%3B%20filename%3Dpg_profile--4.6.tar.gz&response-content-type=application%2Foctet-stream [跟随至新的 URL]
--2024-07-06 15:15:31--  https://objects.githubusercontent.com/github-production-release-asset-2e65be/102480763/d0d6c93b-343f-4bc5-9204-50d7d7cbadbe?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20240706%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240706T071530Z&X-Amz-Expires=300&X-Amz-Signature=ef827100c27bec5216997ea7fe9b95dd1f83abd9f0f6ad0419daa68961bcd632&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=102480763&response-content-disposition=attachment%3B%20filename%3Dpg_profile--4.6.tar.gz&response-content-type=application%2Foctet-stream
正在解析主机 objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.111.133, 185.199.108.133, 185.199.109.133, ...
正在连接 objects.githubusercontent.com (objects.githubusercontent.com)|185.199.111.133|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：210072 (205K) [application/octet-stream]
正在保存至: “pg_profile--4.6.tar.gz”

pg_profile--4.6.tar.gz     100%[======================================>] 205.15K  15.0KB/s  用时 14s

2024-07-06 15:15:47 (15.0 KB/s) - 已保存 “pg_profile--4.6.tar.gz” [210072/210072])

[postgres@Server download]$

###### 步骤2：解压pg_profile


[postgres@Server download]$ tar xvzf pg_profile--4.6.tar.gz -C /postgres/server/share/extension/
pg_profile--4.6.sql
pg_profile.control
pg_profile--4.5--4.6.sql
[postgres@Server ~]$

2.3、安装扩展dblink

代码如下：

 
###### 步骤1：切换到源文件扩展目录

[postgres@Server contrib]$ cd /download/postgresql-16.3/contrib/dblink/
[postgres@Server dblink]$

###### 步骤2：编译安装dblink扩展

[postgres@Server dblink]$ make & make install
[1] 11711
make -C ../../src/backend generated-headers
make -C ../../src/backend generated-headers
make[1]: 进入目录“/install/postgresql-16.3/src/backend”
make -C catalog distprep generated-header-symlinks
make[1]: 进入目录“/install/postgresql-16.3/src/backend”
make -C catalog distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/catalog”
make[2]: 进入目录“/install/postgresql-16.3/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/catalog”
make -C nodes distprep generated-header-symlinks
make -C nodes distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/nodes”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/nodes”
make -C utils distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/nodes”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/nodes”
make -C utils distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make -C adt jsonpath_gram.h
make[2]: 进入目录“/install/postgresql-16.3/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make -C adt jsonpath_gram.h
make[3]: 进入目录“/install/postgresql-16.3/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-16.3/src/backend/utils/adt”
make[3]: 进入目录“/install/postgresql-16.3/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-16.3/src/backend/utils/adt”
make[2]: 离开目录“/install/postgresql-16.3/src/backend/utils”
make[2]: 离开目录“/install/postgresql-16.3/src/backend/utils”
make[1]: 离开目录“/install/postgresql-16.3/src/backend”
make[1]: 离开目录“/install/postgresql-16.3/src/backend”
make -C ../../src/interfaces/libpq all
make -C ../../src/interfaces/libpq all
make[1]: 进入目录“/install/postgresql-16.3/src/interfaces/libpq”
make -C ../../../src/port all
make[1]: 进入目录“/install/postgresql-16.3/src/interfaces/libpq”
make -C ../../../src/port all
make[2]: 进入目录“/install/postgresql-16.3/src/port”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/port”
make[2]: 进入目录“/install/postgresql-16.3/src/port”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/port”
make -C ../../../src/common all
make -C ../../../src/common all
make[2]: 进入目录“/install/postgresql-16.3/src/common”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/common”
make[2]: 进入目录“/install/postgresql-16.3/src/common”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/common”
make[1]: 离开目录“/install/postgresql-16.3/src/interfaces/libpq”
make[1]: 离开目录“/install/postgresql-16.3/src/interfaces/libpq”
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o dblink.o dblink.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o dblink.o dblink.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o dblink.so  dblink.o -L../../src/port -L../../src/common -L../../src/interfaces/libpq -lpq   -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o dblink.so  dblink.o -L../../src/port -L../../src/common -L../../src/interfaces/libpq -lpq   -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden
/usr/bin/mkdir -p '/postgres/server/lib'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/install -c -m 755  dblink.so '/postgres/server/lib/dblink.so'
/usr/bin/install -c -m 644 ./dblink.control '/postgres/server/share/extension/'
/usr/bin/install -c -m 644 ./dblink--1.2.sql ./dblink--1.1--1.2.sql ./dblink--1.0--1.1.sql  '/postgres/server/share/extension/'
[1]+  已完成               make
[postgres@Server dblink]$

2.4、安装扩展pg_stat_statements

代码如下：

 
###### 步骤1：切换到源文件扩展目录

[postgres@Server ~]$ cd /install/postgresql-16.3/contrib/pg_stat_statements/
[postgres@Server pg_stat_statements]$

###### 步骤2：编译安装扩展pg_stat_statements

[postgres@Server pg_stat_statements]$ make & make install
[1] 11765
make -C ../../src/backend generated-headers
make -C ../../src/backend generated-headers
make[1]: 进入目录“/install/postgresql-16.3/src/backend”
make -C catalog distprep generated-header-symlinks
make[1]: 进入目录“/install/postgresql-16.3/src/backend”
make -C catalog distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/catalog”
make[2]: 进入目录“/install/postgresql-16.3/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/catalog”
make -C nodes distprep generated-header-symlinks
make -C nodes distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/nodes”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/nodes”
make[2]: 进入目录“/install/postgresql-16.3/src/backend/nodes”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-16.3/src/backend/nodes”
make -C utils distprep generated-header-symlinks
make -C utils distprep generated-header-symlinks
make[2]: 进入目录“/install/postgresql-16.3/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make -C adt jsonpath_gram.h
make[2]: 进入目录“/install/postgresql-16.3/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make[3]: 进入目录“/install/postgresql-16.3/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-16.3/src/backend/utils/adt”
make[2]: 离开目录“/install/postgresql-16.3/src/backend/utils”
make[1]: 离开目录“/install/postgresql-16.3/src/backend”
make -C adt jsonpath_gram.h
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o pg_stat_statements.o pg_stat_statements.c
make[3]: 进入目录“/install/postgresql-16.3/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-16.3/src/backend/utils/adt”
make[2]: 离开目录“/install/postgresql-16.3/src/backend/utils”
make[1]: 离开目录“/install/postgresql-16.3/src/backend”
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o pg_stat_statements.o pg_stat_statements.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_stat_statements.so  pg_stat_statements.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags -lm  -fvisibility=hidden
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_stat_statements.so  pg_stat_statements.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags -lm  -fvisibility=hidden
/usr/bin/mkdir -p '/postgres/server/lib'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/install -c -m 755  pg_stat_statements.so '/postgres/server/lib/pg_stat_statements.so'
/usr/bin/install -c -m 644 ./pg_stat_statements.control '/postgres/server/share/extension/'
/usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql  '/postgres/server/share/extension/'
[1]+  已完成               make
[postgres@Server pg_stat_statements]$

2.5、配置postgresql.conf

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

 
vi  $PGDATA/postgresql.conf



###### pg_profile相关配置
track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on      # Since Postgres 14
track_functions = all
#pg_stat_statements相关配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'top'
pg_stat_statements.save = off

说明：
如果您需要报告中的语句统计信息，则服务器连接字符串中提到的数据库必须配置pg_stat_statements扩展。设置pg_stat_statements参数以满足您的需求（请参阅 PostgreSQL 文档）： pg_stat_statements.max - 此参数的低设置可能导致在采样之前清除一些语句统计信息。如果您的pg_stat_statements.max似乎太小，报告将警告您。 pg_stat_statements.track = 'top' - 在 Postgres 14 之前，所有值都会影响报告中与语句相关的部分的%Total字段的准确性。

2.6、安装扩展

代码如下：

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

2.7、查看扩展

代码如下：

 
testdb=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_profile         | 4.6     | profile    | PostgreSQL load profile repository and report builder
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

testdb=#

2.8、查看pg_profile扩展相关功能

代码如下：

 

testdb=# \dx+ pg_profile
                                Objects in extension "pg_profile"
                                       Object description
-------------------------------------------------------------------------------------------------
 function profile.cluster_stat_io_format(integer,integer,integer)
 function profile.cluster_stat_io_format(integer,integer,integer,integer,integer)
 function profile.cluster_stat_io(integer,integer,integer)
 function profile.cluster_stat_io_reset_format(integer,integer,integer)
 function profile.cluster_stat_io_reset_format(integer,integer,integer,integer,integer)
 function profile.cluster_stat_io_resets(integer,integer,integer)
 function profile.cluster_stats_format_diff(integer,integer,integer,integer,integer)
 function profile.cluster_stats_format(integer,integer,integer)
 function profile.cluster_stats(integer,integer,integer)
 function profile.cluster_stat_slru_format(integer,integer,integer)
 function profile.cluster_stat_slru_format(integer,integer,integer,integer,integer)
 function profile.cluster_stat_slru(integer,integer,integer)
 function profile.cluster_stat_slru_reset_format(integer,integer,integer)
 function profile.cluster_stat_slru_reset_format(integer,integer,integer,integer,integer)
 function profile.cluster_stat_slru_resets(integer,integer,integer)
 function profile.cluster_stats_reset_format_diff(integer,integer,integer,integer,integer)
 function profile.cluster_stats_reset_format(integer,integer,integer)
 function profile.cluster_stats_reset(integer,integer,integer)
 function profile.collect_obj_stats(jsonb,integer,integer,boolean)
 function profile.collect_pg_stat_statements_stats(jsonb,integer,integer,integer)
 function profile.collect_pg_wait_sampling_stats_11(jsonb,integer,integer,integer)
 function profile.collect_pg_wait_sampling_stats(jsonb,integer,integer,integer)
 function profile.collect_subsamples(integer,integer,jsonb)
 function profile.create_baseline(character varying,integer,integer,integer)
 function profile.create_baseline(character varying,tstzrange,integer)
 function profile.create_baseline(name,character varying,integer,integer,integer)
 function profile.create_baseline(name,character varying,tstzrange,integer)
 function profile.create_server(name,text,boolean,integer,text)
 function profile.create_server_partitions(integer)
 function profile.dbstats_format_diff(integer,integer,integer,integer,integer)
 function profile.dbstats_format(integer,integer,integer)
 function profile.dbstats(integer,integer,integer)
 function profile.dbstats_reset_format_diff(integer,integer,integer,integer,integer)
 function profile.dbstats_reset_format(integer,integer,integer)
 function profile.dbstats_reset(integer,integer,integer)
 function profile.delete_samples(integer,integer)
 function profile.delete_samples(integer,integer,integer)
 function profile.delete_samples(name,integer,integer)
 function profile.delete_samples(name,tstzrange)
 function profile.delete_samples(tstzrange)
 function profile.disable_server(name)
 function profile.drop_baseline(character varying)
 function profile.drop_baseline(name,character varying)
 function profile.drop_server(name)
 function profile.enable_server(name)
 function profile.export_data(name,integer,integer,boolean)
 function profile.get_baseline_samples(integer,character varying)
 function profile.get_connstr(integer,jsonb)
 function profile.get_diffreport(character varying,character varying,text,boolean)
 function profile.get_diffreport(character varying,integer,integer,text,boolean)
 function profile.get_diffreport(integer,integer,character varying,text,boolean)
 function profile.get_diffreport(integer,integer,integer,integer,integer,text,boolean)
 function profile.get_diffreport(integer,integer,integer,integer,text,boolean)
 function profile.get_diffreport(name,character varying,character varying,text,boolean)
 function profile.get_diffreport(name,character varying,integer,integer,text,boolean)
 function profile.get_diffreport(name,character varying,tstzrange,text,boolean)
 function profile.get_diffreport(name,integer,integer,character varying,text,boolean)
 function profile.get_diffreport(name,integer,integer,integer,integer,text,boolean)
 function profile.get_diffreport(name,tstzrange,character varying,text,boolean)
 function profile.get_diffreport(name,tstzrange,tstzrange,text,boolean)
 function profile.get_report(character varying,text,boolean)
 function profile.get_report_context(integer,integer,integer,text,integer,integer)
 function profile.get_report_datasets(jsonb,integer)
 function profile.get_report(integer,integer,integer,text,boolean)
 function profile.get_report(integer,integer,text,boolean)
 function profile.get_report(integer,tstzrange,text,boolean)
 function profile.get_report_latest(name)
 function profile.get_report(name,character varying,text,boolean)
 function profile.get_report(name,integer,integer,text,boolean)
 function profile.get_report(name,tstzrange,text,boolean)
 function profile.get_report_template(jsonb,integer)
 function profile.get_report(tstzrange,text,boolean)
 function profile.get_sampleids_by_timerange(integer,tstzrange)
 function profile.get_server_by_name(name)
 function profile.get_sized_bounds(integer,integer,integer)
 function profile.import_data(regclass,text)
 function profile.import_section_data_profile(refcursor,name,jsonb,jsonb,text[])
 function profile.import_section_data_subsample(refcursor,name,jsonb,jsonb,text[])
 function profile.init_sample(integer)
 function profile.keep_baseline(character varying,integer)
 function profile.keep_baseline(name,character varying,integer)
 function profile.mark_pg_stat_statements(integer,integer,integer)
 function profile.profile_checkavail_cluster_stats_reset(integer,integer,integer)
 function profile.profile_checkavail_dbstats_reset(integer,integer,integer)
 function profile.profile_checkavail_functions(integer,integer,integer)
 function profile.profile_checkavail_io_times(integer,integer,integer)
 function profile.profile_checkavail_planning_times(integer,integer,integer)
 function profile.profile_checkavail_rusage(integer,integer,integer)
 function profile.profile_checkavail_rusage_planstats(integer,integer,integer)
 function profile.profile_checkavail_sessionstats(integer,integer,integer)
 function profile.profile_checkavail_statements_jit_stats(integer,integer,integer)
 function profile.profile_checkavail_statements_temp_io_times(integer,integer,integer)
 function profile.profile_checkavail_statstatements(integer,integer,integer)
 function profile.profile_checkavail_stmt_cnt(integer,integer,integer)
 function profile.profile_checkavail_stmt_wal_bytes(integer,integer,integer)
 function profile.profile_checkavail_tbl_top_dead(integer,integer,integer)
 function profile.profile_checkavail_tbl_top_mods(integer,integer,integer)
 function profile.profile_checkavail_top_temp(integer,integer,integer)
 function profile.profile_checkavail_trg_functions(integer,integer,integer)
 function profile.profile_checkavail_wait_sampling_total(integer,integer,integer)
 function profile.profile_checkavail_walstats(integer,integer,integer)
 function profile.profile_checkavail_wal_stats_reset(integer,integer,integer)
 function profile.rename_server(name,name)
 function profile.report_active_queries_format(jsonb,integer,jsonb)
 function profile.report_queries_format(jsonb,integer,jsonb,integer,integer,integer,integer)
 function profile.sample_dbobj_delta(jsonb,integer,integer,integer,boolean)
 function profile.save_pg_stat_statements(integer,integer)
 function profile.section_apply_conditions(jsonb,jsonb)
 function profile.sections_jsonb(jsonb,integer,integer)
 function profile.set_server_connstr(name,text)
 function profile.set_server_db_exclude(name,name[])
 function profile.set_server_description(name,text)
 function profile.set_server_max_sample_age(name,integer)
 function profile.set_server_size_sampling(name,time with time zone,interval,interval)
 function profile.set_server_subsampling(name,boolean,interval,interval,integer,interval)
 function profile.settings_and_changes(integer,integer,integer)
 function profile.settings_format_diff(integer,integer,integer,integer,integer)
 function profile.settings_format(integer,integer,integer)
 function profile.show_baselines(name)
 function profile.show_samples(integer)
 function profile.show_samples(name,integer)
 function profile.show_servers()
 function profile.show_servers_size_sampling()
 function profile.snapshot()
 function profile.snapshot(name)
 function profile.stat_activity_agg_format(integer,integer,integer)
 function profile.stat_activity_agg_format(integer,integer,integer,integer,integer)
 function profile.stat_activity_agg(integer,integer,integer)
 function profile.stat_activity_states_format(integer,integer,integer)
 function profile.stat_activity_states_format(integer,integer,integer,integer,integer)
 function profile.stat_activity_states(integer,integer,integer)
 function profile.statements_dbstats_format_diff(integer,integer,integer,integer,integer)
 function profile.statements_dbstats_format(integer,integer,integer)
 function profile.statements_dbstats(integer,integer,integer)
 function profile.stmt_cnt_format_diff(integer,integer,integer,integer,integer)
 function profile.stmt_cnt_format(integer,integer,integer)
 function profile.stmt_cnt(integer,integer,integer)
 function profile.tablespace_stats_format_diff(integer,integer,integer,integer,integer)
 function profile.tablespace_stats_format(integer,integer,integer)
 function profile.tablespace_stats(integer,integer,integer)
 function profile.take_sample()
 function profile.take_sample(integer,boolean)
 function profile.take_sample(name,boolean)
 function profile.take_sample_subset(integer,integer)
 function profile.take_subsample()
 function profile.take_subsample(integer,jsonb)
 function profile.take_subsample(name)
 function profile.take_subsample_subset(integer,integer)
 function profile.top_functions_format_diff(integer,integer,integer,integer,integer)
 function profile.top_functions_format(integer,integer,integer)
 function profile.top_functions(integer,integer,integer)
 function profile.top_indexes_format_diff(integer,integer,integer,integer,integer)
 function profile.top_indexes_format(integer,integer,integer)
 function profile.top_indexes(integer,integer,integer)
 function profile.top_io_indexes_format_diff(integer,integer,integer,integer,integer)
 function profile.top_io_indexes_format(integer,integer,integer)
 function profile.top_io_indexes(integer,integer,integer)
 function profile.top_io_tables_format_diff(integer,integer,integer,integer,integer)
 function profile.top_io_tables_format(integer,integer,integer)
 function profile.top_io_tables(integer,integer,integer)
 function profile.top_kcache_statements(integer,integer,integer)
 function profile.top_rusage_statements_format_diff(integer,integer,integer,integer,integer)
 function profile.top_rusage_statements_format(integer,integer,integer)
 function profile.top_statements_format_diff(integer,integer,integer,integer,integer)
 function profile.top_statements_format(integer,integer,integer)
 function profile.top_statements(integer,integer,integer)
 function profile.top_tables_format_diff(integer,integer,integer,integer,integer)
 function profile.top_tables_format(integer,integer,integer)
 function profile.top_tables(integer,integer,integer)
 function profile.top_tbl_last_sample_format(integer,integer,integer)
 function profile.top_toasts(integer,integer,integer)
 function profile.top_wait_sampling_events_format_diff(integer,integer,integer,integer,integer)
 function profile.top_wait_sampling_events_format(integer,integer,integer)
 function profile.wait_sampling_total_stats_format_diff(integer,integer,integer,integer,integer)
 function profile.wait_sampling_total_stats_format(integer,integer,integer)
 function profile.wait_sampling_total_stats(integer,integer,integer)
 function profile.wal_stats_format(integer,integer,integer,integer,integer,numeric,numeric)
 function profile.wal_stats_format(integer,integer,integer,numeric)
 function profile.wal_stats(integer,integer,integer)
 function profile.wal_stats_reset_format_diff(integer,integer,integer,integer,integer)
 function profile.wal_stats_reset_format(integer,integer,integer)
 function profile.wal_stats_reset(integer,integer,integer)
 sequence profile.baselines_bl_id_seq
 sequence profile.servers_server_id_seq
 sequence profile.session_attr_sess_attr_id_seq
 table profile.act_query
 table profile.baselines
 table profile.bl_samples
 table profile.funcs_list
 table profile.import_queries_version_order
 table profile.indexes_list
 table profile.last_stat_activity
 table profile.last_stat_activity_count
 table profile.last_stat_activity_count_srv1
 table profile.last_stat_activity_srv1
 table profile.last_stat_archiver
 table profile.last_stat_cluster
 table profile.last_stat_database
 table profile.last_stat_database_srv1
 table profile.last_stat_indexes
 table profile.last_stat_indexes_srv1
 table profile.last_stat_io
 table profile.last_stat_kcache
 table profile.last_stat_kcache_srv1
 table profile.last_stat_slru
 table profile.last_stat_statements
 table profile.last_stat_statements_srv1
 table profile.last_stat_tables
 table profile.last_stat_tablespaces
 table profile.last_stat_tablespaces_srv1
 table profile.last_stat_tables_srv1
 table profile.last_stat_user_functions
 table profile.last_stat_user_functions_srv1
 table profile.last_stat_wal
 table profile.report
 table profile.report_static
 table profile.report_struct
 table profile.roles_list
 table profile.sample_act_backend
 table profile.sample_act_backend_state
 table profile.sample_act_statement
 table profile.sample_act_xact
 table profile.sample_kcache
 table profile.sample_kcache_total
 table profile.samples
 table profile.sample_settings
 table profile.sample_stat_activity_cnt
 table profile.sample_stat_archiver
 table profile.sample_stat_cluster
 table profile.sample_stat_database
 table profile.sample_statements
 table profile.sample_statements_total
 table profile.sample_stat_indexes
 table profile.sample_stat_indexes_total
 table profile.sample_stat_io
 table profile.sample_stat_slru
 table profile.sample_stat_tables
 table profile.sample_stat_tablespaces
 table profile.sample_stat_tables_total
 table profile.sample_stat_user_functions
 table profile.sample_stat_user_func_total
 table profile.sample_stat_wal
 table profile.sample_timings
 table profile.servers
 table profile.server_subsample
 table profile.session_attr
 table profile.stmt_list
 table profile.tables_list
 table profile.tablespaces_list
 table profile.wait_sampling_total
 view profile.v_sample_settings
 view profile.v_sample_stat_indexes
 view profile.v_sample_stat_tables
 view profile.v_sample_stat_tablespaces
 view profile.v_sample_stat_user_functions
 view profile.v_sample_timings
(256 rows)

三、功能演练

3.1、创建服务器链接

说明：主要用于收集集群内的服务器信息，所以需要提前创建好服务链接。

代码如下：

 
[postgres@Server ~]$ psql -Usy -dtestdb
psql (16.3)
Type "help" for help.

testdb=# SELECT profile.create_server('node1','host=192.168.1.37 dbname=testdb;user=sy;port=5432');
create_server
---------------
            2
(1 row)

testdb=#

3.2、查询创建的服务器链接

说明：默认情况下，本地服务器信息自动添加到该表中，另外新增的servername是node1。

代码如下：

 
testdb=# select * from profile.show_servers();
 server_name |                            connstr                            | enabled | max_sample_age |
description
-------------+---------------------------------------------------------------+---------+----------------+-
------------
 local       | dbname=postgres port=5432 host=localhost user=sy password=123 | t       |                |
 node1       | host=192.168.1.37 dbname=testdb;user=sy;port=5432             | t       |                |
(2 rows)

testdb=#

3.3、创建快照

说明：使用sy用户登录testdb数据库，执行创建快照

代码如下：

 
testdb=# SELECT profile.snapshot() ;
        snapshot
------------------------
 (local,OK,00:00:01.14)
(1 row)

3.4、查询快照

说明：使用sy用户登录testdb数据库，查看创建的快照信息

代码如下：

 

testdb=# select profile.show_samples();
           show_samples
-----------------------------------
 (1,"2024-07-05 23:17:56+08",t,,,)
(1 row)

testdb=#

3.5、添加测试数据

说明：使用sy用户登录testdb数据库，创建表t1，并添加1000条数据

代码如下：

 
testdb=# create table t1(id int);
CREATE TABLE
testdb=# insert into t1 values (generate_series(1,1000));
INSERT 0 1000
testdb=#

3.6、再次创建快照

说明：使用sy用户登录testdb数据库，再次创建快照

代码如下：

 
testdb=# 
testdb=# SELECT profile.snapshot();
       snapshot
-----------------------
 (local,OK,00:00:01.8)
(1 row)

testdb=#

3.7、再次查询快照

说明：使用sy用户登录testdb数据库，再次查询快照

代码如下：

 
testdb=# select profile.show_samples();
           show_samples
-----------------------------------
 (1,"2024-07-06 12:19:01+08",t,,,)
 (2,"2024-07-06 12:19:14+08",t,,,)
 (3,"2024-07-06 12:34:42+08",t,,,)
 (4,"2024-07-06 14:30:47+08",t,,,)
(4 rows)

testdb=#

3.8、查询快照详细信息

说明：使用sy用户登录testdb数据库，再次创建快照

代码如下：

 
testdb=# select * from profile.show_samples();
 sample |      sample_time       | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+------------------------+-----------------+---------------+----------------+-----------------
      1 | 2024-07-06 12:19:01+08 | t               |               |                |
      2 | 2024-07-06 12:19:14+08 | t               |               |                |
      3 | 2024-07-06 12:34:42+08 | t               |               |                |
      4 | 2024-07-06 14:30:47+08 | t               |               |                |
(4 rows)

testdb=#

3.9、导出收集的信息

说明：当您需要将收集的数据发送给系统研发人员时，您可以从pg_profile扩展的实例中导出导出收集的信息，此功能非常有用。

3.9.1、导出csv格式

代码如下：

 
testdb=# \copy (select * from profile.export_data()) to '/install/export.csv'
COPY 3265
testdb=# \q

导出效果图如下：

补充说明：
数据导出：数据通过函数export_data()导出为常规表。您可以使用任何方法从数据库中导出此表。例如，您可以使用psql的copy命令来获取单个.csv文件：testdb=# \copy (select * from export_data()) to 'export.csv'，默认情况下，export_data()函数将导出所有配置服务器的所有样本。

3.9.2、导出常规HTML报告

代码如下：

 

[postgres@Server ~]$ psql -dtestdb -Usy -Aqtc "SELECT profile.get_report(1,2)" -o /install/awr_report_2.html

导出效果图如下：

3.9.3、导出其他服务器的HTML报告

代码如下：

 
[postgres@Server ~]$ psql -dtestdb -Usy -Aqtc "SELECT profile.get_report('local',1,2)" -o /install/awr_rep
ort_5.html
[postgres@Server ~]$

导出效果图如下：

3.9.4、使用时间范围导出HTML报告

代码如下：

 
[postgres@Server ~]$ psql -dtestdb -Usy -Aqtc "select profile.get_report(tstzrange('2024-07-05 17:51:35+03','2024-07-07 15:52:18+03'))" -o  /install/awr_report_range.html
[postgres@Server ~]$

导出效果图如下：

3.9.5、定期生成HTML报告

说明：时间范围对于生成定期报告也很有用。构建过去 24 小时的报告。

代码如下：

 

[postgres@Server ~]$ psql -dtestdb -Usy -Aqtc "select profile.get_report(tstzrange(now() - interval '1 day',now()))" -o /install/last24h_report.html
[postgres@Server ~]$

导出效果图如下：

补充说明：
报告由报告函数以 HTML 标记生成。 pg_profile中有两种类型的报告：
定期报告，包含报告间隔期间实例工作负载的统计信息
差异报告，包含两个间隔的数据，其中相同对象的统计值彼此相邻，便于比较工作量
定期报告功能
get_report([服务器名称，] start_id 整数，end_id 整数 [，描述文本 [，with_growth 布尔值]]) - 根据样本标识符生成报告
get_report([服务器名称，] time_range tstzrange [, 描述文本 [, with_growth boolean]]) - 生成最短采样间隔的报告，涵盖提供的time_range。
get_report([服务器名称], baseline varchar(25) [, description text [, with_growth boolean]]) - 生成报告，使用基线作为样本间隔 -get_report_latest（[服务器名称]） - 生成两个最新样本的报告函数参数：
服务器- 服务器名称。如果省略，则假定为本地服务器
start_id - 间隔开始样本标识符
end_id - 间隔结束样本标识符 -time_range - 时间范围（tstzrange类型） -baseline - 基线名称 -with_growth - 一个标志，请求将间隔扩展到最近的边界，并提供关系增长数据。默认值为false -描述- 文本备忘录，它将作为报告描述包含在报告中
差异报告功能
您可以使用样本标识符、基线和时间范围作为间隔界限来生成差异报告：
get_diffreport([服务器名称，] start1_id 整数，end1_id 整数，start2_id 整数，end2_id 整数 [，描述文本 [，with_growth 布尔值]])>- 根据样本标识符生成两个间隔的差异报告
get_diffreport([服务器名称，] baseline1 varchar(25)，baseline2 varchar(25) [, 描述文本 [, with_growth boolean]]) - 生成由 >- >- basename 名称定义的两个间隔的差异报告
get_diffreport([服务器名称，] time_range1 tstzrange，time_range2 tstzrange [，描述文本 [，with_growth boolean]]) - 生成由时间范>-围定义的两个间隔的差异报告
server是服务器名称。若省略则假定为本地服务器
start1_id，end1_id - 第一个间隔的样本标识符
start2_id，end2_id - 第二个间隔的样本标识符
baseline1 - 第一个间隔的基线名称
baseline2 - 第二个间隔的基线名称
time_range1 - 第一个间隔时间范围
time_range2 - 秒间隔时间范围
描述是文本备忘录 - 它将作为报告描述包含在报告中
owith_growth是一个标志，要求将两个区间扩展到最近的边界，并提供关系增长数据。默认值为false
此外，您还可以使用上述某些组合：
get_diffreport([服务器名称,] baseline varchar(25), time_range tstzrange [, 描述文本 [, with_growth 布尔值]])
get_diffreport([服务器名称,] time_range tstzrange, baseline varchar(25) [, 描述文本 [, with_growth 布尔值]])
et_diffreport([服务器名称,] start1_id 整数, end1_id 整数, baseline varchar(25) [, 描述文本 [, with_growth 布尔值]])
get_diffreport([服务器名称,] baseline varchar(25), start2_id 整数, end2_id 整数 [, 描述文本 [, with_growth 布尔值]])

四、问题汇总

1、权限问题

说明：创建扩展的账户必须有权限操作扩展，因此在创建扩展前必须确定扩展安装库、扩展安装的用户，再次提醒，登录账户需要有权限创建扩展。

2、登录问题

使用profile.get_report创建链接后，如指定了IP地址，需在pg_hba.conf中配置相应的账户、IP地址。

五、总结

5.1、使用pg_profile进行性能分析的主要功能包括：

生成快照：
通过调用profile.snapshot()函数可以创建一个快照，该快照包含了数据库在某一特定时间的性能状态。
查看快照：
可以使用profile.show_samples()函数查看所有的快照样本。
生成报告：
pg_profile提供了多种报告类型，包括普通报告和对比报告。普通报告用于展示单个快照的性能数据，而对比报告则展示了不同快照间性能数据的差异。
报告解读：
生成的报告包含了丰富的性能指标，如执行时间最长的前几条SQL语句、最活跃的用户、最繁忙的索引等，这些信息有助于用户快速定位性能问题并进行优化。

最后修改时间：2024-07-08 15:17:46

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

信创数据库学习中心（共80篇）

信创数据库学习中心

PostgreSQL数据库最强大的信息收集工具—pg_profile

一、pg_profile插件功能概述

1.1、pg_profile插件功能概述

1.2、扩展架构

二、环境搭建

2.1、pg_profile 扩展先决条件

2.2、安装扩展pg_profile

2.3、安装扩展dblink

2.4、安装扩展pg_stat_statements

2.5、配置postgresql.conf

说明：

2.6、安装扩展

2.7、查看扩展

2.8、查看pg_profile扩展相关功能

三、功能演练

3.1、创建服务器链接

3.2、查询创建的服务器链接

3.3、创建快照

3.4、查询快照

3.5、添加测试数据

3.6、再次创建快照

3.7、再次查询快照

3.8、查询快照详细信息

3.9、导出收集的信息

3.9.1、导出csv格式

导出效果图如下：

3.9.2、导出常规HTML报告

导出效果图如下：

3.9.3、导出其他服务器的HTML报告

导出效果图如下：

3.9.4、使用时间范围导出HTML报告

导出效果图如下：

3.9.5、定期生成HTML报告

导出效果图如下：

补充说明：

定期报告功能

差异报告功能

四、问题汇总

1、权限问题

2、登录问题

五、总结

5.1、使用pg_profile进行性能分析的主要功能包括：

生成快照：

查看快照：

生成报告：

报告解读：

文章被以下合辑收录

评论