概述

本文主要介绍 PG 的一个性能采集和分析工具 PoWA 的部署方法和使用特点。欢迎留言交流。

该工具利用 PG 的扩展插件实现，其性能诊断分析能力比较接近 ORACLE AWR 报表功能。

PG 软件源仓库简介

PG 和相关插件的安装都可以从 PG 专用软件源仓库下载安装。根据操作系统版本的不同，软件源仓库的配置方法有所不同。

CentOS/Redhat

PG 在 CentOS、Redhat 上的软件源仓库描述地址：https://yum.postgresql.org/

软件可以使用 yum 或 dnf 命令安装软件包。

# Install the repository RPM:sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm# Disable the built-in PostgreSQL module:sudo dnf -qy module disable postgresql# Install PostgreSQL:sudo dnf install -y postgresql14-server# Optionally initialize the database and enable automatic start:sudo /usr/pgsql-14/bin/postgresql-14-setup initdbsudo systemctl enable postgresql-14sudo systemctl start postgresql-14

Ubuntu/Debian

PG 在 Debian 上的软件源仓库描述地址：https://wiki.postgresql.org/wiki/Apt

软件可以使用 apt 命令安装软件包。

sudo apt install curl ca-certificates gnupgcurl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/nullsudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'sudo apt updatesudo apt install postgresql-14 postgresql-client-14 postgresql-contrib-14

apt update 可能会很慢，就把 debian 源地址替换为国内镜像。如下面这个。

root@292f2c855627:/# cat /etc/apt/sources.list# deb http://snapshot.debian.org/archive/debian/20201012T070000Z buster maindeb http://mirrors.ustc.edu.cn/debian buster main# deb http://snapshot.debian.org/archive/debian-security/20201012T070000Z buster/updates maindeb http://mirrors.ustc.edu.cn/debian-security buster/updates main# deb http://snapshot.debian.org/archive/debian/20201012T070000Z buster-updates maindeb http://mirrors.ustc.edu.cn/debian buster-updates main

PoWA 部署介绍

部署架构简介

PoWA 4.0 之后的部署架构支持本地部署和远程部署。推荐用远程部署方式，部署架构图如下。

在每个 PG 实例里启用插件，在独立的服务器上部署采集程序 PoWA collector 和主程序 PoWA web。

上面架构图用到的插件简介如下：

pg_stat_statements ：记录指定 PG 实例上所有 SQL 的统计信息，具体数据在视图 pg_stat_statements 上。
pg_qualstats ：采集 SQL 语句的 where 和 join 语句中的条件的统计信息。
pg_stat_kcache ：采集主机系统指标的统计信息。
pg_wait_sampling ：采集 SQL 语句的等待事件，并提供等待事件的汇总统计视图。
pg_track_settings ：主要是跟踪实例的参数配置变化。

PoWA archivlist 部署介绍

简介

PoWA-archivlist 是 PoWA 项目的重要组件之一，它包含 2 个元素：

扩展 powa ，包含一些管理用的函数。
模块 powa ，可选，在后台运行，采集本地实例的性能。

安装方法

建议使用 PGDG 软件源里的安装包安装。

apt-get -y install postgresql-14-powa

在目标 PG 实例里创建数据库 powa 和相应的扩展。

postgres=# create database powa;CREATE DATABASEpostgres=# \c powaYou are now connected to database "powa" as user "postgres".powa=# create extension powa;CREATE EXTENSIONpowa=# create extension btree_gist ;CREATE EXTENSIONpowa=# create extension powa;CREATE EXTENSIONpowa=#

也可以用下面方式自动创建依赖的扩展。

postgres=# drop database powa;DROP DATABASEpostgres=# create database powa;CREATE DATABASEpostgres=# \c powa;You are now connected to database "powa" as user "postgres".powa=# create extension powa cascade ;NOTICE:  installing required extension "pg_stat_statements"NOTICE:  installing required extension "btree_gist"CREATE EXTENSIONpowa=# \dx                                            List of installed extensions        Name        | Version |   Schema   |                              Description--------------------+---------+------------+------------------------------------------------------------------------ btree_gist         | 1.6     | public     | support for indexing common datatypes in GiST pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language powa               | 4.1.4   | public     | PostgreSQL Workload Analyser-core(4 rows)

修改 PG 配置文件。

