PG性能采集分析工具之PoWA总结

-- Debian

cat > /etc/apt/sources.list <<"EOF"
deb http://mirrors.ustc.edu.cn/debian stable main contrib non-free
deb http://mirrors.ustc.edu.cn/debian stable-updates main contrib non-free
EOF

apt-get update

-- Debian 11还需要添加该源
deb https://apt-archive.postgresql.org/pub/repos/apt stretch-pgdg main


apt install -y curl ca-certificates gnupg sudo wget lsb-release apt-utils dialog
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


apt-get install -y 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



-- yum安装
yum install -y powa_14 powa_14-web pg_qualstats_14 pg_stat_kcache_14 hypopg_14


-- collector需要单独安装

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 'lhr' ;

show data_directory;

cat >> /var/lib/postgresql/data/postgresql.conf <<"EOF"
shared_preload_libraries = 'pg_stat_statements,powa,pg_qualstats,pg_stat_kcache,pg_wait_sampling'

# 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
EOF

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.7     | 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.10    | 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=# 

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;


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

apt-get install python-pip python-dev
apt-get -y install powa-web


pip3 install psycopg2
pip3 install powa-web


-- 编译安装
wget https://pypi.io/packages/source/p/powa-web/powa-web-4.1.3.tar.gz
tar -zxvf powa-web-4.1.3.tar.gz
cd powa-web-4.1.3
cp ./powa-web.conf-dist ./powa-web.conf
./powa-web

yum install python-pip python-devel
yum -y install powa_14-web


sudo pip install powa-web

cat > /etc/powa-web.conf <<"EOF"
servers={
  'main': {
    'host': '127.0.0.1',
    'port': '5432',
    'database': 'powa',
    'username': 'powa',
    'password': 'lhr',
    'query': {'client_encoding': 'utf8'}
  }
}
cookie_secret="SECRET_STRING"
port=8808
EOF

/etc/powa-web.conf
~/.config/powa-web.conf
~/.powa-web.conf
./powa-web.conf

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

-- Debian
apt-get -y install python3 python3-psycopg2
apt-get -y install powa-collector


-- yum安装的是1.1.0，建议使用pip3安装
yum install powa-collector



mkdir ~/.pip
cat  > ~/.pip/pip.conf  << EOF
[global]
timeout = 6000
index-url = http://mirrors.aliyun.com/pypi/simple/
index-index-url = http://pypi.douban.com/simple/
[install]
trusted-host = 
    mirrors.aliyun.com
    pypi.douban.com
EOF

pip3 install psycopg2
pip3 install powa-collector



-- 编译安装
wget https://pypi.io/packages/source/p/powa-collector/powa-collector-1.2.0.tar.gz
tar -zxvf powa-collector-1.2.0.tar.gz
cp ./powa-collector.conf-dist ./powa-collector.conf
./powa-collector


-- 配置
cat > /etc/powa-collector.conf <<"EOF"
{
    "repository": {
        "dsn": "postgresql://powa@127.0.0.1:5432/powa"
    },
    "debug": false
}
EOF


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

-- 或systemctl status powa-collector.service
/usr/bin/powa-collector

/etc/powa-collector.conf
~/.config/powa-collector.conf
~/.powa-collector.conf
./powa-collector.conf

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

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

-- 配置需要采集性能指标的实例信息
select powa_register_server(
hostname => '172.71.0.23',
alias => '172.71.0.23',
port => 5432,
username => 'postgres',
password => 'lhr',
frequency => 300,
retention => '30 day'::interval ,
extensions => '{pg_stat_statements,pg_qualstats,pg_stat_kcache,pg_wait_sampling,pg_track_settings}');


-- 查看当前采集指标信息实例的信息
select * from powa_servers;


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                   | 
  1 | 172.71.0.23 | 172.71.0.23 | 5432 | postgres | lhr      | powa   |       300 |           100 | 1 day     | t                   | 
(2 rows)

powa=# 

SELECT powa_activate_extension(2, 'pg_track_settings');

SELECT powa_delete_and_purge_server(1);

SELECT powa_configure_server(7, '{"frequency": "30"}');