来源:网易云课堂《精通MySQL存储过程、函数和触发器》课程
讲师:huangxifeng607(黄锡峰)
笔记
(1)在特定时间按一定频繁收集数据库性能数据
1.存储过程的实现
create procedure monitor_proc()
begin
create table if not exists moniter_processlist as
select now() as ctime, db, info, time_ms, rows_sent, rows_examined
from information_schema.processlist where 0=9;
insert into monitor_processlist select NOW(),
db, info, time_ms, rows_sent, rows_examined from information_schema.processlist
where info is not null;
end;
(2)监控子程序的event设置
1.event事件设置
需求:双12当天9点开枪,9点至11点高峰期,高峰期间每1分钟收集一次监控性能数据。
create every monitor_event
on schedule every 1 minute
starts '2016-12-12 9:00:00' ends '2016-12-12 11:00:00'
do call monitor_proc;
(3)event的维护
1.发现1分钟时间间隔太大了,对性能分析并不合理,需要修改为每10秒收集一次。
如何修改event?
alter event monitor_event
on schedule every 10 second
starts '2017-01-01 9:00:00'
2.收集性能数据后问题已经解决,如何临时性的关闭event,全局关闭event?
alter event monitor_event disable; 只关闭monitor_event这个事件
set global event_scheduler=0; 关闭所有event事件
3.查看和删除event
show events like 'monitor_event'\G;
drop event monitor_event;
(4)小结
1.如何修改event事件?
2.如何关闭event,删除event事件?