一.pg_profile介绍

在postgres中，当遇到问题时，可能需要回顾不止一件事情来分析数据库或者整个集群，包括索引，I/O，CPU等，往往缺乏手段。在Oracle中，可以通过AWR报告来发现问题。而在PG中，也有类似AWR的工具pg_profile，pg_profile扩展是基于postgres的标准统计视图。这款工具相对较简单，和Oracle AWR报告工作方式很像，在指定时间生成快照，并切提供html格式来解释快照之间的统计数据，而且它最大的优点是用纯粹pl/pgsql编写，勿需使用任何外部库或者软件。

二.安装前准备

2.1.数据库配置

2.1.1.track相关参数配置

配置数据库统计信息收集器参数，具体配置参数如下：

/pgdb/data/postgres.conf
#增加track相关参数
track_activities = on #默认on 
track_counts = on #默认打开  
track_io_timing = on #默认off  跟踪IO消耗的时间
track_functions = all # none, pl, all default none 
                       none:不收集
                       pl:只收集过程语言函数
                       all:收集所有的函数

这四个参数建议打开，不然使用会有一些影响。

2.1.2.pg_stat_statements配置

vi /pgdb/data/postgres.conf
#加载动态库
shared_preload_libraries = 'pg_stat_statements'  #修改后需重启生效
#增加pg_stat_statements相关参数
pg_stat_statements.max = 10000 #最多保留多少条统计信息，通过LRU算法，覆盖老的记录（即pg_stat_statements视图中行的最大数量）
pg_stat_statements.track = all #跟踪顶层语句（直接由客户端发出的语句)
pg_stat_statements.track_utility = on 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪
pg_stat_statements.save = on  重启后是否保留统计信息

参数说明：
pg_stat_statements.max是跟踪语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果语句超过这个数量，最少被执行的语句的信息将会被丢弃。作者说此参数的设置太低可能会导致在抽取样本之前会清除某些语句的统计信息。建议设置大一些。当前参数设置的10000，足够使用。
pg_stat_statements.track指定top可以跟踪顶层语句（直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none则禁用语句统计信息收集。默认值是top。如果你设置的是all，作者说可能会影响报告中%Totalfields字段的精确值。

2.1.3.重启库

--重启库
pg_ctl stop -D /pgdb/data/
pg_ctl start -D /pgdb/data

三.下载

开源地址：https://github.com/zubkov-andrei/pg_profile/releases

文档下载的是tar包格式，视情况选择安装包。
下载后上传至/home/postgres目录下

四.安装pg_profile

4.1.解压至PG安装目录下

提供的文件 pg_profile–4.3.tar.gz 将解压到 postgresql 安装目录的扩展文件夹中：

--官方步骤
tar xzf pg_profile--4.3.tar.gz --directory $(pg_config --sharedir)/extension

--示例
tar xzf pg_profile--4.3.tar.gz --directory /pgdb/pgsql/share/extension/

详细信息：

[root@node195 opt]# tar -xzvf pg_profile--4.3.tar.gz  -C /usr/pgsql-14/share/extension/
pg_profile--4.3.sql
pg_profile.control
pg_profile--4.2--4.3.sql

4.2.创建扩展

4.2.1.创建扩展dblink和pg_stat_statements

数据库中需要 plpgsql 和 dblink 扩展，如果需要语句统计信息，则需要 pg_stat_statements扩展。
dblink和pg_stat_statements属于系统自带插件

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

4.2.2.创建扩展profile

在 postgresql 数据库中创建扩展：

postgres=# CREATE SCHEMA profile;
postgres=# CREATE EXTENSION pg_profile SCHEMA profile;
postgres=# \dn
  List of schemas
  Name   |  Owner
---------+----------
 profile | postgres
 public  | postgres
(2 rows)

不想指定模式限定符，可以设置search_path

postgres=# set search_path="$user",public,profile;
SET
postgres=# show search_path;
       search_path        
--------------------------
 "$user", public, profile
(1 row)

postgres=# alter user postgres set search_path to "$user", public,profile;
ALTER ROLE

如果数据库中安装了以前版本的 pg_profile，则可以对其进行更新：

ALTER EXTENSION pg_profile UPDATE;

