PostgreSQL性能分析工具-PoWA

duqk1982

已于 2023-04-04 13:32:08 修改

阅读量500

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 数据库

于 2023-04-03 13:57:19 首次发布

本文链接：https://blog.csdn.net/dqk1982/article/details/129928084

版权

PostgreSQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

简介

PoWA是PostgreSQL 9.4及以后更新版本的性能工具，允许从各种Stats Extensions收集、聚合和清除多个 PostgreSQL 实例的统计信息。全称为PostgreSQL工作负载分析器(PostgreSQL Workload Analyzer)

PoWA组成

PoWA-archivist是PostgreSQL的插件，收集其他插件获取到的性能统计数据。
PoWA-collector是在专用存储库服务器上，用于收集远程PostgreSQL实例性能指标的守护进程。
PoWA-web是PoWA-collector收集到的性能指标的用户展示界面。
安装于目标PostgreSQL数据库实例上的其他插件，其是性能指标数据的实际来源。
除了pg_stat_statements、btree_gist、powa为必须的插件，PoWA还支持以下几个插件作为新能指标采集的扩展：
1. pg_qualstats：用于保存在WHERE语句和JOIN子句中发现的谓词的统计信息，powa 利用它来提供索引建议。
2. pg_stat_kcache：收集有关文件系统层的实际读取和写入的统计信息，可以用于观察SQL花费多少cpu等。
3. pg_wait_sampling：用于收集等待事件的采样统计信息，启用后它会收集如下两种统计信息:–历史等待事件。它被实现为内存中的环形缓冲区，其中每个进程等待事件的样本都以给定的（可配置的）周期写入。因此，对于每个正在运行的进程，用户可以根据历史大小（可配置）查看一些最近的样本。假设有一个客户端定期读取此历史记录并将其转储到某个地方，则用户可以拥有连续的历史记录。–等待分析，以内存hash表形式存在，表中存放每个进程和每个等待事件累积的样本，该表可以根据用户请求进行reset。假设有一个客户端定期转储并reset，用户可以统计一段时间内等待事件的详细。
4. pg_track_settings：该插件提供了一个可定期调用的函数(pg_tracksettingssnapshot())。在每次调用时，它将存储自上次调用以来更改的设置。
5. Hypopg：类似MySQL虚拟索引。
6. pg_stat_statements：用于SQL语句监控。

存在风险

PoWA 会对PostgreSQL服务器性能产生很小的负面影响。且很难准确评估这种影响。
如果不使用远程模式，数据将定期存储在本地，所以必须考虑磁盘使用情况，避免影响备份。
在使用远程模式时，powa-repository中配置采集性能指标实例信息需要输入目标实例的IP、帐号及口令，并且可以通过powa_servers表查询到相关信息，其中连接口令以明文形式呈现，存在安全风险。
在PoWA-collector配置文件中，powa-repository的连接信息中无连接口令配置，表示powa-repository对于PoWA-collector的连接配置项必须为trust，存在安全风险。
在PoWA-web配置文件中，可选配置username、password对应powa-repository（远程模式）或者数据库实例（本地模式）的root用户及连接口令，且以明文形式存储，存在安全风险。

架构

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

在这里插入图片描述

安装过程：

监控主机：192.168.10.11        PG 14.7
被监控主机：192.168.10.14     PG 11.5

监控主机192.168.10.11安装要求：
• 需要 python 2.7 或 python 3
• psycopg2


1.监控主机和被监控主机安装PoWA-archivist
192.168.10.11和192.168.10.14都得安装
--root用户
source /home/postgres/.bash_profile
unzip powa-archivist-REL_4_1_2.zip
cd powa-archivist-REL_4_1_2
make && make install

[postgres@du101 ~]$ tail -2 /data/pgdata/postgresql.conf
track_io_timing = on
shared_preload_libraries = 'pg_stat_statements,powa'
[postgres@du101 ~]$ pg_ctl start

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# create database powa;
CREATE DATABASE
postgres=# \c powa
You are now connected to database "powa" as user "postgres".
powa=# create extension pg_stat_statements;
CREATE EXTENSION
powa=# create extension btree_gist;
CREATE EXTENSION
powa=# create extension powa;
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.2   | public     | PostgreSQL Workload Analyser-core
(4 rows)

powa=# \q
[postgres@du101 ~]$ ps -ef|grep powa
postgres  1360  1340  0 11:30 ?        00:00:00 postgres: powa idle
postgres  1362  1313  0 11:30 pts/0    00:00:00 grep --color=auto powa
[postgres@du101 ~]$


