PostgreSQL性能分析工具-PoWA_postgresql workload analyzer

简介

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

PoWA组成

1. PoWA-archivist是PostgreSQL的插件，收集其他插件获取到的性能统计数据。
2. PoWA-collector是在专用存储库服务器上，用于收集远程PostgreSQL实例性能指标的守护进程。
3. PoWA-web是PoWA-collector收集到的性能指标的用户展示界面。
4. 安装于目标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语句监控。

存在风险

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






1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217