安装完成后修改 postgresql.conf 里预加载的模块参数，需要重启 PG 实例才可以加载模块。

shared_preload_libraries = 'pg_stat_statements' # (change requires restart)

并且增加相应的参数控制记录不同 SQL 查询的个数（默认是1000，太少了）。

pg_stat_statements.max = 10000

重启 PostgreSQL 实例，让配置生效。

查看使用示例

powa=# \x autoExpanded display is used automatically.powa=# select query, calls, total_exec_time, rows from pg_stat_statements order by calls desc limit 2;-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------query           | select spcname as  TABLESPACENAME,pg_tablespace_size(spcname)/$1/$2 as  total_mb,$3 as free, (select count(*) from pg_catalog.pg_database where dattablespace=a.OID) as datcount, (select rolname from pg_catalog.pg_authid where oid=b.oid) as ownuser from pg_catalog.pg_tablespace a left join pg_catalog.pg_roles b on a.spcowner=b.oid                                                                                                                                                                                                                                                                                                               +calls           | 254total_exec_time | 9499.660649999998rows            | 508-[ RECORD 2 ]---+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------query           | select a.*, b.*                                                                                                                                                                                                                                                                                                +                | from                                                                                                                                                                                                                                                                                                                                       +                | (select sum(numbackends) numbackends, sum(xact_commit) xact_commit, sum(xact_rollback) xact_rollback, sum(blks_read) blks_read, sum(blks_hit) blks_hit, sum(tup_returned) tup_returned, sum(tup_fetched) tup_fetched, sum(tup_inserted) tup_inserted, sum(tup_updated) tup_updated, sum(tup_deleted) tup_deleted,                          +                | sum(conflicts) conflicts, sum(temp_files) temp_files, sum(temp_bytes) temp_bytes, sum(deadlocks) deadlocks                                                                                                                                                                                                                                 +                | from pg_stat_database) a, (select * from pg_stat_bgwriter) bcalls           | 252total_exec_time | 3321.225349999999rows            | 252

PoWA 插件部署介绍

pg_qualstats 部署介绍

简介

pg_qualstats 主要是采集 SQL 语句的 where 和 join 语句中的条件的统计信息，帮助 DBA 分析下面问题：

使用这个列查询的语句集合是哪些？
where 语句里常用的查询条件的值是哪些？
where 语句里常用的查询条件是哪些？

安装方式采取安装软件包。

apt -y install postgresql-14-pg-qualstats

在目标 PG 实例的数据库 powa 创建相应的扩展。

CREATE EXTENSION pg_qualstats;

修改 PG 配置文件。

安装完成后修改 postgresql.conf 里预加载的模块参数，需要重启 PG 实例才可以加载模块。

shared_preload_libraries = 'pg_stat_statements,pg_qualstats'    # (change requires restart)

下面是插件相关的参数，也在 postgresql.conf 里添加。

pg_qualstats.enabled = truepg_qualstats.max = 10000pg_qualstats.track_pg_catalog = falsepg_qualstats.resolve_oids = falsepg_qualstats.track_constants = truepg_qualstats.sample_rate = 0.1

pg_stat_kcache 部署介绍

简介

pg_stat_kcache ：采集主机系统指标的统计信息。这个扩展可以帮助 DBA 查看每个查询、用户或数据库消耗的主机资源。主机资源包括：

CPU （user time 和 system time）
物理磁盘访问读写次数

安装方式采取安装软件包。

apt -y install postgresql-14-pg-stat-kcache

在目标 PG 实例的数据库 powa 创建相应的扩展。

CREATE EXTENSION pg_stat_kcache;

修改 PG 配置文件。

安装完成后修改 postgresql.conf 里预加载的模块参数，需要重启 PG 实例才可以加载模块。

shared_preload_libraries = 'pg_stat_statements,pg_qualstats,pg_stat_kcache'    # (change requires restart)

pg_stat_kcache 采集的记录数跟插件 pg_stat_statements 的参数保持一致。

重启实例后在 powa 库中执行下面语句：

SELECT powa_kcache_register();

pg_wait_sampling 部署介绍

简介

pg_wait_sampling ：采集 SQL 语句的等待事件，并提供等待事件的汇总统计视图。

安装方式采取安装软件包。

apt -y install postgresql-14-pg-wait-sampling

在目标 PG 实例的数据库 powa 创建相应的扩展。

CREATE EXTENSION pg_wait_sampling;

