mysql数据库故障排查

2023年 9月 12日 23.9k 0

对于问题的排查思路都大同小异,一般情况下可以分为下面几个步骤:

  • 复现问题:对于一个问题,首先需要明确他的复现条件,这些条件和问题现象在后面的问题排查中非常重要;
  • 分析和验证:在这个阶段,通过问题条件和显现,分析可能出现的原因,查看问题日志,再进过猜想验证,基本可以定位问题的原因;
  • 定位和解决:问题定位了,后续解决就顺理成章了,但一般问题的解决方案可能不止一个,这个就需要从修复难度、问题常见等各个方面权衡那种解决方案合适了。
  • 总结和复盘:最后一个阶段,需要总结从这个问题中学会的知识和技能,让自己的能力有更进一步,同时也要做好记录,总结经验,避免后续再出现这样的问题。
  • 一、慢查询

    思路

  • 查询慢查询日志;
  • 通过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与疑难杂症?:juejin.cn/post/716576…

    相关文章

    JavaScript2024新功能:Object.groupBy、正则表达式v标志
    PHP trim 函数对多字节字符的使用和限制
    新函数 json_validate() 、randomizer 类扩展…20 个PHP 8.3 新特性全面解析
    使用HTMX为WordPress增效:如何在不使用复杂框架的情况下增强平台功能
    为React 19做准备:WordPress 6.6用户指南
    如何删除WordPress中的所有评论

    发布评论