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

数据库技术闲谈 2023-04-03

821

概述

本文主要介绍 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 initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

Ubuntu/Debian

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

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

sudo apt install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sudo 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 update
sudo 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 main
deb http://mirrors.ustc.edu.cn/debian buster main
# deb http://snapshot.debian.org/archive/debian-security/20201012T070000Z buster/updates main
deb http://mirrors.ustc.edu.cn/debian-security buster/updates main
# deb http://snapshot.debian.org/archive/debian/20201012T070000Z buster-updates main
deb 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 DATABASE
postgres=# \c powa
You are now connected to database "powa" as user "postgres".
powa=# create extension powa;
CREATE EXTENSION
powa=# create extension btree_gist ;
CREATE EXTENSION
powa=# create extension powa;
CREATE EXTENSION
powa=#

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

postgres=# drop database powa;
DROP DATABASE
postgres=# create database powa;
CREATE DATABASE
postgres=# \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 EXTENSION
powa=# \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 auto
Expanded 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           | 254
total_exec_time | 9499.660649999998
rows            | 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) b
calls           | 252
total_exec_time | 3321.225349999999
rows            | 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 = true
pg_qualstats.max = 10000
pg_qualstats.track_pg_catalog = false
pg_qualstats.resolve_oids = false
pg_qualstats.track_constants = true
pg_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 = 50
pg_wait_sampling.profile_pid = true
pg_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 powa
CREATE 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 here
pg_stat_statements.max = 10000
track_io_timing = on
pg_qualstats.enabled = true
pg_qualstats.max = 10000
pg_qualstats.track_pg_catalog = false
pg_qualstats.resolve_oids = false
pg_qualstats.track_constants = true
pg_qualstats.sample_rate = 0.1
pg_wait_sampling.profile_period = 50
pg_wait_sampling.profile_pid = true
pg_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 |            | <local>      |     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-psycopg2
apt-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.conf
servers={
  '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 的访问安全。

更多阅读

PostgreSQL 会话事务中断报错问题分析
Linux 内存 PageCache 和数据库共享内存分析（上）

select postgresql

文章转载自数据库技术闲谈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

概述

PG 软件源仓库简介

CentOS/Redhat

Ubuntu/Debian

PoWA 部署介绍

部署架构简介

PoWA archivlist 部署介绍

PoWA 插件部署介绍

pg_qualstats 部署介绍

pg_stat_kcache 部署介绍

pg_wait_sampling 部署介绍

pg_track_settings 部署介绍

HypoPG 部署介绍

汇总所有插件部署方法

配置远程实例

PoWA collector 部署介绍

PoWA web 部署

PoWA 使用简介

功能概览

使用示例

其他问题

评论