PG_TOP使用整理

PostgreSQL数据库工作学习随笔 2022-06-08

5663

安装pg_top

首先安装libbsd，下载地址https://pkgs.org/

[root@db3 atlasdb]# 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@db3 atlasdb]# rpm -ivh libbsd-devel-0.8.3-1.el7
error: open of libbsd-devel-0.8.3-1.el7 failed: No such file or directory
[root@db3 atlasdb]# 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@db3 atlasdb]#

切换到数据库用户下并解压文件
[root@db3 opt]# su - atlasdb
Last login: Tue Jun  7 02:05:24 EDT 2022 on pts/2
[atlasdb@db3 ~]$ tar zxvf pg_top-main.tar.gz
...
pg_top-main/utils.h
pg_top-main/version.c
pg_top-main/version.h
[atlasdb@db3 ~]$ cd pg_top-main
[atlasdb@db3 pg_top-main]$ cat 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
[atlasdb@db3 pg_top-main]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pg_top CMakeLists.txt
CMake Deprecation Warning at CMakeLists.txt:1 (cmake_minimum_required):
  Compatibility with CMake < 2.8.12 will be removed from a future version of
  CMake.


  Update the VERSION argument <min> value or use a ...<max> suffix to tell
  CMake that the project does not need compatibility with older versions.




-- The C compiler identification is GNU 4.8.5
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working C compiler: bin/cc - skipped
-- Detecting C compile features
-- Detecting C compile features - done
-- machine - linux
-- arch - x86_64
-- pg_config --includedir - home/atlasdb/atlasdb/include
-- pg_config --libdir - home/atlasdb/atlasdb/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/atlasdb/pg_top-main
编译pg_top
[atlasdb@db3 pg_top-main]$ make install
[  7%] Building C object CMakeFiles/pg_top.dir/color.c.o
[ 14%] Building C object CMakeFiles/pg_top.dir/commands.c.o
[ 21%] Building C object CMakeFiles/pg_top.dir/display.c.o
[ 28%] Building C object CMakeFiles/pg_top.dir/getopt.c.o
[ 35%] Building C object CMakeFiles/pg_top.dir/screen.c.o
[ 42%] Building C object CMakeFiles/pg_top.dir/sprompt.c.o
[ 50%] Building C object CMakeFiles/pg_top.dir/pg.c.o
[ 57%] Building C object CMakeFiles/pg_top.dir/pg_top.c.o
[ 64%] Building C object CMakeFiles/pg_top.dir/utils.c.o
[ 71%] Building C object CMakeFiles/pg_top.dir/version.c.o
[ 78%] Building C object CMakeFiles/pg_top.dir/machine/m_remote.c.o
[ 85%] Building C object CMakeFiles/pg_top.dir/machine/m_common.c.o
[ 92%] Building C object CMakeFiles/pg_top.dir/machine/m_linux.c.o
[100%] Linking C executable pg_top
[100%] Built target pg_top
Install the project...
-- Install configuration: ""
-- Installing: opt/pg_top/bin/pg_top
-- Installing: /opt/pg_top/share/man/man1/pg_top.1
[atlasdb@db3 ~]$ opt/pg_top/bin/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
  -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
[atlasdb@db3 ~]$

使用示例

使用pg_top查看数据库服务器负载情况

[atlasdb@db3 ~]$ opt/pg_top/bin/pg_top -W
last pid: 13682;  load avg:  0.09,  0.25,  0.21;       up 0+03:18:58                                                            
7 processes: 5 other background task(s), 1 idle, 1 active
CPU states:  0.0% user,  0.0% nice,  0.2% system, 99.6% idle,  0.2% iowait
Memory: 169M used, 815M free, 0K shared, 0K buffers, 49M cached
Swap: 201M used, 2663M free, 6972K cached, 96K in, 0K out


    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  13603 atlasdb     614M   12M active   0:00   0:00   0.2     8 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle
   1732             608M  260K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: background writer
   1734             609M  996K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: autovacuum launcher
   1733             607M 1060K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: walwriter
   1737 atlasdb     609M  540K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: logical replication launcher
   1731             608M  612K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: checkpointer
  13520 atlasdb     610M 1132K idle     0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle

查看数据库服务器中数据库进程IO情况

