我们在操作系统上经常喜欢用top命令，使用top命令查看操作系统层面的资源使用情况。我们使用top命令查看信息的时候，并没有数据库中的一些进程信息，与数据库进程信息无法紧密结合。
所以我们可以使用pg_top工具来查看，比如top也有一些衍生的工具（单独监控IO、CPU等）。在数据库监控层，我们可以组合的使用pg_top命令，可以将io、iops，内存，CPU 等信息结合起来查看。
今天我们来学习一下pg_top工具的安装及使用。
pg_top 说明
pg_top 是 PostgreSQL 的’top’。它源自 Unix Top。与 top 类似，pg_top 允许您监控 PostgreSQL 进程。它还允许您：
- 查看进程当前正在运行的 SQL 语句。
- 查看当前正在运行的 SELECT 语句的查询计划。
- 查看进程持有的锁。
- 查看每个进程的 I/O 统计信息。
- 查看下游节点的复制统计信息。
pg_top 安装
软件下载
安装包下载地址： 墨天轮地址：https://cdn.modb.pro/download/357651 gitlb地址：https://gitlab.com/pg_top/pg_top gihub地址：https://github.com/markwkm/pg_top
安装准备
解压压缩包
[postgres@lyp ~]$ ls -rlt pg_top-master.zip
-rw-r--r--. 1 postgres postgres 189401 Feb 10 14:30 pg_top-master.zip
[postgres@lyp ~]$ unzip pg_top-master.zip
Archive: pg_top-master.zip
fb32e974ee21f2f9af58f08bbdfe53b38a57207f
creating: pg_top-master/
inflating: pg_top-master/.gitignore
..........
inflating: pg_top-master/version.c
inflating: pg_top-master/version.h
[postgres@lyp ~]$
要编译并安装“pg_top”，请阅读文件“INSTALL.rst”，然后按照其中包含的指示和建议进行编译安装pg_top。
查看安装文件
[postgres@lyp pg_top-master]$ more INSTALL.rst
pg_top
======
Installation
------------
Configuring
~~~~~~~~~~~
::
cmake [options] CMakeLists.txt
options:
-DCMAKE_INSTALL_PREFIX=PREFIX Install files in PREFIX. Default is
'/usr/local'.
-DENABLE_COLOR=0 Default on. Include code that allows for the
use of color in the output display. Use
-DENABLE_COLOR=0 if you do not want this
feature compiled in to the code. The configure
script also recognizes the spelling "colour".
Installing
~~~~~~~~~~
::
make install
Uninstalling
~~~~~~~~~~~~
::
xargs rm < install_manifest.txt
[postgres@lyp pg_top-master]$
配置安装路径并检查
安装路径选择postgresql软件安装路径：/opt/pgsql14.1/
[postgres@lyp pg_top-master]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pgsql14.1/ CMakeLists.txt bash: cmake: command not found... Similar command is: 'make' [postgres@lyp pg_top-master]$
缺少cmake工具，安装cmake工具，后重新安装
[root@lyp ~]# yum -y install cmake
[root@lyp ~]# su - postgres
Last login: Thu Feb 10 19:53:18 CST 2022 on pts/5
[postgres@lyp ~]$ cd pg_top-master/
[postgres@lyp pg_top-master]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pgsql14.1/ CMakeLists.txt
-- The C compiler identification is GNU 4.8.5
-- Check for working C compiler: /bin/cc
-- Check for working C compiler: /bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- machine - linux
-- arch - x86_64
-- pg_config --includedir - /opt/pgsql14.1/include
-- pg_config --libdir - /opt/pgsql14.1/lib
-- Looking for 4 include files stdlib.h, ..., float.h
-- Looking for 4 include files stdlib.h, ..., float.h - found
-- Looking for include file string.h
-- Looking for include file string.h - found
-- Looking for include file strings.h
-- Looking for include file strings.h - found
-- Looking for include files sys/time.h, time.h
-- Looking for include files sys/time.h, time.h - found
-- Looking for include file sys/time.h
-- Looking for include file sys/time.h - found
-- Looking for include file sys/resource.h
-- Looking for include file sys/resource.h - found
-- Looking for include file unistd.h
-- Looking for include file unistd.h - found
-- Looking for getopt
-- Looking for getopt - found
-- Looking for memcpy
-- Looking for memcpy - found
-- Looking for setpriority
-- Looking for setpriority - found
-- Looking for sigaction
-- Looking for sigaction - found
-- Looking for sighold
-- Looking for sighold - found
-- Looking for sigprocmask
-- Looking for sigprocmask - found
-- Looking for sigrelse
-- Looking for sigrelse - found
-- Looking for snprintf
-- Looking for snprintf - found
-- Looking for strchr
-- Looking for strchr - found
-- Looking for strerror
-- Looking for strerror - found
-- Performing Test SIGNAL_RETURN
-- Performing Test SIGNAL_RETURN - Failed
-- Performing Test TIME_T_DEFINED
-- Performing Test TIME_T_DEFINED - Success
-- Configuring done
-- Generating done
-- Build files have been written to: /home/postgres/pg_top-master
[postgres@lyp pg_top-master]$
安装软件
问题1
[postgres@lyp pg_top-master]$ make install
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
/home/postgres/pg_top-master/machine/m_remote.c:14:24: fatal error: bsd/stdlib.h: No such file or directory
#include <bsd/stdlib.h>
^
compilation terminated.
make[2]: *** [CMakeFiles/pg_top.dir/machine/m_remote.c.o] Error 1
make[1]: *** [CMakeFiles/pg_top.dir/all] Error 2
make: *** [all] Error 2
[postgres@lyp pg_top-master]$
在执行make的过程中，遇到了一个报错。在centos/redhat系统都会报这个错误。该错误提示找不到bsd/stdlib.h。可以根据报错大致判断是因为缺少bsd的lib包导致的。
安装libbsd包
libbsd下载地址：https://cdn.modb.pro/download/359238 libbsd-devel下载地址：https://cdn.modb.pro/download/359481
[root@lyp ~]# rpm -ivh libbsd-0.8.3-1.el7.x86_64.rpm warning: libbsd-0.8.3-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:libbsd-0.8.3-1.el7 ################################# [100%] [root@lyp ~]# rpm -ivh libbsd-devel-0.8.3-1.el7.x86_64.rpm warning: libbsd-devel-0.8.3-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:libbsd-devel-0.8.3-1.el7 ################################# [100%] [root@lyp ~]#
问题2
此时重新安装不能重复执行make install继续安装。否则会报以下报错。
[postgres@lyp pg_top-master]$ make install
[ 7%] Building C object CMakeFiles/pg_top.dir/machine/m_remote.c.o
[ 15%] Building C object CMakeFiles/pg_top.dir/machine/m_common.c.o
[ 23%] Building C object CMakeFiles/pg_top.dir/machine/m_linux.c.o
Linking C executable pg_top
CMakeFiles/pg_top.dir/machine/m_remote.c.o: In function `get_process_info_r':
m_remote.c:(.text+0x2d8c): undefined reference to `reallocarray'
CMakeFiles/pg_top.dir/machine/m_linux.c.o: In function `get_process_info':
m_linux.c:(.text+0x2d06): undefined reference to `reallocarray'
collect2: error: ld returned 1 exit status
make[2]: *** [pg_top] Error 1
make[1]: *** [CMakeFiles/pg_top.dir/all] Error 2
make: *** [all] Error 2
[postgres@lyp pg_top-master]$
重新安装
此时需要把之前的安装目录清理掉，重新安装。
[postgres@lyp pg_top-master]$ cd ..
[postgres@lyp ~]$ rm -rf pg_top-master
[postgres@lyp ~]$ unzip pg_top-master.zip
Archive: pg_top-master.zip
fb32e974ee21f2f9af58f08bbdfe53b38a57207f
creating: pg_top-master/
inflating: pg_top-master/.gitignore
..........
[postgres@lyp ~]$ cd pg_top-master/
[postgres@lyp pg_top-master]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pgsql14.1/ CMakeLists.txt
-- The C compiler identification is GNU 4.8.5
-- Check for working C compiler: /bin/cc
..........
[postgres@lyp pg_top-master]$ make install
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
Install the project...
-- Install configuration: ""
-- Installing: /opt/pgsql14.1/bin/pg_top
-- Installing: /opt/pgsql14.1/share/man/man1/pg_top.1
[postgres@lyp pg_top-master]$
pg_top 使用
帮助说明
[postgres@lyp pg_top-master]$ pg_top --help
pg_top monitors a PostgreSQL database cluster.
Usage:
pg_top [OPTION]... [COUNT]
General options:
-b, --batch use batch mode --使用batch模式
-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统计数据
-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
[postgres@lyp pg_top-master]$
使用说明
通过pg_top可以监控主机的负载情况。包括CPU、内存、SWAP交换分区。以及PG进程信息。
监控时，我们可以关注主机的负载情况，也可以看进程的一些信息（XTIME/QTIME/LOCKS等信息）
这是一个动态的展示过程
[postgres@lyp ~]$ pg_top
last pid: 46448; load avg: 0.00, 0.01, 0.05; up 0+15:50:08 21:03:14
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states: 0.0% user, 0.0% nice, 0.0% system, 100% idle, 0.0% iowait
Memory: 2933M used, 7047M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
46449 postgres 273M 7352K active 0:00 0:00 0.0 8 postgres: postgres postgres [local] idle
44513 272M 2192K 0:00 0:00 0.0 0 postgres: autovacuum launcher
45597 replxs 272M 3200K idle 0:00 0:00 0.0 0 postgres: replxs postgres [local] idle
44511 272M 2136K 0:00 0:00 0.0 0 postgres: background writer
45318 replxs 272M 2640K active 0:00 0:00 0.0 0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24000148
44517 postgres 283M 15M idle 0:00 0:00 1.0 0 postgres: postgres postgres 192.168.60.1(56947) idle
44514 271M 1112K 0:00 0:00 0.0 0 postgres: archiver last was 000000010000000000000023.00000028.backup
44512 271M 5036K 0:00 0:00 0.0 0 postgres: walwriter
44516 postgres 272M 1592K 0:00 0:00 0.0 0 postgres: logical replication launcher
44510 272M 2556K 0:00 0:00 0.0 0 postgres: checkpointer
系统负载
load avg: 0.00, 0.01, 0.05;
进程数
10 processes: 6 other background task(s), 2 idle, 2 active
进程数量：10 ，后台进程：6 ，idle进程：2 ，活动进程：2
系统CPU情况
CPU states: 0.0% user, 0.0% nice, 0.0% system, 100% idle, 0.0% iowait
系统内存情况
Memory: 2933M used, 7047M free, 0K shared, 3664K buffers, 1572M cached
SWAP情况
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PG进程信息
| 列名 | 信息 |
|---|---|
| PID | 进程的pid |
| USERNAME | 用户名 |
| SIZE | 进程使用内存 |
| RES | 常驻内存大小 |
| STATE | 状态 |
| XTIME | 事务时间 |
| QTIME | query执行时间 |
| %CPU | 占用Cpu百分比 |
| LOCKS | 持有锁数量 |
| COMMAND | 操作命令 |
远程监控
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
监控remote主机的信息时，需要对remote主机上安装pg_proctab插件，只有安装插件才能在remote主机上进行pg_top命令的使用。
插件安装
在remote主机上安装pg_proctab插件
插件下载
墨天轮地址：https://www.modb.pro/download/430532 gitlb地址：https://gitlab.com/pg_proctab/pg_proctab gihub地址：https://github.com/markwkm/pg_proctab
插件安装
[postgres@lyp ~]$ ls -lrt pg_proctab-main.zip
-rw-r--r--. 1 postgres postgres 19062 Feb 10 14:30 pg_proctab-main.zip
[postgres@lyp ~]$ unzip pg_proctab-main.zip
Archive: pg_proctab-main.zip
e64333e8355586efb4c3fa2fced992450ab41795
creating: pg_proctab-main/
..........
inflating: pg_proctab-main/src/pg_proctab.c
inflating: pg_proctab-main/src/pg_proctab.h
[postgres@lyp ~]$ cd pg_proctab-main/
[postgres@lyp pg_proctab-main]$ make && make install
cp sql/pg_proctab.sql sql/pg_proctab--0.0.9.sql
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o src/pg_proctab.o src/pg_proctab.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC src/pg_proctab.o -L/opt/pgsql14.1/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql14.1/lib',--enable-new-dtags -shared -o src/pg_proctab.so
/usr/bin/mkdir -p '/opt/pgsql14.1/share/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/share/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/lib'
/usr/bin/mkdir -p '/opt/pgsql14.1/share/doc/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/bin'
/usr/bin/install -c -m 644 .//pg_proctab.control '/opt/pgsql14.1/share/extension/'
/usr/bin/install -c -m 644 .//sql/pg_proctab--0.0.5--0.0.6.sql .//sql/pg_proctab--0.0.9.sql '/opt/pgsql14.1/share/extension/'
/usr/bin/install -c -m 755 src/pg_proctab.so '/opt/pgsql14.1/lib/'
/usr/bin/install -c -m 644 .//doc/README.pg_proctab '/opt/pgsql14.1/share/doc/extension/'
/usr/bin/install -c -m 755 .//contrib/ps-io-utilization.sh .//contrib/ps-processor-utilization.sh .//contrib/ps-util.pl .//contrib/ps-report.pl '/opt/pgsql14.1/bin/'
[postgres@lyp pg_proctab-main]$ psql
psql (14.1)
Type "help" for help.
postgres=# create extension pg_proctab ;
CREATE EXTENSION
postgres=#
远程监控
[postgres@lyp pgdata-14]$ pg_top -U postgres -d postgres -h 192.168.60.190 -p 5433
last pid: 48082; load avg: 0.00, 0.01, 0.05; up 0+16:15:49 21:28:54
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states: 0.0% user, 0.0% nice, 0.5% system, 99.5% idle, 0.0% iowait
Memory: 2937M used, 7043M free, 0K shared, 3664K buffers, 1575M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
48083 postgres 273M 7328K active 0:00 0:00 0.0 8 postgres: postgres postgres 192.168.60.190(50334) idle
44513 272M 2432K 0:00 0:00 0.0 0 postgres: autovacuum launcher
45597 replxs 272M 3200K idle 0:00 0:00 0.0 0 postgres: replxs postgres [local] idle
44511 272M 2608K 0:00 0:00 0.0 0 postgres: background writer
45318 replxs 272M 3028K active 0:00 0:00 0.0 0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24011DF0
44517 postgres 283M 15M idle 0:00 0:00 0.0 0 postgres: postgres postgres 192.168.60.1(56947) idle
44514 271M 1392K 0:00 0:00 0.0 0 postgres: archiver last was 000000010000000000000023.00000028.backup
44512 272M 5556K 0:00 0:00 0.0 0 postgres: walwriter
44516 postgres 272M 2004K 0:00 0:00 0.0 0 postgres: logical replication launcher
44510 272M 3152K 0:00 0:00 0.0 0 postgres: checkpointer
常用参数说明
参数：-X
display i/o stats 显示i/o统计数据
展示PostgreSQL数据库每个进程的I/O信息。例如iops、Reads、Writes。能够监控到高耗I/O的进程。
[postgres@lyp ~]$ pg_top -X
last pid: 44147; load avg: 0.00, 0.01, 0.05; up 0+15:21:14 20:34:19
15 processes: 6 other background task(s), 7 idle, 1 active, 1 idle txn
CPU states: 0.2% user, 0.0% nice, 0.6% system, 99.2% idle, 0.0% iowait
Memory: 2703M used, 7277M free, 0K shared, 3664K buffers, 1340M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID IOPS IORPS IOWPS READS WRITES COMMAND
44148 10 0 10 0B 0B postgres: postgres postgres [local] idle
20646 0 0 0 0B 0B postgres: archiver
43084 0 0 0 0B 0B postgres: postgres mydb 192.168.60.1(51762) idle
20648 0 0 0 0B 0B postgres: logical replication launcher
43059 9 0 9 0B 0B postgres: postgres postgres 192.168.60.1(51711) idle
44020 0 0 0 0B 0B postgres: postgres postgres [local] idle
20645 0 0 0 0B 0B postgres: autovacuum launcher
43053 0 0 0 0B 0B postgres: lxs postgres 192.168.60.1(51689) idle
20642 0 0 0 0B 0B postgres: checkpointer
20644 0 0 0 0B 0B postgres: walwriter
20643 0 0 0 0B 0B postgres: background writer
43071 0 0 0 0B 0B postgres: lxs mydb 192.168.60.1(51749) idle
43086 0 0 0 0B 0B postgres: postgres mydb1 192.168.60.1(51766) idle
42743 0 0 0 0B 0B postgres: postgres postgres [local] idle in transaction
43064 0 0 0 0B 0B postgres: lxs mydb1 192.168.60.1(51730) idle
参数：-R
display replication stats 显示复制统计信息
监控主从复制信息。监控主从延迟、监控主从的lsn的位置。这个跟在数据库里面查询pg_stat_replication一样的数据。
[postgres@lyp ~]$ pg_top -R
last pid: 45377; load avg: 0.00, 0.01, 0.05; up 0+15:35:10 20:48:15
1 processes:
CPU states: 0.0% user, 0.0% nice, 0.0% system, 100% idle, 0.0% iowait
Memory: 2931M used, 7049M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME APPLICATION CLIENT STATE PRIMARY SENT WRITE FLUSH REPLAY SLAG WLAG FLAG RLAG
45318 replxs walreceiver 192.168.60.190 streaming 0/24000148 0/24000148 0/24000148 0/24000148 0/24000148 0B 0B 0B 0B
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag |
replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+-
-----------+---------------+------------+-------------------------------
45318 | 24749 | replxs | walreceiver | 192.168.60.190 | | 50318 | 2022-02-10 20:47:27.588443+08 | | streaming | 0/24000148 | 0/24000148 | 0/24000148 | 0/24000148 | | |
| 0 | async | 2022-02-10 20:48:57.789438+08
(1 row)
postgres=#
参数：-z
–show-username=NAME display only processes owned by given username --仅显示给定进程所拥有的进程用户名
如果数据库里面的用户比较多，也可以按用户做过滤。可以只监控replxs，监控这个用户会话连接的相关信息。
[postgres@lyp ~]$ pg_top -z replxs
last pid: 45603; load avg: 0.00, 0.01, 0.05; up 0+15:38:27 20:51:33
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states: 0.5% user, 0.0% nice, 0.5% system, 99.0% idle, 0.0% iowait
Memory: 2932M used, 7048M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
45597 replxs 272M 3200K idle 0:00 0:00 0.0 0 postgres: replxs postgres [local] idle
45318 replxs 272M 2640K active 0:00 0:00 0.0 0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24000148
参数：-o
–order-field=FIELD select sort order --选择排序顺序
如果想要对显示出来的数据进行排序，比如对xtime/qtime进行排序
注意：列名需要使用小写
[postgres@lyp ~]$ pg_top -o xtime
last pid: 45681; load avg: 0.05, 0.03, 0.05; up 0+15:39:41 20:52:46
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states: 0.0% user, 0.0% nice, 0.5% system, 99.5% idle, 0.0% iowait
Memory: 2933M used, 7047M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
45682 postgres 273M 7352K active 0:00 0:00 0.0 8 postgres: postgres postgres [local] idle
45597 replxs 272M 3200K idle 0:00 0:00 0.0 0 postgres: replxs postgres [local] idle
45318 replxs 272M 2640K active 0:00 0:00 0.0 0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24000148
44517 postgres 283M 15M idle 0:00 0:00 1.0 0 postgres: postgres postgres 192.168.60.1(56947) idle
44513 272M 2192K 0:00 0:00 0.0 0 postgres: autovacuum launcher
44511 272M 2136K 0:00 0:00 0.0 0 postgres: background writer
44514 271M 1112K 0:00 0:00 0.0 0 postgres: archiver last was 000000010000000000000023.00000028.backup
44512 271M 5036K 0:00 0:00 0.0 0 postgres: walwriter
44516 postgres 272M 1592K 0:00 0:00 0.0 0 postgres: logical replication launcher
44510 272M 2556K 0:00 0:00 0.0 0 postgres: checkpointer
参数：-x
set maximum number of displays exit once this number is reached --设置最大显示数量，达到此数后退出
适用于输出结果至文本，长期记录。
[postgres@lyp ~]$ pg_top -b -x 20 > pg_top.log
[postgres@lyp ~]$ more pg_top.log
last pid: 47809; load avg: 0.00, 0.01, 0.05; up 0+16:11:42 21:24:47
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states: 0.0% user, 0.0% nice, 0.5% system, 99.0% idle, 0.5% iowait
Memory: 2936M used, 7044M free, 0K shared, 3664K buffers, 1575M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
47810 postgres 273M 7352K active 0:00 0:00 0.0 8 postgres: postgres postgres [local] idle
44513 272M 2192K 0:00 0:00 0.0 0 postgres: autovacuum launcher
45597 replxs 272M 3200K idle 0:00 0:00 0.0 0 postgres: replxs postgres [local] idle
44511 272M 2136K 0:00 0:00 0.0 0 postgres: background writer
45318 replxs 272M 2896K active 0:00 0:00 0.0 0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24011D08
44517 postgres 283M 15M idle 0:00 0:00 0.0 0 postgres: postgres postgres 192.168.60.1(56947) idle
44514 271M 1112K 0:00 0:00 0.0 0 postgres: archiver last was 000000010000000000000023.00000028.backup
44512 271M 5036K 0:00 0:00 0.0 0 postgres: walwriter
44516 postgres 272M 1592K 0:00 0:00 0.0 0 postgres: logical replication launcher
44510 272M 2556K 0:00 0:00 0.0 0 postgres: checkpointer
last pid: 47814; load avg: 0.00, 0.01, 0.05; up 0+16:11:47 21:24:52
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states: 0.2% user, 0.0% nice, 0.1% system, 99.7% idle, 0.0% iowait
Memory: 2936M used, 7044M free, 0K shared, 3664K buffers, 1575M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out
PID USERNAME SIZE RES STATE XTIME QTIME %CPU LOCKS COMMAND
47815 postgres 273M 7352K active 0:00 0:00 0.0 8 postgres: postgres postgres [local] idle
44513 272M 2192K 0:00 0:00 0.0 0 postgres: autovacuum launcher
45597 replxs 272M 3200K idle 0:00 0:00 0.0 0 postgres: replxs postgres [local] idle
44511 272M 2136K 0:00 0:00 0.0 0 postgres: background writer
45318 replxs 272M 2896K active 0:00 0:00 0.0 0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24011D08
44517 postgres 283M 15M idle 0:00 0:00 0.4 0 postgres: postgres postgres 192.168.60.1(56947) idle
44514 271M 1112K 0:00 0:00 0.0 0 postgres: archiver last was 000000010000000000000023.00000028.backup
44512 271M 5036K 0:00 0:00 0.0 0 postgres: walwriter
44516 postgres 272M 1592K 0:00 0:00 0.0 0 postgres: logical replication launcher
44510 272M 2556K 0:00 0:00 0.0 0 postgres: checkpointer
帮助命令
进入pg_top之后，可以按 h 进入帮助页面，可以显示可以使用说明指令。
再根据想要获取的信息，直接输入指令即可。
pg_top version 4.0.0, Copyright (c) 1984 through 2007, William LeFebvre
A top users display for PostgreSQL
These single-character commands are available:
^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) --显示每个进程的I/O统计信息
L - show locks held by a process --显示进程持有的锁
Q - show current query of a process --显示进程的当前查询
c - toggle the display of process commands
d - change number of displays to show --更改要显示的显示器数量
h or ? - help; show this text
i - toggle the displaying of idle processes --切换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.
Hit any key to continue:
最后修改时间：2022-02-12 12:12:29
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