4.2.3.安装pg_stat_kcache插件并创建扩展

4.2.3.1.pg_stat_kcache介绍

PostgreSQL中一般建议将shared_buffer值设置成物理内存的1/4，而是要给OS的cache/buffer以足够的空间，从而达到最佳的系统性能。
因此通过使用buffer io, shared buffer的统计信息很容易判断错误。假如shared_buffer设置的过小，那么在统计信息中发现SQL的shared_buffer命中率很低，很可能该SQL的read都发生在操作系统的cache buffer中，而并没有进行disk io，因为这些行为是file system接口来完成的, 所以数据库内核并不知情。
pg_stat_kcache插件可以为避免这一问题，可以查看到每个查询，用户或数据库消耗了多少系统资源，可以更真实的分析出SQL消耗的性能。 包括：

1. CPU（user and system）
2. Disk IO（read and write）

Pyhsical disk access对于计算真实命中率（cached_reads / all_reads）是必不可少的。 没有这个，只能计算shared_buffers的命中率，而知道，PostgreSQL是十分倚靠page cache的，某些读取可以由操作的系统缓存来处理。
限制：PostgreSQL Version ≥ 9.4，同时需配合pg_stat_statements插件，另外需要在任何Posix的操作系统之上，所以windows暂不支持

4.2.3.2.pg_stat_kcache下载

下载地址：https://github.com/powa-team/pg_stat_kcache
上传至/home/postgres

4.2.3.3.pg_stat_kcache安装

pg源码安装场景下安装pg_stat_kcache

提供的文件 pg_stat_kcache-master.zip 将解压到 postgresql 安装目录的扩展文件夹中：

#编译安装
su - postgres
unzip pg_stat_kcache-master.zip -d /pgdb/pgsql/share/extension/
cd /pgdb/pgsql/share/extension/pg_stat_kcache-master
make
make install

详细过程如下：

[postgres@localhost pg_stat_kcache-master]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pgdb/pgsql/include/server -I/pgdb/pgsql/include/internal  -D_GNU_SOURCE   -c -o pg_stat_kcache.o pg_stat_kcache.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_stat_kcache.so pg_stat_kcache.o -L/pgdb/pgsql/lib    -Wl,--as-needed -Wl,-rpath,'/pgdb/pgsql/lib',--enable-new-dtags  
[postgres@localhost pg_stat_kcache-master]$ make install
/usr/bin/mkdir -p '/pgdb/pgsql/lib'
/usr/bin/mkdir -p '/pgdb/pgsql/share/extension'
/usr/bin/mkdir -p '/pgdb/pgsql/share/extension'
/usr/bin/install -c -m 755  pg_stat_kcache.so '/pgdb/pgsql/lib/pg_stat_kcache.so'
/usr/bin/install -c -m 644 .//pg_stat_kcache.control '/pgdb/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_stat_kcache--2.1.0.sql .//pg_stat_kcache--2.1.1.sql .//pg_stat_kcache--2.2.0.sql .//pg_stat_kcache--2.2.0--2.2.1.sql .//pg_stat_kcache--2.2.1--2.2.2.sql .//pg_stat_kcache--2.1.2--2.1.3.sql .//pg_stat_kcache--2.1.1--2.1.2.sql .//pg_stat_kcache--2.1.0--2.1.1.sql .//pg_stat_kcache--2.2.2.sql .//pg_stat_kcache--2.1.3--2.2.0.sql .//pg_stat_kcache--2.1.2.sql .//pg_stat_kcache--2.1.3.sql .//pg_stat_kcache--2.2.1.sql  '/pgdb/pgsql/share/extension/'

pgrpm安装场景下安装pg_stat_kcache

将以下图片中的文件分别上传至 /pgdb/pgsql/lib、/pgdb/pgsql/share/extension并分配权限
pg_stat_kcache.so —> /pgdb/pgsql/lib chmod 755 /pgdb/pgsql/lib/pg_stat_kcache.so
pg_stat_kcache*—>/pgdb/pgsql/share/extension chmod 644 /pgdb/pgsql/share/extension/

4.2.3.4.pg_stat_kcache配置

--加载动态库
vi /pgdb/data/postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'  # (change requires restart)