修改 PG 配置文件。

安装完成后修改 postgresql.conf 里预加载的模块参数，需要重启 PG 实例才可以加载模块。

shared_preload_libraries = 'pg_stat_statements,pg_qualstats,pg_stat_kcache,pg_wait_sampling'    # (change requires restart)

pg_stat_kcache 提供参数配置插件行为。

pg_wait_sampling.profile_period = 50pg_wait_sampling.profile_pid = truepg_wait_sampling.profile_queries = true

重启实例后在 powa 库中执行下面语句：

SELECT powa_wait_sampling_register();

查看使用示例

视图 pg_wait_sampling_profile 统计了每个 pid 的等待事件类型、语句id 和数量。

powa=# select * from pg_wait_sampling_profile ;pid | event_type |        event        |       queryid        | count-----+------------+---------------------+----------------------+-------  27 | Activity   | CheckpointerMain    |                    0 |  8164  28 | Activity   | BgWriterMain        |                    0 |  8164  35 | Client     | ClientRead          |                    0 |  8105  33 | Activity   | LogicalLauncherMain |                    0 |  8164  30 | Activity   | AutoVacuumMain      |                    0 |  8158  49 | Client     | ClientRead          |                    0 |  5143  34 | Client     | ClientRead          |                    0 |  8100  35 | IO         | DataFileRead        | -8101737766526846102 |     1  29 | Activity   | WalWriterMain       |                    0 |  8163  48 | Client     | ClientRead          |                    0 |  5247  51 | Client     | ClientRead          |                    0 |  5108(11 rows)

有关 PG 等待事件的详细介绍可以参考 https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE 。

pg_track_settings 部署介绍

介绍

pg_track_settings ：主要是跟踪实例的参数配置变化。包括下面：

参数文件 postgresql.conf 或命令 alter system 修改后在视图 pg_settings 里的变化。
用户 role 或数据库级别的变化，主要是命令 alter role 或 alter database 修改后在视图 pg_db_role_setting 里的变化。

在 PoWA 的远程部署架构里，这个插件必须在 powa 元数据库以及远程 PG 实例的 powa 里都要安装。

安装方式采取安装软件包。

apt -y install postgresql-14-pg-track-settings

在目标 PG 实例的数据库 powa 创建相应的扩展。

CREATE EXTENSION pg_track_settings;

还要执行下面语句注册这个实例。

SELECT powa_track_settings_register();

HypoPG 部署介绍

HypoPG 插件可以充分利用前面各个插件的特性，主要是用来创建虚拟索引，然后用 explain 命令来检验虚拟索引的执行计划是否符合预期。虚拟索引不会产生磁盘 IO ，在磁盘上并不存在。

安装方式采取安装软件包。

apt -y install postgresql-14-hypopg

在目标 PG 实例的数据库 powa 创建相应的扩展。

CREATE EXTENSION hypopg ;

汇总所有插件部署方法

如果要安装所有插件，命令汇总如下。其中 14 是 PG 版本号，需要根据实际情况修改。

apt-get install postgresql-14-powa postgresql-14-pg-track-settings postgresql-14-pg-qualstats postgresql-14-pg-stat-kcache postgresql-14-hypopg postgresql-14-pg-wait-sampling

然后创建数据库 powa 并在下面创建相应扩展。

create database powa;\c powaCREATE EXTENSION pg_stat_statements;CREATE EXTENSION btree_gist;CREATE EXTENSION powa;CREATE EXTENSION pg_qualstats;CREATE EXTENSION pg_stat_kcache;CREATE EXTENSION pg_wait_sampling;CREATE EXTENSION pg_track_settings;CREATE EXTENSION hypopg;CREATE ROLE powa SUPERUSER LOGIN PASSWORD '********' ;

修改配置文件 postgresql.conf 并重启 PG 实例。

shared_preload_libraries = 'pg_stat_statements,pg_qualstats,pg_stat_kcache,pg_wait_sampling'    # (change requires restart)

# Add settings for extensions herepg_stat_statements.max = 10000track_io_timing = onpg_qualstats.enabled = truepg_qualstats.max = 10000pg_qualstats.track_pg_catalog = falsepg_qualstats.resolve_oids = falsepg_qualstats.track_constants = truepg_qualstats.sample_rate = 0.1pg_wait_sampling.profile_period = 50pg_wait_sampling.profile_pid = truepg_wait_sampling.profile_queries = true

