对MySQL报警的一次分析处理小结
这是学习笔记的第 2334篇文章
最近有一个服务出现了报警,已经让我到了忍无可忍的地步,报警信息如下:
Metric:mysql.innodb_row_lock_waits Tags:port=4306,service=xxxx diff(#1): 996>900
大概的意思是有一个数据库监控指标innodb_row_lock_waits 目前超出了阈值900
但是尴尬的是,每次报警后去环境中查看,得到的信息都很有限,慢日志,错误日志里面都没有充分的信息可以分析,一来二去之后,我开始静下心来分析这个问题的原因。
首先这个报警信息的时间点貌似是有些规律的,我拿着最近几天的报警时间做了比对,发现还是比较有规律的,那么在系统层面有哪些任务可能会触发呢,我查找比对了相关的任务配置,发现有一个定时任务每1分钟会执行一次,但是到了这里疑问就来了,如果每1分钟执行1次,为什么在特定的时间会产生差异较大的处理结果?当然这个现象的解释是个起始。
其实要证明这一点还是蛮容易的,今天我就采取了守株待兔的模式,我在临近报警的时间前后打开了通用日志,从日志输出来看,操作的频率还是相对有限的。
很快得到了规律性的报警,于是我开始抓取相关的通用日志记录,比如11:18分,我们可以采用如下的模式得到相关的日志,首先得到一个临时的通用日志文件,把各种DML和执行操作都网罗进来。
cat general.log|grep -E "insert|delete|update|select|exec" > general_tmp.log
我们以11:18分为例,可以在前后1两分钟做比对,结果如下:
# less general_tmp.log |grep "11:18"|wc -l
400
# less general_tmp.log |grep "11:17"|wc -l
666
# less general_tmp.log |grep "11:16"|wc -l
15
发现在报警的那1分钟前后,数量是能够对得上的。
这个表的数据量有200多万,表结构如下:CREATE TABLE `task_queue` ( `AccID` bigint(20) NOT AUTO_INCREMENT COMMENT "自增ID", `TaskStepID` bigint(20) DEFAULT COMMENT "任务步骤ID task_step_conf", `QOrder` int(11) DEFAULT COMMENT "队列排序 task_step_confi.Step_ID", `QState` tinyint DEFAULT "1" COMMENT "队列状态 1:待执行 2:执行中 3:执行成功 4:执行失败", `QExcCount` int(11) DEFAULT "1" COMMENT "执行次数", `CrtTime` datetime DEFAULT COMMENT "创建时间", `ModTime` datetime DEFAULT COMMENT "修改时间", PRIMARY KEY (`AccID`), KEY `idx_taskstepid` (`TaskStepID`), KEY `idx_qstate` (`QState`)) ENGINE=InnoDB AUTO_INCREMENT=3398341 DEFAULT CHARSET=utf8
在日志中根据分析和比对,基本能够锁定SQL是在一类Update操作上面,SQL的执行计划如下: >>explain update task_queue set QState=1,QExcCount=QExcCount+1,modtime=now where QState=0 and taskstepid =411G*************************** 1. row *************************** id: select_type: UPDATE table: task_queue partitions: type: index_mergepossible_keys: idx_taskstepid,idx_qstate key: idx_qstate,idx_taskstepid key_len: 2,9 ref: rows: 11 filtered: 100.00 Extra: Using intersect(idx_qstate,idx_taskstepid); Using where; Using temporary
这个执行结果中key_len是2,9,是和以往的ken_len计算法则不一样的。 其中Extra列已经给出了明确的提示,这是一个intersect处理,特别的是它是基于二级索引级别的处理,在优化器层面是有一个相关的参数index_merge_intersection。
我们知道在MySQL中主键是一等公民,而二级索引最后都会映射到主键层面处理,而索引级别的intersect其实有点我们的左右手,左手对应一些数据结果映射到一批主键id,右手对应一些数据结果映射到另外一批主键id,把两者的主键id值进行intersect交集计算,所以在当前的场景中,索引级别的intersect到底好不好呢?
在此我设想了3个对比场景进行分析,首先这是一个update语句,我们为了保证后续测试的可重复性,可以转换为一个select语句。
select * from task_queue where QState=0 and taskstepid =411;
所以我们的对比测试基于查询语句进行比对分析。
场景1:优化器保持默认index_merge_intersection开启,基于profile提取执行明细信息>explain select * from task_queue where QState=0 and taskstepid =411G*************************** 1. row *************************** id: select_type: SIMPLE table: task_queue partitions: type: index_mergepossible_keys: idx_qstate,idx_taskstepid key: idx_qstate,idx_taskstepid key_len: 2,9 ref: rows: 11 filtered: 100.00 Extra: Using intersect(idx_qstate,idx_taskstepid); Using where row in set, 1 warning (0.00 sec)
profile信息为:
场景2:优化器关闭index_merge_intersection,基于profile进行对比>set session optimizer_switch="index_merge_intersection=off";
>explain select * from task_queue where QState=0 and taskstepid =411G*************************** 1. row *************************** id: select_type: SIMPLE table: task_queue partitions: type: refpossible_keys: idx_qstate,idx_taskstepid key: idx_qstate key_len: ref: const rows: 1451 filtered: 0.82 Extra: Using where row in set, 1 warning (0.00 sec)
profile信息为:
场景3:重构索引,进行比对分析
根据业务逻辑,如果创建一个复合索引,是能够大大减少结果集的量级的,同时依然保留idx_qstate索引,使得一些业务依然能够正常使用。 >alter table task_queue drop key idx_taskstepid;>alter table task_queue add key `idx_taskstepid` (`TaskStepID`,QState);explain select * from task_queue where QState=0 and taskstepid =411G*************************** 1. row *************************** id: select_type: SIMPLE table: task_queue partitions: type: refpossible_keys: idx_qstate,idx_taskstepid key: idx_taskstepid key_len: 11 ref: const,const rows: filtered: 100.00 Extra: row in set, 1 warning (0.00 sec)
profile信息为:
可以明显看到通过索引重构,"Sending data"的部分少了两个数量级
所以接下里的事情就是进一步进行分析和验证,有理有据,等待的过程也不再彷徨,一天过去了,再没有收到1条报警,再次说明在工作中不要小看这些报警。
各大平台都可以找到我
微信公众号:杨建荣的学习笔记
Github:@jeanron100
CSDN:@jeanron100
知乎:@jeanron100
头条号:@杨建荣的学习笔记
网易号:@杨建荣的数据库笔记
大鱼号:@杨建荣的数据库笔记
腾讯云+社区:@杨建荣的学习笔记
孩子不听话?杨幂郎朗陈宥维都爱这个方法,家长老师快来学学孩子不听话怎么办?这个问题看起来很笼统,但确实有很多父母和老师,会遇到孩子不合作的情况,比如顶嘴发脾气对着干不服管教等。虽说,每个孩子犯错的原因和情景都不同,但孩子不听话,有没有一
张绍刚说胡扯,刘璇为啥让儿子坚持?小学生问题多,别小看日程表一张日程表引争议在新生日记中,张绍刚来到刘璇家做客,刘璇的老公王弢对于她的军事化育儿,很有意见,所以请张绍刚来给评评理。当张绍刚看到墙上的日程表后,哑口无言,组织了半天语言,来了句
看图写话不会写?这份攻略从起步到高阶,帮孩子插上作文的翅膀孩子从一年级下半学期开始,就会接触看图写话,这个语文作文的起步题型。看图写话是孩子写作文的基础,家长一定要重视起来,抓住关键期,培养孩子的语文思维,对未来写作文可以说是事半功倍。一
镇江一日行游金山公园西津渡有感镇江,在昨天之前,仅存在我遥远的记忆里,缄默了很久的她,也曾给过我几个模糊的梦,但都是小时候的事了。这几年,镇江变成我从外地回乡必经的中转站,在站台短暂地停留过数次,但急于往家赶,
我的课堂教学故事之学生当老师解放学生的眼睛双手头脑嘴空间时间,把课堂时间还给学生,把创造还给学生。让学生在宽松自由的环境中,顺着他们作为儿童的天性,采取适合的教育教学方式,自自然然地引导,他们才会感到自由自在
响鼓还需重锤前段时间,收到一份请柬及一盒精美的糖果糕点。我很高兴,同事羡慕的同时夸赞学生懂事小学教师收到学生的结婚喜帖,很少见。这位学生,大学毕业后,在深圳发展得很好。国庆假期回娘家办回门宴席
我的课堂教学故事之学生是辩手?诗人?(一)学生是辩手课文船长中有句话船长说哪个男人敢走在女人前面,你就开枪打死他!在预习情况反馈时,有学生提出奥克勒大副真的会开枪吗?话音刚落,学生们便议论开来,有的说会,有的说不会。
代号大鹏展翅之反思和分析现代信息技术的介入,使我们的课堂教学,师生交流都发生了变革。不仅广辟了师生沟通的渠道,拓宽了的视野,还悄悄地变革着师生的思维方式交流方式,让我们走近彼此,找到了共同的努力方向。沟通
尽显爱与自由的芬芳综合实践活动后的思考四月上旬的这三天,对我校五年级学生来说是难忘的,对我们教师来说,也是颇有感触的。在综合实践活动基地与学生朝夕相处的三天,我脑海里留下一个又一个难忘的场景。(一)看,美丽的丝网花在手
代号大鹏展翅之大鹏试飞现代信息技术,让师生沟通无极限现代信息技术的介入,使我们的课堂教学,师生交流都发生了变革。微课电子白板E学习等等原以为离我们很远,但在这一两年里像一股飓风,裹挟着翻转课堂等一些新名词及一股不可抗拒的力量,落户在
花苞心态,才是真爱每当课间站在阳台上或上下班路过校园假山喷泉池,看到静静绽放的莲花,我脑海中便浮现出一幅荷叶田田,荷花娇妍的美景。耳畔不由自主地响起南京市行知小学杨瑞清校长绘声绘色的讲演。特别是那句