对于问题的排查思路都大同小异,一般情况下可以分为下面几个步骤:
- 复现问题:对于一个问题,首先需要明确他的复现条件,这些条件和问题现象在后面的问题排查中非常重要;
- 分析和验证:在这个阶段,通过问题条件和显现,分析可能出现的原因,查看问题日志,再进过猜想验证,基本可以定位问题的原因;
- 定位和解决:问题定位了,后续解决就顺理成章了,但一般问题的解决方案可能不止一个,这个就需要从修复难度、问题常见等各个方面权衡那种解决方案合适了。
- 总结和复盘:最后一个阶段,需要总结从这个问题中学会的知识和技能,让自己的能力有更进一步,同时也要做好记录,总结经验,避免后续再出现这样的问题。
一、慢查询
思路
- 查询慢查询日志;
- 通过expain查询慢查询的sql原因;查询是否进入死锁状态;
- 优化索引;解除死锁;
案例
对于不唯一字段加唯一索引,会引入全表扫描,如果数据比较多,就会形成慢查询。解决方案是对于非唯一字段加普通索引,对唯一字段加唯一索引。
操作
1.修改慢查询的两个关键参数:
slow_query_log:取值为on、off,默认为off关闭。打开慢查询:set global slow_query_log='on'; long_query_time:指定记录慢查询日志的阈值,单位是秒,要指定更细粒度可以用小数表示。
2.通过:show variables like ‘slow_query_log_file’ 查询慢查询日志文件目录,通过在慢日志中看到的日志信息如下:
3.慢日志产生的原因可能是进入死锁状态了,可以通过以下命令查看:show status like ‘innodb_row_lock_%’;
查询结果为:
- Innodb_row_lock_current_waits:当前正在阻塞等待锁的事务数量。
- Innodb_row_lock_time:MySQL启动到现在,所有事务总共阻塞等待的总时长。
- Innodb_row_lock_time_avg:平均每次事务阻塞等待锁时,其平均阻塞时长。
- Innodb_row_lock_time_max:MySQL启动至今,最长的一次阻塞时间。
- Innodb_row_lock_waits:MySQL启动到现在,所有事务总共阻塞等待的总次数。
除了进入死锁外,也有可能是由于索引问题导致SQL本身执行很慢,可以通过explain来排查。
二、死锁
mysql默认会开启死锁检测的功能,带死锁超时之后会自动解锁死锁。但我们再业务开发过程中不能仅依赖mysql提供的死锁检测,需要自己的业务和SQL尽量不要引入死锁才行。碰到死锁的排查思如和操作如下:
思路:
- 通过show status查看加锁线程;
- 找出加锁sql和加锁对象;
- 优化sql;
案例:
在未加索引的事务中,多个事务同时按照同一字段条件进行更新操作,可能会造成死锁,原因是没有索引,在进行更新操作时会加表锁,就会阻塞其他事务的操作,形成死锁。修复的方案是对条件字段加索引,从而形成行锁,提升并发度。
操作
先通过命令判断是否存在死锁:show status like ‘innodb_row_lock_%’;
再查询死锁状态:SHOW ENGINE INNODB STATUS;查看InnoDB存储引擎的运行状态日志。
在死锁状态中可以清除看到哪个SQL导致的死锁,以及如何解开死锁;
对于线上碰到的死锁故障,可以手动回滚死锁事务临时解决,但是为了彻底解决,需要在写SQL的时候考虑避免死锁情况。
三、CPU使用率飙升
思路
- 通难过top命令查看是否是mysqld的进程导致;
- 通过show processlist查看session的连接时间和状态等信息;
- 找到性能最大连接的sql,进行sql和索引优化;
操作
- 通过top命令能找到占用率最高的进程;
- 通过top -Hp [PID]命令查看该进程中CPU占用率最高的线程
- 将OS中的线程id转化为Mysql层面的线程id;
- 通过线程id就能找到正在执行的哪句正在执行的SQL占用CPU最高。
总结
Mysql中CPU飙升的排查思路和JVM中CPU飙升的排查思路差不多。首先还是通过top命令看到哪个进程的占用率最高,再通过命令看该进程下哪个线程的占用率最高,最高在Mysql中能查到OS的线程Id和Mysql的线程Id的对应关系,找到这个关系后,就能找到哪句SQL执行占用的CPU比较高。
四、磁盘IO使用率飙升
思路:
这个一般是突然大批量的对Mysql数据库进行读写,同时Buffer Pool容量不够,导致命中率不高,经常需要去读写磁盘。可以通过iotop、pstack等命令查看IO情况 。
总结:
- 可以优化SQL对数据库的读写操作,可以进行批量查询和修改;
- 可以提升Buffer Pool的容量,提升命中率。
参考资料
(十八)MySQL排查篇:该如何定位并解决线上突发的Bug与疑难杂症?:https://juejin.cn/post/7165767441883398175