mysql存储过程+事件统计mysql增删改查qps-Jeff
/*统计mysql增删改查 qps 的sql脚本*/;DROP TABLE IF EXISTSqpsInfo ;
CREATE TABLE qpsInfo(
id INT PRIMARY KEY AUTO_INCREMENT,
`type` INT COMMENT "1 insert，2 delete，3 update，4 select",
`count` INT COMMENT "更新的总数",
`qps` INT COMMENT "10秒钟更新的数量",
create_time DATETIMEDEFAULT NOW()
);
/*初始化表数据*/;
insert into qpsInfo(`type`,`count`,`qps`) values (1,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_insert"),0);
insert into qpsInfo(`type`,`count`,`qps`) values (2,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_delete"),0);
insert into qpsInfo(`type`,`count`,`qps`) values (3,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_update"),0);
insert into qpsInfo(`type`,`count`,`qps`) values (4,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_select"),0);
/*创建存储过程统计增删改查qps*/;
DROP PROCEDUREIF 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_valueINTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_insert";
SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 1 ORDER BY> SET _num = (_count - _old_count) / 10;
INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (1,_count,_num);
SELECT variable_valueINTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_delete";
SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 2 ORDER BY> SET _num = (_count - _old_count) / 10;
INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (2,_count,_num);
SELECT variable_valueINTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_update";
SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 3 ORDER BY> SET _num = (_count - _old_count) / 10;
INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (3,_count,_num);
SELECT variable_valueINTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_select";
SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 4 ORDER BY> SET _num = (_count - _old_count) / 10;
INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (4,_count,_num);
END //
DELIMITER ;
/*创建事件，让mysql定时运行*/;
DROP event IF EXISTS `qpsCount`;
CREATE EVENT `qpsCount` ON SCHEDULE EVERY 10 SECOND ON COMPLETION NOT PRESERVE ENABLE DO CALL qpsCount();
页:
[1]