以前有整理过一些关于pg监控的工具，其中有介绍到pg_top,以下是一个简单的学习使用
环境准备
基于虚拟机部署的，centos8
- 安装pg
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
- 修改配置（注意需要重启服务）
为了测试方便，去掉安全文件 /var/lib/pgsql/13/data/pg_hba.conf
修改为:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
- 安装pg_top
直接使用dnf安装就可以了
dnf install -y pg_top
- 压测工具安装
压测基于sysbench
dnf install -y sysbench
试用
- 运行pg_top
pg_top -U postgres -h 127.0.0.1
- 压测
sysbench
--db-driver=pgsql
--pgsql-host=127.0.0.1
--pgsql-port=5432
--pgsql-user=postgres
--pgsql-db=postgres
--oltp-table-size=10000
--rand-init=on
--threads=10
--time=120
--events=0
--report-interval=10
--percentile=99
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
prepare
sysbench
--db-driver=pgsql
--pgsql-host=127.0.0.1
--pgsql-port=5432
--pgsql-user=postgres
--pgsql-db=postgres
--oltp-table-size=10000
--threads=10
--time=120
--events=0
--report-interval=10
--percentile=99
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
run
sysbench
--db-driver=pgsql
--pgsql-host=127.0.0.1
--pgsql-port=5432
--pgsql-user=postgres
--pgsql-db=postgres
--oltp-table-size=10000
--threads=10
--time=120
--events=0
--report-interval=10
--percentile=99
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
cleanup
- 效果