2.监控主机安装和启动PoWA-collector
192.168.10.11上操作
tar -zxvf powa-collector-1.2.0.tar.gz
mv powa-collector-1.2.0 /home/postgres/
chown postgres:postgres  /home/postgres/ -R
su - postgres
cd powa-collector-1.2.0
cp ./powa-collector.conf-dist ./powa-collector.conf

[postgres@du101 powa-collector-1.2.0]$ vi powa-collector.conf
[postgres@du101 powa-collector-1.2.0]$ cat !$
cat powa-collector.conf
{
    "repository": {
        "dsn": "postgresql://postgres@localhost:1921/powa"
    },
    "debug": false
}
[postgres@du101 powa-collector-1.2.0]$
[postgres@du101 powa-collector-1.2.0]$ ./powa-collector.py &
[1] 1352
[postgres@du101 powa-collector-1.2.0]$ 

3.监控主机配置监控示例信息
192.168.10.11上操作

select powa_register_server(
hostname => '192.168.10.14',
alias => 'mytest',
port => 1921,
username => 'postgres',
password => 'postgres',
frequency => 300);

powa=# select powa_register_server(
powa(# hostname => '192.168.10.14',
powa(# alias => 'mytest',
powa(# port => 1921,
powa(# username => 'postgres',
powa(# password => 'postgres',
powa(# frequency => 300);
 powa_register_server
----------------------
 t
(1 row)

powa=#

powa=# select * from powa_servers\gx
-[ RECORD 1 ]-------+--------------
id                  | 0
hostname            |
alias               | <local>
port                | 0
username            |
password            |
dbname              |
frequency           | -1
powa_coalesce       | 100
retention           | 00:00:00
allow_ui_connection | t
version             |
-[ RECORD 2 ]-------+--------------
id                  | 1
hostname            | 192.168.10.14
alias               | mytest
port                | 1921
username            | postgres
password            | postgres
dbname              | powa
frequency           | 300
powa_coalesce       | 100
retention           | 1 day
allow_ui_connection | t
version             |




4.监控主机PoWA-web配置及启动
192.168.10.11上操作

安装依赖包：
tar xvzf tornado-3.1.0.tar.gz
cd tornado-3.1.0
python setup.py build
python setup.py install

tar xvzf greenlet-1.0a1.tar.gz
cd greenlet-1.0a1
python setup.py build
python setup.py install

tar zxvf typing_extensions-3.6.5.tar.gz
cd typing_extensions-3.6.5
python setup.py build
python setup.py install

tar zxvf setuptools_scm-1.15.4.tar.gz
cd setuptools_scm-1.15.4
python setup.py build
python setup.py install


tar zxvf more-itertools-8.0.0.tar.gz
cd more-itertools-8.0.0
python setup.py build
python setup.py install

tar zxvf zipp-1.0.0.tar.gz
cd zipp-1.0.0
python setup.py build
python setup.py install


pip install importlib_metadata-4.11.0-py3-none-any.whl

tar xvzf SQLAlchemy-1.4.47.tar.gz
cd SQLAlchemy-1.4.47
python setup.py build
python setup.py install


[root@du101 ~]# mv powa-web-4.1.2.tar.gz /home/postgres/
[root@du101 ~]# chown postgres:postgres /home/postgres/ -R
[root@du101 ~]# su - postgres
Last login: Mon Apr  3 09:52:50 CST 2023 on pts/0
[postgres@du101 ~]$ tar -zxvf powa-web-4.1.2.tar.gz
[postgres@du101 ~]$ cd powa-web-4.1.2
[postgres@du101 powa-web-4.1.2]$ cp ./powa-web.conf-dist ./powa-web.conf
[postgres@du101 powa-web-4.1.2]$ cat powa-web.conf
servers={
  'main': {
    'host': 'localhost',
    'port': '1921',
    'database': 'powa',
    'username': 'postgres',
    'query': {'client_encoding': 'utf8'}
  }
}
cookie_secret="SUPERSECRET_THAT_YOU_SHOULD_CHANGE"
# Some extra options you can set
#
# port on which the UI should be available.
# port=8888
# Address on which the UI should be available on
# address=0.0.0.0
# Forbid UI to connect to databases globally (can be configured per server)
# allow_ui_connection=False
# Custom URL prefix the UI should be available on
# url_prefix="/"
[postgres@du101 powa-web-4.1.2]$

[postgres@du101 powa-web-4.1.2]$ ./powa-web &


5.登录监控页面
http://192.168.10.11:8888
数据库用户密码：postgres/postgres