我們在操作系統上經常喜歡用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插件
插件下載
墨天輪地址：http://www.sunline.cc/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進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
