重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛

标题: PG 的 AWR pg_profile [打印本页]

作者: jiawang 时间: 7 天前
标题: PG 的 AWR pg_profile
本帖最后由 jiawang 于 2025-4-9 09:22 编辑

一、什么是PG_PROFILE

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

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

二、配置postgresql.conf

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

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

---------+---------+------------+------------------------------

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedurallanguage

(1 row)

postgres=# CREATEEXTENSION dblink;

CREATE EXTENSION

postgres=# CREATEEXTENSION pg_stat_statements;

CREATE EXTENSION

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+------------------------------------------------------------------------

dblink | 1.2 | public | connect to other PostgreSQL databasesfrom within a database

pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedurallanguage

(3 rows)

创建扩展profile

postgres=# CREATE SCHEMA profile;

CREATE SCHEMA

postgres=# CREATE EXTENSION pg_profileSCHEMA profile;

CREATE EXTENSION

postgres=# \dn

List of schemas

Name | Owner

---------+-------------------

profile | postgres

public | pg_database_owner

(2 rows)

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+------------------------------------------------------------------------

dblink | 1.2 | public | connect to other PostgreSQL databasesfrom within a database

pg_profile | 4.8 | profile | PostgreSQL load profile repository andreport builder

pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedurallanguage

(4rows)

[postgres@sztech data]$ psql

psql (16.3)

Type "help" for help.

postgres=# SELECT profile.snapshot() ;

snapshot

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

(local,OK,00:00:00.36)

(1 row)

postgres=# select profile.show_samples();

show_samples

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

(1,"2025-04-08 17:06:25+08",t,,,)

(1 row)

postgres=#

postgres=# select profile.show_samples();

show_samples

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

(1,"2025-04-08 17:06:25+08",t,,,)

(1 row)

postgres=# SELECT profile.snapshot() ;

snapshot

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

(local,OK,00:00:00.38)

(1 row)

postgres=# SELECT profile.snapshot() ;

snapshot

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

(local,OK,00:00:00.31)

(1 row)

postgres=# select profile.show_samples();

show_samples

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

(1,"2025-04-08 17:06:25+08",t,,,)

(2,"2025-04-08 17:06:58+08",t,,,)

(3,"2025-04-08 17:13:45+08",t,,,)

(4,"2025-04-08 17:13:54+08",t,,,)

(4 rows)

添加测试数据

postgres=# create table t1(id int);

CREATE TABLE

postgres =# insert into t1 values(generate_series(1,1000));

INSERT 0 1000

查询快照详情

postgres=# select * fromprofile.show_samples();

--------+------------------------+-----------------+---------------+----------------+-----------------

1 | 2025-04-08 17:06:25+08 | t | | |

2 | 2025-04-08 17:06:58+08 | t | | |

3 | 2025-04-08 17:13:45+08 | t | | |

4 | 2025-04-08 17:13:54+08 | t | | |

5 | 2025-04-08 17:41:24+08 | t | | |

6 | 2025-04-08 17:42:35+08 | t | | |

(6 rows)

基于两个快照点的间隔生成

[postgres@sztech ~]$ psql -dpostgres-Upostgres -Aqtc "SELECT profile.get_report(3,6)" -oawr_report_postgres_3_6.html

[postgres@sztech ~]$ ll

总用量 518952

-rw-r--r-- 1 postgres postgres531027917 4月 8 17:38 awr_report_postgres_1_2.html

-rw-r--r-- 1 postgres postgres 375773 4月 817:45 awr_report_postgres_3_6.html

导出效果图如下：