PG AWR(pg_profile)实战攻略
PG AWR(pg_profile)实战攻略
目录
一、pg_profile用途
pg_profile基于postgres标准统计视图，通过捕获数据库中被任意服务器进程执行的命令、收集关于表和索引访问的统计信息、监控对块读写次数、对用户定义函数使用的跟踪等四个方面来进行性能分析汇总展示。它类似于Oracle AWR架构，指定时间间隔生成快照，并提供html文档进行分析。
二、安装部署
PG版本号
PostgreSQL 12.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
Pg_profile版本号
pg_profile0.3.6
Github链接地址
https://github.com/zubkov-andrei/pg_profile/releases/tag/0.3.6
1.依赖extension
默认pg_profile需要使用dblink和pg_stat_statements扩展包，这两个属于系统自带插件。
2.安装部署
2.1安装pg_profile驱动
–postgres用户：
(For pg12)######################################For12
tar -zxvf pg_profile–0.3.6.tar.gz
cp pg_profile* /usr/local/pgsq/share/extension
2.2配置参数
vi postgresql.conf
###pg_stat_statements
shared_preload_libraries = 'pg_stat_statements.so'
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
2.3创建extension
–pg_profile/pg_sata_statements/db_link
postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE SCHEMA profile;
postgres=# CREATE EXTENSION pg_profile SCHEMA profile;
查看插件：
–查看extension
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------±--------±-----------±-------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
pg_buffercache | 1.3 | public | examine the shared buffer cache
pg_prewarm | 1.2 | public | prewarm relation data
pg_profile | 4.2 | profile | PostgreSQL load profile repository and report builder
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(7 rows)
postgres=# \dx+ pg_profile
Objects in extension “pg_profile”
Object description
-----------------------------------------------------------------------------------------------------------
function profile.check_stmt_all_setting(integer,integer,integer)
function profile.check_stmt_cnt(integer,integer,integer)
function profile.cluster_stats(integer,integer,integer)
function profile.cluster_stats_diff_htbl(jsonb,integer,integer,integer,integer,integer)
function profile.cluster_stats_htbl(jsonb,integer,integer,integer)
function profile.cluster_stats_reset(integer,integer,integer)
function profile.cluster_stats_reset_diff_htbl(jsonb,integer,integer,integer,integer,integer)
function profile.cluster_stats_reset_htbl(jsonb,integer,integer,integer)
function profile.collect_obj_stats(jsonb,integer,integer,text,boolean)
function profile.collect_pg_stat_statements_stats(jsonb,integer,integer,integer)
function profile.collect_queries(oid,oid,bigint)
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.dbstats(integer,integer,integer,integer)
function profile.dbstats_diff_htbl(jsonb,integer,integer,integer,integer,integer,integer)
function profile.dbstats_htbl(jsonb,integer,integer,integer,integer)
function profile.dbstats_reset(integer,integer,integer)
function profile.dbstats_reset_diff_htbl(jsonb,integer,integer,integer,integer,integer)
function profile.dbstats_reset_htbl(jsonb,integer,integer,integer)
……
table profile.sample_settings
table profile.sample_stat_archiver
table profile.sample_stat_cluster
table profile.sample_stat_database
table profile.sample_stat_indexes
table profile.sample_stat_indexes_total
table profile.sample_stat_tables
table profile.sample_stat_tables_total
table profile.sample_stat_tablespaces
table profile.sample_stat_user_func_total
table profile.sample_stat_user_functions
table profile.sample_stat_wal
table profile.sample_statements
table profile.sample_statements_total
table profile.sample_timings
table profile.samples
table profile.servers
table profile.stmt_list
table profile.tables_list
table profile.tablespaces_list
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
(217 rows)
3.创建快照
postgres=# SELECT profile.snapshot() ;
snapshot
------------------------
(local,OK,00:00:00.64)
(1 row)
–自动快照crontab
4.查询快照
postgres=# select profile.show_samples();
show_samples
-----------------------------------
(1,“2023-07-18 15:59:31-07”,t,)
(2,“2023-07-18 15:59:33-07”,t,)
(3,“2023-07-18 15:59:35-07”,t,)
(4,“2023-07-18 15:59:36-07”,t,)
(5,“2023-07-18 15:59:38-07”,t,)
(6,“2023-07-18 15:59:41-07”,t,)
(7,“2023-07-18 15:59:45-07”,t,)
(7 rows)
postgres=# select * from profile.show_samples();
sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------±-----------------------±----------------±--------------±---------------±----------------
1 | 2023-07-18 09:13:24-07 | t | | |
2 | 2023-07-18 09:13:26-07 | t | | |
3 | 2023-07-18 09:13:27-07 | t | | |
4 | 2023-07-18 09:13:28-07 | t | | |
5 | 2023-07-18 09:13:29-07 | t | | |
6 | 2023-07-18 09:13:29-07 | t | | |
7 | 2023-07-18 09:13:30-07 | t | | |
8 | 2023-07-18 09:14:13-07 | t | | |
9 | 2023-07-18 09:14:15-07 | t | | |
(9 rows)
5.awr报告
5.1awr报告生成
psql -qtc “select profile.get_report(1,7)” -o 1-7.html
1-7.html
5.2awr报告对比
psql -qtc “select profile.get_diffreport(1,2,3,4)” -o awr_report_postgres_16_17.html
6. 异常处理
获取快照报错：“could not establish connection”
//pg_profile–0.3.6.sql
1）源码找到138行
CREATE FUNCTION take_sample(IN sserver_id integer, IN skip_sizes boolean
– Server connection
PERFORM dblink_connect(‘server_connection’,server_connstr); --FUNCTION take_sample -->line 138
2）错误分析
调用dblink_connect方法无法连接数据库
3）查看配置pg_hba.conf
host all all 0.0.0.0/0 md5
4）修改认证方式trust
host all all 0.0.0.0/0 trust
5）问题解决
三、报告解析
分析报告从16:08开始，到16:32结束持续24分钟：
浙公网安备 33010602011771号