TPS – Transactions PerSecond(每秒传输的事物处理个数),即服务器每秒处理的事务数,如果是InnoDB会显示,没有InnoDB就不会显示。其他支持事务的存储引擎也可以。
方法一:利用mysql的系统统计表information_schema.global_status。其实多看看mysql自带的information_schema库下的表。基本能解决平时工作中的问题。
TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME USE information_schema; SELECT variable_value INTO @num_com FROM global_status WHERE variable_name ='COM_COMMIT’; SELECT variable_value INTO @num_roll FROM global_status WHERE variable_name = 'COM_ROLLBACK’; SELECT variable_value INTO @uptime FROM global_status WHERE variable_name =‘UPTIME’; SELECT ( @num_com + @num_roll ) / @uptime; QPS – Queries Per Second(每秒查询处理量)MyISAM引擎 QUESTIONS/UPTIME USE information_schema; SELECT variable_value INTO @num_queries FROM global_status WHERE variable_name = 'QUESTIONS’; SELECT variable_value INTO @uptime FROM global_status WHERE variable_name = ‘UPTIME’; SELECT @num_queries / @uptime; #方法二:利用mysql的全局状态值。 MySQL的QPS计算 show global status where Variable_name in(‘com_select’,’com_insert’,’com_delete’,’com_update’); sleep(10) show global status where Variable_name in(‘com_select’,’com_insert’,’com_delete’,’com_update’); QPS:$stat_val{“Com_select”} +$stat_val{“Com_insert”} + $stat_val{“Com_update”} + $stat_val{“Com_delete”}, #方法二 #同理也是利用mysql的全局status值进行计算。 Questions = SHOW GLOBAL STATUS LIKE ‘Questions’; Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’; QPS=Questions/Uptime TPS 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