对于问题的排查思路都大同小异,一般情况下可以分为下面几个步骤:
一、慢查询
思路
案例
对于不唯一字段加唯一索引,会引入全表扫描,如果数据比较多,就会形成慢查询。解决方案是对于非唯一字段加普通索引,对唯一字段加唯一索引。
操作
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 like ‘innodb_row_lock_%’;
再查询死锁状态:SHOW ENGINE INNODB STATUS;查看InnoDB存储引擎的运行状态日志。
在死锁状态中可以清除看到哪个SQL导致的死锁,以及如何解开死锁;
对于线上碰到的死锁故障,可以手动回滚死锁事务临时解决,但是为了彻底解决,需要在写SQL的时候考虑避免死锁情况。
三、CPU使用率飙升
思路
操作
总结
Mysql中CPU飙升的排查思路和JVM中CPU飙升的排查思路差不多。首先还是通过top命令看到哪个进程的占用率最高,再通过命令看该进程下哪个线程的占用率最高,最高在Mysql中能查到OS的线程Id和Mysql的线程Id的对应关系,找到这个关系后,就能找到哪句SQL执行占用的CPU比较高。
四、磁盘IO使用率飙升
思路:
这个一般是突然大批量的对Mysql数据库进行读写,同时Buffer Pool容量不够,导致命中率不高,经常需要去读写磁盘。可以通过iotop、pstack等命令查看IO情况 。
总结:
参考资料
(十八)MySQL排查篇:该如何定位并解决线上突发的Bug与疑难杂症?:juejin.cn/post/716576…