The MySQL 5.1 offers new GLOBAL_STATUS information schema tables. These can be used to report certain performance metrics, such as the number of queries processed per certain seconds, NOT overall avg queries per second, Its good to know how much qps in peak hours.

delimiter //
 
DROP PROCEDURE IF EXISTS qps//
 
CREATE PROCEDURE `qps`(timer int)
BEGIN 
 
-- Last Updated 01 oct 2009
-- Mohammad Lahlouh
-- mohammadlahlouh.blogspot.com
 
DROP TEMPORARY TABLE IF EXISTS tmp_status;
CREATE TEMPORARY TABLE tmp_status (Queries int, Uptime int);
 
 
-- Queries vs. Questions to support before and after MySQL 5.1.31
 
INSERT INTO tmp_status
SELECT MAX( IF(variable_name IN ('Queries','Questions') , CAST(variable_value AS UNSIGNED) , 0 )) AS Queries, 
MAX( IF(variable_name='Uptime' , CAST(variable_value AS UNSIGNED) , 0 )) AS Uptime
FROM information_schema.GLOBAL_STATUS
WHERE variable_name IN ('Queries', 'Uptime', 'Questions');
 
SELECT sleep(timer) INTO @x;
 
INSERT INTO tmp_status
SELECT MAX( IF(variable_name IN ('Queries','Questions') , CAST(variable_value AS UNSIGNED) , 0 )) AS Queries, 
MAX( IF(variable_name='Uptime' , CAST(variable_value AS UNSIGNED) , 0 )) AS Uptime
FROM information_schema.GLOBAL_STATUS
WHERE variable_name IN ('Queries', 'Uptime', 'Questions');
 
SELECT (MAX(Queries) - MIN(Queries)) / (MAX(Uptime) - MIN(Uptime)) AS AVG_QPS FROM tmp_status;
 
END//
 
delimiter ;
 

Current Tags

You must be logged in to tag this tool

No Comments yet