重启实例后，在数据库 powa 里再运行下面语句。

SELECT powa_kcache_register();SELECT powa_wait_sampling_register();SELECT powa_track_settings_register();

查看所有插件情况

powa=# \dx                                            List of installed extensions        Name        | Version |   Schema   |                              Description--------------------+---------+------------+------------------------------------------------------------------------ btree_gist         | 1.6     | public     | support for indexing common datatypes in GiST hypopg             | 1.3.1   | public     | Hypothetical indexes for PostgreSQL pg_qualstats       | 2.0.4   | public     | An extension collecting statistics about quals pg_stat_kcache     | 2.2.1   | public     | Kernel statistics gathering pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed pg_track_settings  | 2.1.0   | public     | Track settings changes pg_wait_sampling   | 1.1     | public     | sampling based statistics of wait events plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language powa               | 4.1.4   | public     | PostgreSQL Workload Analyser-core(9 rows)

配置远程实例

在 PoWA 的元数据库里注册远程PG 实例。

SELECT powa_register_server(hostname => '10.0.0.109',    port => '15432',    alias => 'monitor-beta',    username => 'powa',    password => '********',    extensions => '{pg_stat_statements,pg_qualstats,pg_stat_kcache,pg_wait_sampling,pg_track_settings}');

查看注册的实例。

powa=# select * from powa_servers; id |  hostname  |    alias     | port  | username | password  | dbname | frequency | powa_coalesce | retention | allow_ui_connection | version----+------------+--------------+-------+----------+-----------+--------+-----------+---------------+-----------+---------------------+---------  0 |            |       |     0 |          |           |        |        -1 |           100 | 00:00:00  | t                   |  2 | 10.0.0.109 | monitor-beta | 15432 | powa     | admIN@123 | powa   |       300 |           100 | 1 day     | t                   |(2 rows)

如果要增加扩展，方法如下：

SELECT powa_activate_extension(2, 'pg_track_settings');

更多操作方法请参考文档：background worker configuration — PoWA 4.1.4 documentation 。

PoWA collector 部署介绍

安装方法

apt-get -y install python3 python3-psycopg2apt-get -y install powa-collector

配置方法

vim /etc/powa-collector.conf{    "repository": {        "dsn": "postgresql://powa@127.0.0.1:5432/powa"    },    "debug": false}

运行 PoWA collector

nohup powa-collector.py 2>&1 1>/tmp/powa-collector.log &

PoWA web 部署

安装方法

在 debian 系统上，powa-web 包跟 PG 版本无关。

apt-get -y install powa-web

在 CentOS/Linux 系统上，powa-web 包跟 PG 版本有关。

yum -y install powa_14-web

配置 PoWA web

[root@monitor-test1 ~]# vim /etc/powa-web.confservers={  'main': {    'host': '127.0.0.1',    'port': '5432',    'database': 'powa',    'username': 'powa',    'password': '*********',  }}cookie_secret="SECRET_STRING"port=8808

注意，监听端口是 8808 。

启动 PoWA web

nohup powa-web 2>&1 1>/tmp/powa-web.log &

登录

网址：http://10.0.0.107:8808/login

用户名和密码就是元数据库 powa 的访问账号。

PoWA 使用简介

功能概览

PoWA 能够采集和展示本地 PG 实例和多个远程 PG实例的性能指标，并能够从实例到数据库到SQL语句级别进行下钻分析。

使用示例

官网提供了一个公网的例子，可以在线查看。

地址：https://demo-powa.anayrat.info/
用户名：postgres
密码：postgres

下面是几个功能截图。

首先是主页，展示多个PG实例信息。

每个实例的配置中有扩展启用信息。

这个是具体的一个 PG 实例的主页，展示多个角度的指标图。

这个是具体的一个PG 实例下的多个数据库性能汇总信息。

下面两个是 SQL 的 IO 信息。包括对 PG 缓存和 OS 缓存的利用情况。

最厉害的还是这个功能，索引建议功能。

其他问题

PoWA的不足之处就是采集账户权限比较大，且在配置的时候还是明文密码保存。所以需要控制好 PoWA web 和 PG 的访问安全。

更多阅读

PG 的性能采集分析工具 PoWA 部署实践

pg_qualstats 部署介绍

PoWA 使用简介

其他问题