有些时候想统计一下PostgreSQL当前的查询数或者事务数,就直接用内置的视图去统计,但可视化或可读性不是很高,以前看到的pgcenter这个工具挺好的,但是没有记录历史的QTPS,只有实时刷的最新值,同事问起,周末就写了个小工具,用来查询数据库当前的QTPS。
一、脚本代码
#!/usr/bin/env python
#coding=utf-8
import time
import sys
import psycopg2 as pgdb
#传入参数
i_host = sys.argv[1]
i_port = sys.argv[2]
i_pwd = sys.argv[3]
conn = pgdb.connect(host=i_host,database='postgres',user='postgres',port=i_port,password=i_pwd)
cursor = conn.cursor()
sql = "select sum(tup_inserted) ,sum(tup_updated) ,sum(tup_deleted) ,sum(xact_commit) ,sum(xact_rollback) from pg_stat_database where datname not in('postgres','template1','template0');"
while True:
try:
cursor.execute(sql)
conn.commit()
results = cursor.fetchall()
for row in results:
p_ins = row[0]
p_upd = row[1]
p_del = row[2]
p_com = row[3]
p_rol = row[4]
time.sleep(1)
cursor.execute(sql)
conn.commit()
results2 = cursor.fetchall()
for row in results2:
p_ins2 = row[0]
p_upd2 = row[1]
p_del2 = row[2]
p_com2 = row[3]
p_rol2 = row[4]
ins_diff = int(p_ins2) - int(p_ins)
upd_diff = int(p_upd2) - int(p_upd)
del_diff = int(p_del2) - int(p_del)
iud_diff = int(ins_diff) + int(upd_diff) + int(del_diff)
com_diff = int(p_com2) - int(p_com)
rol_diff = int(p_rol2) - int(p_rol)
tps_diff = int(com_diff) + int(rol_diff)
print "%s ins=%-8s, upd=%-8s, del=%-8s, com=%-8s ,rol=%-8s ,iud=%-8s ,TPS=%-8s" %(time.strftime("%Y-%m-%d %H:%M:%S"),ins_diff,upd_diff,del_diff,com_diff,rol_diff,iud_diff,tps_diff)
except KeyboardInterrupt :
print "exit .."
sys.exit()
conn.close()
二、实际效果
压测的脚本
\set id random(1,2000000)
begin;
insert into tbl_kenyon(id,ctime) values(:id,now());
SELECT id FROM tbl_kenyon WHERE id = :id;
update tbl_kenyon set ctime = now() where id = :id;
delete from tbl_kenyon where id = :id;
end;
实际的效果
[postgres@kenyon ~]$ python pgsql_qtps.py localhost 1949 123456
2017-02-20 12:41:37 ins=0 , upd=0 , del=0 , com=0 ,rol=0 ,iud=0 ,TPS=0
2017-02-20 12:41:38 ins=0 , upd=0 , del=0 , com=0 ,rol=0 ,iud=0 ,TPS=0
2017-02-20 12:41:39 ins=0 , upd=0 , del=0 , com=0 ,rol=0 ,iud=0 ,TPS=0
2017-02-20 12:41:40 ins=0 , upd=0 , del=0 , com=15 ,rol=0 ,iud=0 ,TPS=15
2017-02-20 12:41:41 ins=5966 , upd=12318 , del=12318 , com=6127 ,rol=0 ,iud=30602 ,TPS=6127
2017-02-20 12:41:42 ins=5038 , upd=10301 , del=10301 , com=5038 ,rol=0 ,iud=25640 ,TPS=5038
2017-02-20 12:41:43 ins=4457 , upd=9191 , del=9191 , com=4457 ,rol=0 ,iud=22839 ,TPS=4457
2017-02-20 12:41:44 ins=1368 , upd=2829 , del=2829 , com=1368 ,rol=0 ,iud=7026 ,TPS=1368
2017-02-20 12:41:45 ins=1694 , upd=3426 , del=3426 , com=1694 ,rol=0 ,iud=8546 ,TPS=1694
2017-02-20 12:41:46 ins=5919 , upd=12158 , del=12158 , com=5919 ,rol=0 ,iud=30235 ,TPS=5919
2017-02-20 12:41:47 ins=3866 , upd=7932 , del=7932 , com=3866 ,rol=0 ,iud=19730 ,TPS=3866
2017-02-20 12:41:48 ins=5286 , upd=10712 , del=10712 , com=5286 ,rol=0 ,iud=26710 ,TPS=5286
2017-02-20 12:41:49 ins=3133 , upd=6357 , del=6357 , com=3133 ,rol=0 ,iud=15847 ,TPS=3133
2017-02-20 12:41:50 ins=1355 , upd=2790 , del=2790 , com=1355 ,rol=0 ,iud=6935 ,TPS=1355
2017-02-20 12:41:51 ins=1257 , upd=2581 , del=2581 , com=1257 ,rol=0 ,iud=6419 ,TPS=1257
2017-02-20 12:41:52 ins=4881 , upd=9987 , del=9987 , com=4881 ,rol=0 ,iud=24855 ,TPS=4881
2017-02-20 12:41:53 ins=4432 , upd=8898 , del=8898 , com=4432 ,rol=0 ,iud=22228 ,TPS=4432
2017-02-20 12:41:54 ins=5312 , upd=10529 , del=10529 , com=5312 ,rol=0 ,iud=25230 ,TPS=5042
2017-02-20 12:42:04 ins=5699 , upd=11351 , del=11351 , com=5699 ,rol=0 ,iud=28401 ,TPS=5699
2017-02-20 12:42:05 ins=6299 , upd=12546 , del=12546 , com=6299 ,rol=0 ,iud=31391 ,TPS=6299
2017-02-20 12:42:06 ins=3562 , upd=7156 , del=7156 , com=3562 ,rol=0 ,iud=17874 ,TPS=3562
2017-02-20 12:42:08 ins=2021 , upd=4014 , del=4014 , com=2021 ,rol=0 ,iud=10049 ,TPS=2021
2017-02-20 12:42:09 ins=4442 , upd=8742 , del=8742 , com=4442 ,rol=0 ,iud=21926 ,TPS=4442
2017-02-20 12:42:10 ins=2614 , upd=5222 , del=5222 , com=2614 ,rol=0 ,iud=13058 ,TPS=2614
2017-02-20 12:42:11 ins=2235 , upd=4487 , del=4487 , com=2235 ,rol=0 ,iud=11209 ,TPS=2235
2017-02-20 12:42:12 ins=0 , upd=0 , del=0 , com=0 ,rol=0 ,iud=0 ,TPS=0
2017-02-20 12:42:13 ins=0 , upd=0 , del=0 , com=0 ,rol=0 ,iud=0 ,TPS=0
2017-02-20 12:42:14 ins=0 , upd=0 , del=0 , com=0 ,rol=0 ,iud=0 ,TPS=0
其他:
1.PG和Mysq不一样的地方是不能通过show状态来获取com_select诸如查询变量值,而查询在pg中是作为一项事务提交的
2.脚本统计的是单台服务器上所有的实例,也可以通过改SQL来得到某个库的实时QTPS值
3.最新的9.6版本pgbench已经不支持常用的setrandom参数,可以调整为set id random