/* Statistics MySQL additions and changes to the QPS SQL script */;
DROP TABLE IF EXISTS qpsinfo;
CREATE TABLE Qpsinfo (
ID INT PRIMARY KEY auto_increment,
' Type ' INT COMMENT "1 insert,2 delete,3 update,4 Select",
' Count ' INT COMMENT "total number of updates",
' QPS ' INT COMMENT "number of updates for 10 seconds",
Create_time DATETIME DEFAULT Now ()
);
/* Initialize table data */;
Insert into Qpsinfo (' type ', ' count ', ' QPS ') of values (1, (SELECT variable_value from INFORMATION_SCHEMA. Global_status where variable_name = "Com_insert"), 0);
Insert into Qpsinfo (' type ', ' count ', ' QPS ') of Values (2, (SELECT variable_value from INFORMATION_SCHEMA. Global_status where variable_name = "Com_delete"), 0);
Insert into Qpsinfo (' type ', ' count ', ' QPS ') of Values (3, (SELECT variable_value from INFORMATION_SCHEMA. Global_status where variable_name = "com_update"), 0);
Insert into Qpsinfo (' type ', ' count ', ' QPS ') of Values (4, (SELECT variable_value from INFORMATION_SCHEMA. Global_status where variable_name = "Com_select"), 0);
/* Create stored procedure statistics adding or deleting qps*/;
DROP PROCEDURE IF EXISTS qpscount;
DELIMITER//
CREATE PROCEDURE Qpscount ()
BEGIN
DECLARE _count INT DEFAULT 0;
DECLARE _old_count INT DEFAULT 0;
DECLARE _num INT DEFAULT 0;
SELECT Variable_value to _count from INFORMATION_SCHEMA. Global_status WHERE variable_name = "Com_insert";
SELECT ' count ' into _old_count from Qpsinfo WHERE ' type ' = 1 ORDER by id DESC LIMIT 1;
SET _num = (_count-_old_count)/10;
INSERT into Qpsinfo (' type ', ' count ', ' QPS ') VALUES (1,_count,_num);
SELECT Variable_value to _count from INFORMATION_SCHEMA. Global_status WHERE variable_name = "Com_delete";
SELECT ' count ' into _old_count from Qpsinfo WHERE ' type ' = 2 ORDER by id DESC LIMIT 1;
SET _num = (_count-_old_count)/10;
INSERT into Qpsinfo (' type ', ' count ', ' QPS ') VALUES (2,_count,_num);
SELECT Variable_value to _count from INFORMATION_SCHEMA. Global_status WHERE variable_name = "Com_update";
SELECT ' count ' into _old_count from Qpsinfo WHERE ' type ' = 3 ORDER by id DESC LIMIT 1;
SET _num = (_count-_old_count)/10;
INSERT into Qpsinfo (' type ', ' count ', ' QPS ') VALUES (3,_count,_num);
SELECT Variable_value to _count from INFORMATION_SCHEMA. Global_status WHERE variable_name = "Com_select";
SELECT ' count ' into _old_count from Qpsinfo WHERE ' type ' = 4 ORDER by id DESC LIMIT 1;
SET _num = (_count-_old_count)/10;
INSERT into Qpsinfo (' type ', ' count ', ' QPS ') VALUES (4,_count,_num);
END//
DELIMITER;
/* Create events to allow MySQL to run on time */;
DROP event IF EXISTS ' Qpscount ';
CREATE EVENT ' Qpscount ' on SCHEDULE every SECOND on completion not PRESERVE ENABLE does call Qpscount ();
This article is from the "Jeff" blog, so be sure to keep this source http://zhangxz.blog.51cto.com/5490116/1610714
MySQL stored procedures + Event statistics MySQL additions and deletions change to QPS