ANBOB™

操作系统有top (AIX topas)可以监视系统的进程信息， oracle也有著名的oratop 工具显示顶级会话信息，目前在oracle 12c以后的版本oratop已经集成到了安装介质中， PostgreSQL 也有一个类似的工具pg_top， 用于监视数据库中的实时活动，以及查看数据库主机本身的基本信息，也可以交互显示及定期自动刷新。

安装（CentOS Linux release 7.2）

--as root
# yum install pg_top
...
Total download size: 57 k
Installed size: 110 k
Is this ok [y/d/N]: y
Downloading packages:
pg_top-3.7.0-5.el7.x86_64.rpm                                                                                                         |  57 kB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_top-3.7.0-5.el7.x86_64                                                                                                                 1/1
  Verifying  : pg_top-3.7.0-5.el7.x86_64                                                                                                                 1/1

Installed:
  pg_top.x86_64 0:3.7.0-5.el7
Complete!
# which pg_top
/usr/bin/pg_top

测试

# su - postgre
Last login: Sun Jan  5 20:06:19 CST 2020 on pts/7
$ pg_top
pg_top: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory
$ env|grep LIB
LD_LIBRARY_PATH=/u01/soft/client_1/lib:/u01/soft/client_1/lib32:/lib/usr/lib:/usr/local/lib
$ which psql
/sas_data/soft/pgsql12/bin/psql

$ export LD_LIBRARY_PATH=/sas_data/soft/pgsql12/lib
$ pg_top

用法
–local

pg_top -h localhost -p 5432 -d mydb -U postgres

— remote

pg_top -r -h 192.168.1.20 -p 5432 -d mydb -U postgres

新开个会话，跑一些SQL

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
	
CREATE TABLE measurement_y2020 PARTITION OF measurement
    FOR VALUES FROM ('2020-01-01') TO ('2020-12-31');
	
mydb=# insert into measurement values(1,'2006-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2006-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2020-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2020-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2020-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2020-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2020-02-01',1,1);
INSERT 0 1
mydb=# insert into measurement values(1,'2020-02-01',1,1);
INSERT 0 1
mydb=# SELECT tableoid::regclass, * FROM measurement;
       tableoid       | city_id |  logdate   | peaktemp | unitsales
----------------------+---------+------------+----------+-----------
 measurement_y2006m02 |       1 | 2006-02-01 |        1 |         1
 measurement_y2006m02 |       1 | 2006-02-01 |        1 |         1
 measurement_y2020    |       1 | 2020-02-01 |        1 |         1
 measurement_y2020    |       1 | 2020-02-01 |        1 |         1
 measurement_y2020    |       1 | 2020-02-01 |        1 |         1
 measurement_y2020    |       1 | 2020-02-01 |        1 |         1
 measurement_y2020    |       1 | 2020-02-01 |        1 |         1
 measurement_y2020    |       1 | 2020-02-01 |        1 |         1
(8 rows)

mydb=# select * from measurement,measurement,measurement,measurement,measurement,measurement,measurement,measurement,measurement,measurement;
psql: ERROR:  table name "measurement" specified more than once

mydb=# select * from measurement,measurement t1,measurement t2,measurement t3,measurement t4,measurement t5,measurement t6,measurement t7,measurement t8,measurement t9;

note:
PostgreSQL分区语法真是奇怪要先创建一个主表，再建子表，但是EDB(PostgreSQL分支)语法已经和Oracle一样了，以后版本可能会统一。查询也不能像ORACLE一样使用同名的表做多次笛卡尔积，需要给不同的别名。

显示PG_TOP

# pg_top
last pid: 31357;  load avg:  1.42,  0.58,  0.44;       up 158+22:22:38                                                                              20:49:30
7 processes: 1 running, 6 sleeping
CPU states:  2.6% user,  0.0% nice,  2.2% system, 94.7% idle,  0.6% iowait
Memory: 31G used, 213M free, 16M buffers, 8406M cached
DB activity:   0 tps,  0 rollbs/s,   0 buffer r/s, 100 hit%,      1 row r/s,    0 row w/s
DB I/O:     0 reads/s,     0 KB/s,     0 writes/s,     0 KB/s
DB disk: 499.8 GB total, 256.0 GB free (48% used)
Swap:
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
26870 pg12 20 0 171M 8684K run 0:09 0.69% 1.38% postgres: pg12 mydb [local] SELECT
27341 pg12 20 0 166M 1664K sleep 0:17 0.00% 0.00% postgres: autovacuum launcher
27340 pg12 20 0 165M 4724K sleep 0:08 0.00% 0.00% postgres: walwriter
27339 pg12 20 0 165M 1864K sleep 0:07 0.00% 0.00% postgres: background writer
27343 pg12 20 0 166M 1128K sleep 0:00 0.00% 0.00% postgres: logical replication launcher
27338 pg12 20 0 165M 5184K sleep 0:00 0.00% 0.00% postgres: checkpointer
31358 pg12 20 0 166M 5072K sleep 0:00 0.00% 0.00% postgres: pg12 postgres [local] idle

pg_top也可以用于shell批处理中，入在OSW中调用top一样，定时记录负载信息在文本日志文件中，分析历史负载信息和报告，也可用于告警等。

mydb=# select * from measurement,measurement t1,measurement t2,measurement t3,measurement t4,measurement t5,measurement t6,measurement t7,measurement t8,measurement t9;
Killed
$

另外不知为何在运行15s左右自动被KILL了，^_^！ 后面再分析。

打赏

微信扫一扫，打赏作者吧～