--重启库
pg_ctl stop -D /pgdb/data/
pg_ctl start -D /pgdb/data

--创建扩展
postgres=# CREATE EXTENSION pg_stat_kcache;
CREATE EXTENSION

pg_stat_kcache总共提供了三个GUC参数，in postgresql.conf:：

• pg_stat_kcache.linux_hz：通知pg_stat_kcache Linux CONFIG_HZ 配置选项。pg_stat_kcache使用它来补偿采样误差。默认值为 -1，尝试在启动时猜测它。
• pg_stat_kcache.track：控制pg_stat_kcache跟踪哪些语句。指定 top 可跟踪顶级语句（由客户端直接发出的语句），指定 all 可同时跟踪嵌套语句（例如在函数中调用的语句），或指定 none 可禁用语句统计信息收集。
• pg_stat_kcache.track_planning：是否跟踪planning的操作和持续时间，默认off(需要Version ≥13)

安装完成后可以通过pg_stat_kcache和pg_stat_kcache_detail这两个视图来进行查看。
pg_stat_kcache提供的是整体运行状况的视图，而pg_stat_kcache_detail则是细化到语句级。

4.2.3.5.pg_stat_kcache使用

--重置pg_stat_kcache收集的统计信息。可以由超级用户调用：
select pg_stat_kcache_reset();

--pg_stat_kcache提供的是整体运行状况的视图(转储共享内存结构的计数器)
select * from pg_stat_kcache limit 1;

主要说明下plan_minflts和plan_majflts这两个字段的含义。

• plan_minflts：生成执行计划时minor page fault次数。
• plan_majflts：生产执行计划时major page fault次数。

这里简单说明下minor page fault和major page fault的区别：
page fault缺页异常分为两种类型，一种叫做major page fault，这种类型的缺页可以通过 Disk IO来满足，另一种叫做minor page fault，这种缺页可以直接利用内存中的缓存页满足。
如果访问一个地址时，与该地址空间虚拟内存绑定的数据还存在于Disk上，那么此时即会触发一次major fault；如果访问一个地址时，与之绑定的虚拟内存对应的地址空间已经被内核加载到了Page Cache中，那么此时只需要把该Page映射到虚拟内存中即可，这种异常即为一次minor fault。

--查看数据库中最消耗CPU的语句
select * from pg_stat_kcache_detail order by exec_user_time desc limit 1;

五.创建快照

5.1.创建快照

--执行函数创建快照
postgres=# select * from take_sample();
 server | result |   elapsed   
--------+--------+-------------
 local  | OK     | 00:00:00.56
(1 row)


--也可以调用SELECT profile.snapshot()来生成快照，pg_profile 0.1.2开始snapshot()函数重命名为take_sample()

--通过profile.show_samples()函数，查看生成的快照
postgres=# select profile.show_samples();
           show_samples            
-----------------------------------
 (1,"2023-12-17 08:19:27+08",t,,,)

5.2.查看快照列表

，执行profile.get_report命令，取2个时间段就可以生成AWR报告。

--查看快照，当前总共有20个快照
postgres=#  select profile.show_samples();
                                                         show_samples                                                          
-------------------------------------------------------------------------------------------------------------------------------
 (1,"2023-12-17 08:19:27+08",t,,,)
 (2,"2023-12-17 08:19:39+08",t,,,)
 (3,"2023-12-17 08:59:59+08",t,,,)
 (4,"2023-12-17 09:02:30+08",t,,,)
 (5,"2023-12-17 09:10:39+08",t,,,)
 (6,"2023-12-17 09:18:01+08",t,,,)
 (7,"2023-12-17 09:19:01+08",t,,,)
 (8,"2023-12-17 09:20:02+08",t,,,)
 (9,"2023-12-17 09:21:02+08",t,,,)
 (10,"2023-12-17 09:22:02+08",t,,,)
 (11,"2023-12-17 09:23:02+08",t,,,)
 (12,"2023-12-17 09:24:01+08",t,,,)
 (13,"2023-12-17 09:25:01+08",t,,,)
 (14,"2023-12-17 10:48:02+08",t,"2023-12-17 10:48:01.877019+08","2023-12-17 10:47:57.85121+08","2023-12-17 10:47:57.85121+08")
 (15,"2023-12-17 10:48:31+08",t,,,)
 (16,"2023-12-17 10:49:01+08",t,,,)
 (17,"2023-12-17 12:30:03+08",t,,,)
 (18,"2023-12-17 12:35:32+08",t,,,)
 (19,"2023-12-17 12:50:02+08",t,,,)
 (20,"2023-12-17 12:55:02+08",t,,,)
