有些时候想统计一下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,TPS=0 2017-02-20 12:41:39 ins=0,TPS=0 2017-02-20 12:41:40 ins=0,com=15,TPS=15 2017-02-20 12:41:41 ins=5966,upd=12318,del=12318,com=6127,iud=30602,TPS=6127 2017-02-20 12:41:42 ins=5038,upd=10301,del=10301,com=5038,iud=25640,TPS=5038 2017-02-20 12:41:43 ins=4457,upd=9191,del=9191,com=4457,iud=22839,TPS=4457 2017-02-20 12:41:44 ins=1368,upd=2829,del=2829,com=1368,iud=7026,TPS=1368 2017-02-20 12:41:45 ins=1694,upd=3426,del=3426,com=1694,iud=8546,TPS=1694 2017-02-20 12:41:46 ins=5919,upd=12158,del=12158,com=5919,iud=30235,TPS=5919 2017-02-20 12:41:47 ins=3866,upd=7932,del=7932,com=3866,iud=19730,TPS=3866 2017-02-20 12:41:48 ins=5286,upd=10712,del=10712,com=5286,iud=26710,TPS=5286 2017-02-20 12:41:49 ins=3133,upd=6357,del=6357,com=3133,iud=15847,TPS=3133 2017-02-20 12:41:50 ins=1355,upd=2790,del=2790,com=1355,iud=6935,TPS=1355 2017-02-20 12:41:51 ins=1257,upd=2581,del=2581,com=1257,iud=6419,TPS=1257 2017-02-20 12:41:52 ins=4881,upd=9987,del=9987,com=4881,iud=24855,TPS=4881 2017-02-20 12:41:53 ins=4432,upd=8898,del=8898,com=4432,iud=22228,TPS=4432 2017-02-20 12:41:54 ins=5312,upd=10529,del=10529,com=5312,iud=25230,TPS=5042 2017-02-20 12:42:04 ins=5699,upd=11351,del=11351,com=5699,iud=28401,TPS=5699 2017-02-20 12:42:05 ins=6299,upd=12546,del=12546,com=6299,iud=31391,TPS=6299 2017-02-20 12:42:06 ins=3562,upd=7156,del=7156,com=3562,iud=17874,TPS=3562 2017-02-20 12:42:08 ins=2021,upd=4014,del=4014,com=2021,iud=10049,TPS=2021 2017-02-20 12:42:09 ins=4442,upd=8742,del=8742,com=4442,iud=21926,TPS=4442 2017-02-20 12:42:10 ins=2614,upd=5222,del=5222,com=2614,iud=13058,TPS=2614 2017-02-20 12:42:11 ins=2235,upd=4487,del=4487,com=2235,iud=11209,TPS=2235 2017-02-20 12:42:12 ins=0,TPS=0 2017-02-20 12:42:13 ins=0,TPS=0 2017-02-20 12:42:14 ins=0,TPS=0
其他： 1.PG和Mysq不一样的地方是不能通过show状态来获取com_select诸如查询变量值，而查询在pg中是作为一项事务提交的 2.脚本统计的是单台服务器上所有的实例，也可以通过改sql来得到某个库的实时QTPS值 3.最新的9.6版本pgbench已经不支持常用的setrandom参数，可以调整为set id random
