MySQL stored procedures + Event statistics MySQL additions and deletions change to QPS

/* 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