(20 rows)

5.3.获取时间段之间的AWR报告

–获取2个时间段之间的AWR报告(18,20为两个快照的ID)

psql -Aqtc "select profile.get_report(10,20)"  -o awr_report_postgres_10_20.html

5.4.生成时间段之间对比的报告

如果要生产对比报告，可以执行profile.get_diffreport命令。

$ psql -Aqtc "SELECT get_diffreport(14,16,8,9)" -o diffreport_pg_1_2_8_9.html

说明：1,2,8,9为四个快照ID，前两个为一组，后两个为一组

5.5.实现定时创建快照

间隔为30分钟，时间越短准确定越高

--设置每半个小时生成1次快照，需要把脚本放在contab下执行
  crontab -e 输入以下内容：
 #add by pg_awr 每半个小时生成1次快照
*/30 * * * *  /pgdb/pgsql/bin/psql -p 5439 -c 'SELECT profile.snapshot()' >> /home/postgres/pg_awr.log  2>&1

 --设置每1个小时生成1次快照，需要把脚本放在contab下执行
  crontab -e 输入以下内容：
 #add by pg_awr 每1个小时生成1次快照
 0 */1 * * *  /pgdb/pgsql/bin/psql -p 5439 -c 'SELECT profile.snapshot()' >> /home/postgres/pg_awr.log  2>&1

六、浏览报告

• 报告总共有六个部分

Report sections

• Server statistics
  • Database statistics
  • Cluster SLRU statistics
  • Session statistics by database
  • Statement statistics by database
  • Cluster statistics
  • WAL statistics
  • Tablespace statistics
• SQL query statistics
  • Top SQL by execution time
  • Top SQL by executions
  • Top SQL by I/O wait time
  • Top SQL by shared blocks fetched
  • Top SQL by shared blocks read
  • Top SQL by shared blocks dirtied
  • Top SQL by shared blocks written
  • Top SQL by WAL size
  • Complete list of SQL texts
• Schema object statistics
  • Top tables by estimated sequentially scanned volume
  • Top tables by blocks fetched
  • Top tables by blocks read
  • Top DML tables
  • Top tables by updated/deleted tuples
  • Top growing tables
  • Top indexes by blocks fetched
  • Top indexes by blocks read
  • Top growing indexes
  • Unused indexes
• User function statistics
  • Top functions by total time
  • Top functions by executions
• Vacuum-related statistics
  • Top tables by vacuum operations
  • Top tables by analyze operations
  • Top indexes by estimated vacuum load
• Cluster settings during the report interval
• 第一部分是服务器统计，包含了整个数据库再此快照期间的统计信息，例如事务数，内存名中率，元组的操作统计数据，以及数据库调用次数数据库聚簇的统计信息，表空间增长等等

• 第二部分是TopSQL，包括执行时长，次数，执行消耗的io，以及逻辑读等topN的排序，还包括完整的sql，根据Query ID可以查看具体的SQL。这里根据rows和executions都是10208可能是同一个事务生成

• 第三部分是Schema的对象统计，包含了Top对象的信息，类似OracleAWR报告的segmentstatistics部分，从这里我们可以定位到DML最频繁的表，增长最快的表等等

• 第四部分是函数的统计，可以看到调用的次数以及消耗的时间

• 第五部分是vacuum相关的统计

• 第六部分是报告快照期间的参数设置

总结

• 这个工具结合了pg_stat_statement可以分析具体的sql，最大的作用是用来抓取数据库异常的这段时间的详细数据，用于辅助分析数据库问题

尊重原创，致谢原创
参考原创链接如下：
https://www.modb.pro/db/37859
https://www.cnblogs.com/zhangfx01/p/15587596.html
https://juejin.cn/post/7087402509689421854
https://blog.csdn.net/u014650965/article/details/124373011
https://blog.csdn.net/u014650965/article/details/124373011