pg_top 安装使用
1.pg_top 安装使用
wget -c https://gitlab.com/pg_top/pg_top/-/archive/master/pg_top-master.zip
#1.下载安装包：
https://github.com/markwkm/pg_top
#2.解压并安装
postgres@s2ahumysqlpg01-> unzip pg_top-master.zip
postgres@s2ahumysqlpg01-> cmake -DCMAKE_INSTALL_PREFIX=/usr/local
postgres@s2ahumysqlpg01-> make
Scanning dependencies of target pg_top
[ 7%] Building C object CMakeFiles/pg_top.dir/color.c.o
[ 15%] Building C object CMakeFiles/pg_top.dir/commands.c.o
[ 23%] Building C object CMakeFiles/pg_top.dir/display.c.o
[ 30%] Building C object CMakeFiles/pg_top.dir/getopt.c.o
[ 38%] Building C object CMakeFiles/pg_top.dir/screen.c.o
[ 46%] Building C object CMakeFiles/pg_top.dir/sprompt.c.o
[ 53%] Building C object CMakeFiles/pg_top.dir/pg.c.o
[ 61%] Building C object CMakeFiles/pg_top.dir/pg_top.c.o
[ 69%] Building C object CMakeFiles/pg_top.dir/utils.c.o
[ 76%] Building C object CMakeFiles/pg_top.dir/version.c.o
[ 84%] Building C object CMakeFiles/pg_top.dir/machine/m_remote.c.o
[ 92%] Building C object CMakeFiles/pg_top.dir/machine/m_common.c.o
[100%] Building C object CMakeFiles/pg_top.dir/machine/m_linux.c.o
Linking C executable pg_top
[100%] Built target pg_top
#3.PG_TOP使用
使用pg_top进行数据库监控。和登录本地数据库一样，设置-U指定登录用户，-d指定数据库，-h指定主机名，-p指定端口。
可以查看帮助文档：
postgres@s2ahumysqlpg01-> pg_top --help
pg_top monitors a PostgreSQL database cluster.
Usage:
pg_top [OPTION]... [COUNT]
General options:
-b, --batch use batch mode
-c, --show-command display command name of each process
-C, --color-mode turn off color mode
-i, --interactive use interactive mode
-I, --hide-idle hide idle processes
-n, --non-interactive use non-interactive mode
-o, --order-field=FIELD select sort order
-r, --remote-mode activate remote mode
-R display replication stats #查看复制信息
-s, --set-delay=SECOND set delay between screen updates
-T, --show-tags show color tags
-V, --version output version information, then exit
-x, --set-display=COUNT set maximum number of displays #结果输出
exit once this number is reached
-X display i/o stats #显示每个进程的I/O信息，能够监控到高耗I/O的进程
-z, --show-username=NAME display only processes owned by given
username
-?, --help show this help, then exit
Connection options:
-d, --dbname=DBNAME database to connect to
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-W, --password force password prompt, and persistent connection
#常规使用
#a.在连接的时候 例用 -R 查看复制信息
#b.在连接的时候 例用 -X参数，它可以显示每个进程的I/O信息。例如iops、Reads、Writes。能够监控到高耗I/O的进程。
#c. -x 后面指定次数，然后把该结果重定向到一个文件 。
如： pg_top -U postgres -d s2db -b -x 10 > pg_top.out
#d.监控remote主机的信息，这需要在remote主机上安装pg_proctab插件，只有安装插件才能查询remote主机的cpu、内存等信息，
git https://gitlab.com/pg_proctab/pg_proctab.git
cd pg_proctab
make && make install
[postgres@centos8 ~]$ psql
psql (12.6)
Type "help" for help.
postgres=# create extension pg_proctab;
CREATE EXTENSION
#4.pg_top 示例
postgres@s2ahumysqlpg01-> pg_top -U postgres -d s2db -h localhost -p 5432
last pid: 4894; load avg: 0.29, 0.32, 0.28; up 77+05:48:35 17:21:12
10 processes: 5 other background task(s), 4 idle, 1 active
CPU states: 5.5% user, 0.0% nice, 6.0% system, 88.6% idle, 0.0% iowait
Memory: 3485M used, 194M free, 0K shared, 6736K buffers, 881M cached
Swap: 35M used, 3779M free, 8892K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
4895 postgres 333M 16M active 0:00 0:00 0.0 8 postgres: postgres s2db ::1(40272) idle
17171 zcloud_m 335M 18M idle 0:00 0:00 0.0 0 postgres: zcloud_monitor postgres 192.168.1.58(42034) idle
13568 332M 4692K 0:00 0:00 0.0 0 postgres: background writer
14742 zcloud_m 333M 12M idle 0:00 0:00 0.0 0 postgres: zcloud_monitor postgres 192.168.1.58(41936) idle
13573 postgres 335M 18M idle 0:00 0:00 0.0 0 postgres: pgsentinel
1877 zcloud_m 334M 16M idle 0:00 0:00 0.0 0 postgres: zcloud_monitor postgres 192.168.1.58(42648) idle
13569 331M 6792K 0:00 0:00 0.0 0 postgres: walwriter
13574 postgres 332M 5812K 0:00 0:00 0.0 0 postgres: logical replication launcher
13567 332M 16M 0:00 0:00 0.0 0 postgres: checkpointer
13570 332M 7716K 0:00 0:00 0.0 0 postgres: autovacuum launcher
#在使用过程中帮可按h 进入帮助界面
L - redraw screen
<sp> - update screen
A - EXPLAIN ANALYZE (UPDATE/DELETE safe)
a - show PostgreSQL activity #手动刷新
C - toggle the use of color
E - show execution plan (UPDATE/DELETE safe) #查看执行计划
I - show I/O statistics per process (Linux only) #查看IO
L - show locks held by a process #查看锁
Q - show current query of a process #显示进程的当前查询
c - toggle the display of process commands #显示详细 SQL
d - change number of displays to show
h or ? - help; show this text
i - toggle the displaying of idle processes #idle 进程 和 非 idle 进程切换
n or # - change number of processes to display #更改要显示的进程数
o - specify sort order (cpu, size, res, xtime, qtime, iops, iorps, iowps, reads, writes, locks, command, flag, rlag, slag, wlag) # 指定排序顺序
q - quit
s - change number of seconds to delay between updates #更改刷新时间
u - display processes for only one user (+ selects all users) # 显示某个用户的连接信息
Not all commands are available on all systems.
a.按一下c，就会显示这些进程执行的详细SQL语句。
b.按Q输入进程号可以查看单个进程执行SQL语句。
c.按E可以查看该进程运行SQL的执行计划。
c.按L输入进程号可以查看锁信息。
这里最上面会进程数量。后台进程有多少个，活动的进程有多少个，idle的进程有多少个。还可以看到主机的cpu和内存使用情况。下面是当前PostgreSQL进程的pid，用户名，状态，XTIME代表事务时间，QTIME代表query执行时间，还有cpu百分比和锁的数量。后面的COMMAND代表进程和它的操作类型。
你可以按一下c，就会显示这些进程执行的详细SQL语句。
2.pg_top 依赖包 libbsd-devel 示例
# 安装的时候报错，在执行make的过程中，我遇到了一个报错。我估计大多数人的centos/redhat系统都会报这个错误。 我的是centos 。
该错误提示找不到bsd/stdlib.h。从这个名字中我们就可以大概猜出它缺少bsd的lib包。
所以我们需要安装libbsd-devel ，下面是centos安装libbsd-devel 示例，安装完后再次执行make就能顺利编译(如果报其它错误，可以把先删除 pg_top-master 目录，重新解压执行安装)。
# CentOS 安装 libbsd-devel 示例
https://centos.pkgs.org/7/epel-aarch64/libbsd-devel-0.8.3-1.el7.aarch64.rpm.html
1.从下载最新的 epel-release rpm
http://download-ib01.fedoraproject.org/pub/epel/7/aarch64/
2.安装 epel-release rpm：
# rpm -Uvh epel-release*rpm
3.安装 libbsd-devel rpm 包：
# yum install libbsd-devel#安装步骤如下
# 1.下载 epel-release-7-12.noarch.rpm
http://download-ib01.fedoraproject.org/pub/epel/7/aarch64/Packages/e/ 
#2.安装 epel-release-7-12.noarch.rpm 包
[root@s2ahumysqlpg01 postgres]# rpm -Uvh epel-release*rpm
warning: epel-release-7-12.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:epel-release-7-12 ################################# [100%]
#3.安装libbsd-devel
[root@s2ahumysqlpg01 postgres]# yum install libbsd-devel
Loaded plugins: ulninfo
epel/x86_64/metalink | 4.4 kB 00:00:00
epel | 4.7 kB 00:00:00
(1/3): epel/x86_64/updateinfo | 1.0 MB 00:00:02
(2/3): epel/x86_64/group_gz | 96 kB 00:00:06
(3/3): epel/x86_64/primary_db | 7.0 MB 00:00:06
Resolving Dependencies
--> Running transaction check
---> Package libbsd-devel.x86_64 0:0.8.3-1.el7 will be installed
--> Processing Dependency: libbsd = 0.8.3-1.el7 for package: libbsd-devel-0.8.3-1.el7.x86_64
--> Processing Dependency: libbsd.so.0()(64bit) for package: libbsd-devel-0.8.3-1.el7.x86_64
--> Running transaction check
---> Package libbsd.x86_64 0:0.8.3-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================
Installing:
libbsd-devel x86_64 0.8.3-1.el7 epel 106 k
Installing for dependencies:
libbsd x86_64 0.8.3-1.el7 epel 85 k
Transaction Summary
=============================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 191 k
Installed size: 432 k
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/epel/packages/libbsd-0.8.3-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for libbsd-0.8.3-1.el7.x86_64.rpm is not installed
(1/2): libbsd-0.8.3-1.el7.x86_64.rpm | 85 kB 00:00:01
(2/2): libbsd-devel-0.8.3-1.el7.x86_64.rpm | 106 kB 00:00:00
-----------------------------------------------------------------------------------------------------------------------------
Total 97 kB/s | 191 kB 00:00:01
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid : "Fedora EPEL (7) <epel@fedoraproject.org>"
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package : epel-release-7-12.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:
2:postfix-2.10.1-7.el7.x86_64 has missing requires of libmysqlclient.so.18()(64bit)
2:postfix-2.10.1-7.el7.x86_64 has missing requires of libmysqlclient.so.18(libmysqlclient_18)(64bit)
Installing : libbsd-0.8.3-1.el7.x86_64 1/2
Installing : libbsd-devel-0.8.3-1.el7.x86_64 2/2
Verifying : libbsd-0.8.3-1.el7.x86_64 1/2
Verifying : libbsd-devel-0.8.3-1.el7.x86_64 2/2
Installed:
libbsd-devel.x86_64 0:0.8.3-1.el7
Dependency Installed:
libbsd.x86_64 0:0.8.3-1.el7
Complete!参考连接:
https://pg_top.gitlab.io/
https://github.com/markwkm/pg_proctab
https://cdn.modb.pro/db/48198