PostgreSQL的AWR(PG_PROFILE)

PostgreSQL参数配置

如下配置为使用pg_profile最基本的配置

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'top'
pg_stat_statements.save = on

track_activities = on
track_counts = on
track_io_timing = on
track_functions = all

安装扩展文件

$ tar xzf pg_profile--0.3.4.tar.gz --directory $(pg_config --sharedir)/extension

创建扩展

为便于管理，建议创建单独的schema，本文创建了profile

postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
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;
-[ RECORD 1 ]-------------------------
search_path | "$user", public, profile

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

pg_profile相关对象

postgres=# \d
                      List of relations
 Schema  |             Name             |   Type   |  Owner
---------+------------------------------+----------+----------
 profile | baselines                    | table    | postgres
 profile | baselines_bl_id_seq          | sequence | postgres
 profile | bl_samples                   | table    | postgres
 profile | funcs_list                   | table    | postgres
 profile | import_queries               | table    | postgres
 profile | import_queries_version_order | table    | postgres
 profile | indexes_list                 | table    | postgres
 profile | last_stat_archiver           | table    | postgres
 profile | last_stat_cluster            | table    | postgres
 profile | last_stat_database           | table    | postgres
 profile | last_stat_indexes            | table    | postgres
 profile | last_stat_tables             | table    | postgres
 profile | last_stat_tablespaces        | table    | postgres
 profile | last_stat_user_functions     | table    | postgres
 profile | last_stat_wal                | table    | postgres
 profile | roles_list                   | table    | postgres
 profile | sample_kcache                | table    | postgres
 profile | sample_kcache_total          | table    | postgres
 profile | sample_settings              | table    | postgres
 profile | sample_stat_archiver         | table    | postgres
 profile | sample_stat_cluster          | table    | postgres
 profile | sample_stat_database         | table    | postgres
 profile | sample_stat_indexes          | table    | postgres
 profile | sample_stat_indexes_total    | table    | postgres
 profile | sample_stat_tables           | table    | postgres
 profile | sample_stat_tables_total     | table    | postgres
 profile | sample_stat_tablespaces      | table    | postgres
 profile | sample_stat_user_func_total  | table    | postgres
 profile | sample_stat_user_functions   | table    | postgres
 profile | sample_stat_wal              | table    | postgres
 profile | sample_statements            | table    | postgres
 profile | sample_statements_total      | table    | postgres
 profile | sample_timings               | table    | postgres
 profile | samples                      | table    | postgres
 profile | servers                      | table    | postgres
 profile | servers_server_id_seq        | sequence | postgres
 profile | stmt_list                    | table    | postgres
 profile | tables_list                  | table    | postgres
 profile | tablespaces_list             | table    | postgres
 profile | v_sample_settings            | view     | postgres
 profile | v_sample_stat_indexes        | view     | postgres
 profile | v_sample_stat_tables         | view     | postgres
 profile | v_sample_stat_tablespaces    | view     | postgres
 profile | v_sample_stat_user_functions | view     | postgres
 profile | v_sample_timings             | view     | postgres
(45 rows)

创建快照

postgres=# select * from profile.take_sample();
 server | result |   elapsed
--------+--------+-------------
 local  | OK     | 00:00:00.52
(1 row)

postgres=# select show_samples();
           show_samples
-----------------------------------
 (1,"2022-04-23 22:11:58+08",t,,,)
 (2,"2022-04-23 22:13:33+08",t,,,)
 (3,"2022-04-23 22:19:55+08",t,,,)
 (4,"2022-04-23 22:19:57+08",t,,,)
 (5,"2022-04-23 22:19:58+08",t,,,)
 (6,"2022-04-23 22:19:59+08",t,,,)
 (7,"2022-04-23 22:20:00+08",t,,,)
 (8,"2022-04-23 22:29:27+08",t,,,)
 (9,"2022-04-23 22:44:52+08",t,,,)
(9 rows)

生成报告

$ psql -c "select show_samples()"

普通报告

仅需要两个快照的ID即可

$ psql -Aqtc "SELECT get_report(1,2)" -o report_pg_1_2.html

差异报告

需要输入四个快照ID，前两个为一组，后两个为一组

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

创建快照加入定时任务
间隔为30分钟，时间越短准确定越高

$ crontab -e
*/30 * * * *   psql -c 'SELECT profile.take_sample()' > /dev/null 2>&1

彩蛋是普通报告及差异报告样例

链接：https://pan.baidu.com/s/1uzY7NXwQjV_6D6OT2V4IaA
提取码：v2rj