关于MySQL的TPS和QPS

最新推荐文章于 2024-07-01 09:14:46 发布

转载最新推荐文章于 2024-07-01 09:14:46 发布 · 1.3k 阅读

mysql 专栏收录该内容

78 篇文章

订阅专栏

本文详细介绍了服务器性能指标TPS（每秒处理事务数）和QPS（每秒查询处理量）的概念及计算方法。TPS适用于支持事务的存储引擎如InnoDB，通过计算COM_COMMIT和COM_ROLLBACK来得出；QPS则同时适用于InnoDB和MyISAM引擎，通过计算QUESTIONS和UPTIME得出。

TPS - Transactions Per Second（每秒传输的事物处理个数），这是指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB等特有的一个性能指标。

计算方法：

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（每秒查询处理量）同时适用与InnoDB和MyISAM 引擎

计算方法：

QPS=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;

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

show status like 'uptime';

http://blog.youkuaiyun.com/JeffreyNicole/article/details/47451043