最近同事问有没有一款工具可以实时查看postgresql数据库当前的每秒增删改查,翻了一圈,有一款叫pgcenter的工具可以满足需求,而且某些方面更加详细和强大。这款工具目前已经开源,使用也很方便,简介之。
一、环境及下载
OS:Centos 6.6
DB:PostgreSQl 9.6
git clone https://github.com/lesovsky/pgcenter
二、安装
需要在/usr/bin下创建可执行文件,需要sudo权限
[root@temp postgres]# upzip pgcenter-master.zip
[root@temp postgres]# cd pgcenter-master
[root@temp postgres]# make
[root@temp postgres]# make install
[root@temp pgcenter-master]# which pgcenter
/usr/bin/pgcenter
[root@temp pgcenter-master]# source /home/postgres/.bash_profile
[root@temp pgcenter-master]# pgcenter -V
pgcenter 0.3.0
三、使用
简单做个压测,看看效果
[postgres@temp ~]$ more test.conf
\set id random(1,2000000)
begin;
insert into tbl_kenyon(id,ctime) values(:id,now());
SELECT id FROM tbl_kenyon WHERE id = :id;
end;
[postgres@temp ~]$ pgbench -M simple -r -c 8 -f /home/postgres/test.conf -j 1 -n -T 120 db_kenyon
--开启监控,具体效果图见下
[root@temp ~]# pgcenter db_kenyon postgres
效果图如下,增删改查数每秒会刷新一次:
还有其他的一些监控指标,比如监控表的大小,索引的大小,IO、vacuum、当前进程,等,具体用法见下:
Help for interactive commands - pgcenter version 0.3.0
general actions:
a,d,i,f,r mode: 'a' activity, 'd' databases, 'i' indexes, 'f' functions, 'r' replication,
s,t,T,v 's' tables sizes, 't' tables, 'T' tables IO, 'v' vacuum progress,
x,X 'x' pg_stat_statements switch, 'X' pg_stat_statements menu.
Left,Right,/,F 'Left,Right' change column sort, '/' change sort desc/asc, 'F' set filter.
C,E,R config: 'C' show config, 'E' edit configs, 'R' reload config.
p 'p' start psql session.
l 'l' open log file with pager.
N,Ctrl+D,W 'N' add new connection, Ctrl+D close current connection, 'W' write connections info.
1..8 switch between consoles.
subscreen actions:
B,I,L 'B' iostat, 'I' nicstat, 'L' logtail.
activity actions:
-,_ '-' cancel backend by pid, '_' terminate backend by pid.
>,. '>' set new mask, '.' show current mask.
Del,Shift+Del 'Del' cancel backend group using mask, 'Shift+Del' terminate backend group using mask.
A change activity age threshold.
G get report about query using hash.
other actions:
, Q ',' show system tables on/off, 'Q' reset postgresql statistics counters.
z,Z 'z' set refresh interval, 'Z' change color scheme.
space pause program execution.
h,F1 show help screen.
q quit.
Type 'Esc' to continue.