mysql的qps和tps

做db基准测试的时候，有2个指标很重要。

QPS：Queries Per Second         查询量/秒，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理查询量多少的衡量标准。

TPS :  Transactions Per Second   是事务数/秒，是一台数据库服务器在单位时间内处理的事务的个数。 

 

如何计算：

从网上查看如果获取mysql 的qps,tps 的方法有如下两种：

一、基于 questions  计算qps,基于  com_commit  com_rollback 计算tps

questions = show global status like 'questions';

uptime = show global status like 'uptime';

qps=questions/uptime。另外，可以隔10s分别测试得出(questions2-questions1)/10

 

com_commit = show global status like 'com_commit';

com_rollback = show global status like 'com_rollback';

uptime = show global status like 'uptime';

tps=(com_commit + com_rollback)/uptime

二、基于 com_* 的status 变量计算tps ,qps

使用如下命令：

show global status where variable_name in('com_select','com_insert','com_delete','com_update');

获取间隔1s 的 com_*的值，并作差值运算

del_diff = (int(mystat2['com_delete'])   - int(mystat1['com_delete']) ) / diff

ins_diff = (int(mystat2['com_insert'])    - int(mystat1['com_insert']) ) / diff

sel_diff = (int(mystat2['com_select'])    - int(mystat1['com_select']) ) / diff

upd_diff = (int(mystat2['com_update'])   - int(mystat1['com_update']) ) / diff