mysql qps提升_MySQL-QPS&TPS计算

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