PG 的 AWR pg_profile

  一、什么是PG_PROFILE

在postgres中，当遇到问题时，可能需要回顾不止一件事情来分析数据库或者整个集群，包括索引，I/O，CPU等

pg_profile 扩展是基于postgres的标准统计视图。它类似于Oracle AWR架构，和Oracle一样，它在指定时间生成快照，并切提供html格式来解释快照之间的统计数据

二、配置postgresql.conf

track_activities = on

 track_counts = on

 track_io_timing = on

 track_wal_io_timing = on      # Since Postgres 14

 track_functions = all

 log_truncate_on_rotation = on

 shared_preload_libraries = 'pg_stat_statements'

 pg_stat_statements.max = 1000

 pg_stat_statements.track = 'top'

 pg_stat_statements.save = off

 pg_profile.topn = 20

 pg_profile.retention = 7

track_activities 和track_counts默认是打开的。track_activities允许监控当前被任意服务器进程执行的命令。track_counts控制是否收集关于表和索引访问的统计信息。track_io_timing启用对块读写次数的监控。参数track_functions启用对用户定义函数使用的跟踪。

pg_stat_statements.max 是跟踪语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果语句超过这个数量，最少被执行的语句的信息将会被丢弃。作者说此参数的设置太低可能会导致在抽取样本之前会清除某些语句的统计信息。建议设置大一些。当前参数我们设置的10000，足够使用。

pg_stat_statements.track 指定top可以跟踪顶层语句（直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none则禁用语句统计信息收集。默认值是top。如果你设置的是all，作者说可能会影响报告中%Totalfields字段的值。上面参数修改稍为简单，因为基本上都是默认打开的。只有少数没开，打开也不需要重启。

下载并安装pg_profile

三、下载并安装pg_profile

https://github.com/zubkov-andrei/pg_profile/releases

目前是最新是4.1

Tar –xvf pg_profile--4.1.tar.gz

pg_profile--4.0--4.1.sql  pg_profile--4.1.sql  pg_profile--4.1.tar.gz  pg_profile.control

cp *.sql *.control /pg/soft/share/postgresql/extension

这里有两种方式安装，一种是公共模式安装，这种安装方式最简单。第二种是独立Schema安装，这种安装将在自己创建的Schema中建自己的表，视图，序列和函数，可以和其他用户有效的进行隔离。

postgres=# CREATE EXTENSION dblink;

postgres=# CREATE EXTENSION pg_stat_statements;

postgres=# CREATE SCHEMA profile;

postgres=# CREATE EXTENSION pg_profile SCHEMA profile;

四、创建快照和生产报告

接下来我们可以执行函数创建快照。

postgres=# select profile.show_samples();

           show_samples           

-----------------------------------

 (1,"2023-03-31 15:59:45+08",t,,,)

 (2,"2023-03-31 15:59:55+08",t,,,)

 (3,"2023-03-31 16:00:16+08",t,,,)

 (4,"2023-03-31 16:03:13+08",t,,,)

 (5,"2023-03-31 16:22:47+08",t,,,)

(5 rows)

执行profile.get_report命令，取2个时间段就可以生成AWR报告了。如果要生产对比报告，可以执行profile.get_diffreport命令。

psql -qtc "select profile.get_report(8,9)"  --output awr_report_postgres_8_9.html

如果要每半个小时生成快照，需要把脚本放在contab下执行。

*/30 * * * *  psql -c 'SELECT profile.snapshot()' > /pg/pg_awr.log  2>&1