[atlasdb@db3 ~]$ opt/pg_top/bin/pg_top -X -W
Password:
last pid: 13875;  load avg:  1.21,  0.49,  0.29;       up 0+04:21:44                                                                                 02:47:30
7 processes: 5 other background task(s), 1 idle, 1 active
CPU states:  0.0% user,  0.0% nice, 83.3% system,  0.0% idle, 16.7% iowait
Memory: 925M used, 59M free, 0K shared, 0K buffers, 20M cached
Swap: 1194M used, 1670M free, 788M cached, 0K in, 0K out


    PID    IOPS   IORPS   IOWPS READS WRITES COMMAND
  13801       0       0       0    0B   781B vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle
   1732       0       0       0    0B     0B vastbase: AtlasDB_Cluster: background writer
   1734       0       0       0    0B     0B vastbase: AtlasDB_Cluster: autovacuum launcher
   1733       0       0       0    0B     0B vastbase: AtlasDB_Cluster: walwriter
   1737       0       0       0    0B     0B vastbase: AtlasDB_Cluster: logical replication launcher
   1731       0       0       0    0B     0B vastbase: AtlasDB_Cluster: checkpointer
  13520       0       0       0    0B     0B vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle

显示主备节点复制情况（无主备环境，只显示命令）

[atlasdb@db3 ~]$ opt/pg_top/bin/pg_top -R -W
Password:
last pid: 13944;  load avg:  0.32,  0.37,  0.27;       up 0+04:23:05                                                                                 02:48:51
0 processes:
CPU states:  0.2% user,  0.0% nice,  0.6% system, 98.6% idle,  0.6% iowait
Memory: 915M used, 70M free, 0K shared, 0K buffers, 23M cached
Swap: 1190M used, 1674M free, 772M cached, 0K in, 0K out

仅显示指定用户的进程情况

[atlasdb@db3 ~]$ opt/pg_top/bin/pg_top -z atlasdb -W
last pid: 15855;  load avg:  0.01,  0.09,  0.21;       up 0+04:55:46                                                                                 03:21:32
7 processes: 5 other background task(s), 1 active, 1 idle txn
CPU states:  1.0% user,  0.0% nice,  0.0% system, 98.0% idle,  1.0% iowait
Memory: 913M used, 71M free, 0K shared, 0K buffers, 787M cached
Swap: 191M used, 2673M free, 9088K cached, 96K in, 0K out


    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  13520 atlasdb     610M  252M idltxn   9:06   9:06   0.0     1 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle in transaction
  15855 atlasdb     614M   13M active   0:00   0:00   0.0     8 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle
   1737 atlasdb     609M  500K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: logical replication launcher

按排序显示进程

[atlasdb@db3 ~]$ opt/pg_top/bin/pg_top -o xtime -W
last pid: 15215;  load avg:  0.02,  0.14,  0.29;       up 0+04:43:15                                                                                 03:09:01
8 processes: 5 other background task(s), 2 idle, 1 active
CPU states:  1.5% user,  0.0% nice,  0.0% system, 98.5% idle,  0.0% iowait
Memory: 393M used, 591M free, 0K shared, 0K buffers, 256M cached
Swap: 339M used, 2525M free, 11M cached, 0K in, 0K out


    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  15205 atlasdb     614M   13M active   0:00   0:00   0.0     8 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle
  14887 atlasdb     614M 9796K idle     0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle
  13520 atlasdb     610M 4744K idle     0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: atlasdb vastbase [local] idle
   1732             608M  102M          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: background writer
   1734             609M 1000K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: autovacuum launcher
   1733             607M 1308K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: walwriter
   1737 atlasdb     609M  424K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: logical replication launcher
   1731             608M 1368K          0:00   0:00   0.0     0 vastbase: AtlasDB_Cluster: checkpointer

显示一定数量后退出，用于信息记录

[atlasdb@db3 ~]$ opt/pg_top/bin/pg_top -o xtime -x 13 -W

帮助命令

进入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) # 显示IO信息
L       - show locks held by a process                 # 显示进程持有的锁
R       - show PostgreSQL replication subscriptions    # 显示复制信息
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:

再根据想要获取的信息，直接输入指令即可，列举几个常用命令。

显示进程的SQL语句 --/opt/pg_top/bin/pg_top -W 之后输入Q


Current query for procpid 13520:


delete from t where id <3;


Hit any key to continue:


显示进程SQL语句的执行计划 --/opt/pg_top/bin/pg_top -W 之后输入E
Current query plan for procpid 13520:


 Statement:


DELETE FROM T;


Query Plan:


Delete on t  (cost=0.00..267173.87 rows=10050687 width=6)
  ->  Seq Scan on t  (cost=0.00..267173.87 rows=10050687 width=6)




Hit any key to continue:


显示进程对应语句锁持有的锁 --/opt/pg_top/bin/pg_top -W 之后输入L
Locks held by procpid 13520:


  | database | schema | table | index | type             | granted
--+----------+--------+-------+-------+------------------+--------
1 | vastbase | public | t     |       | RowExclusiveLock | t


Hit any key to continue:

文章转载自PostgreSQL数据库工作学习随笔，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PG_TOP使用整理

评论