有些时候想统计一下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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!