简介
MySQL 的故障诊断，需要对整个系统的架构、硬件、软件都有比较深入的了解，由于数据库服务的特点，一旦出现问题，可能影响整个系统的可用性,需要在最短的时间内解决。
一般处理流程
按照发现→定位→解决的顺序，对故障处理的一般流程和关键点做一下简单介绍。
故障发现
发现依赖于监控，对于数据库服务,一定要构建一套完整的监控系统,在出现问题的时候，能够通过邮件、短信、电话等形式将报警信息发送给DBA和其他相关人员，比故障更可怕的是,出现了故障还没人知道。数据库监控一般应该包括以下几个指标。
操作系统指标
负载
负载是衡量服务器整体压力最直观的指标，代表平均有多少进程在等待被 CPU 调度，可以通过 W 、uptime 、top 等命令来获取。
[root@GreatSQL ~]# uptime
02:57:29 up 3 days, 15:51, 2 users, load average: 0.30, 0.08, 0.07
load average后面的3个数值，分别代表 1 min 平均负载、5 min 平均负载、15 min 平均负载。一般来说，应该保持负载来 0.7 以下，满载的之后意味着系统开始拥塞。
负载报警的阈值，应该根据系统正常状态下的负载来定。当负载显著升高时，就应该及时排查原因，防患于未然。
CPU 使用率
CPU 使用率可以通过 top 或者 sar 等命令获取：
top - 02:58:39 up 3 days, 15:52, 2 users, load average: 0.09, 0.06, 0.06
Tasks: 79 total, 3 running, 76 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.7 us, 2.7 sy, 0.0 ni, 95.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
如果发现 CPU 使用率太高，可以进一步分析到底是消耗在 user 、 system 还是 iowait 上。
磁盘空间
磁盘空间可以通过 df -h 命令来获取， -h 表示结果用最佳可读方式展现。磁盘空间满会直接导致数据库服务不可用，如果是根目录满，还会导致服务器登录困难。
IO 使用率
作为 IO 密集型服务， IO使用率是数据库监控中的重要指标，IO 使用率可以通过 iostat 命令来获取：
yum install sysstat
[root@GreatSQL ~]# iostat -xd
Linux 3.10.0-1160.76.1.el7.x86_64 (GreatSQL) 2022年09月17日 _x86_64_ (1 CPU)
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
vda 0.12 1.27 0.90 0.54 27.64 10.37 53.09
avgqu-sz await r_await w_await svctm %util
0.00 2.96 2.73 3.33 0.67 0.10
-c: 输出cpu统计信息
-d: 输出磁盘统计信息 注：默认是两个都输出
-k|-m: 以kb/s|mb/s代替原来的块/s
-t: 输出时打印收集信息时刻的时间 注：时间的打印格式和系统变量S_TIME_FORMAT相关
-x: 输出详细的拓展统计数据，比如各种等待时间，队列，利用率等信息。
interval [count] :interval是统计的时间间隔单位是s，count则是统计次数
%util：在统计时间内所有处理IO时间，除以总共统计时间。表示该设备的繁忙程度。例如，如果统计间隔1秒，该设备有0.5秒在处理IO，而0.5秒闲置，则该设备的 %util = 0.5/1 = 50%。一般地，如果该参数是100%表示设备已经接近满负荷运行。需要从 IO 层面关注故障原因。
SWAP 情况
当系统内存不足时，操作系统会借用磁盘来保存内存中的数据。由于磁盘速度远低于内存，所以一旦发生 SWAP，往往伴随着操作系统整体性能大幅下降。可以使用 free 或 top 来检查SWAP 使用情况：
[root@GreatSQL ~]# free -m
total used free shared buff/cache available
Mem: 486 322 25 18 139 133
Swap: 1022 469 553
默认是以KB为单位，-m 是以MB为单位，-g是以GB为单位。由于数据库对 IO 的依赖比较大，应该尽量避免系统使用SWAP分区。
数据库层面指标
数据库存活
存活状态是数据库的基本监控。由于 MySQL 的单进程架构，相对于 Oracle 等多进程架构来说，更容易因为某些异常或者 BUG 导致数据库实例奔溃。对于数据库存活的监控，一般是通过尝试建立数据库连接来检查。
连接数
如果数据库连接数出现异常增长，监控需要能及时发现。因为一旦超过最大连接，就会无法建立连接，影响服务。由于每个连接都要消耗一定的内存，因此数据库的总连接数也应该控制在一个合理的范围内。数据库连接数可以通过 information_schema.processlist 表来确定：
mysql> select count(*) from information_schema.processlist;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.04 sec)
慢 SQL
慢 SQL 是导致数据库问题最常见原因之一，因此需要及时发现数据库中执行效率偏低的 SQL。监控慢 SQL 的方法有很多种，最常用的是开启慢查询日志，然后通过 pt-digest 工具来定期分析日志，这样子就可以获得慢查询 SQL 的详细信息。也可以简单的通过查询 information_schema.processlist 来监控是否有慢查询的存在：
select * from information_schema.processlist
where user not in ('system user','event_scheduler')
and Command <> 'sleep' and Time > 10;
主从延迟
从库作为主库备份，如果延迟过大，一旦主库出现问题，那么从库将会无法及时顶替主库提供服务。此外，从库会分担一部分主库的查询请求，如果延迟过大，那么发送到从库的查询请求也无法提供正确的结果。可以通过 show slave status 命令来检查主从延迟情况。
故障定位
检查当前和最近的操作
如果DBA、开发正在做某项操作，那么由该操作导致故障的可能性比较大，所以要首先确认当前所有相关操作。常见导致数据库故障的操作有以下几种。
- 程序发布：每次应用程序发布都有可能引发数据库问题，例如新业务逻辑带来的 SQL 变动、新上线的促销带来访问量的增加、发布的时候批量创建的连接带来的连接冲击等。
- 在线表变更：造成报警的常见原因包括变更造成的锁表、复制表过程中带来的 IO 压力等。
- 在线数据修改：照成报警的常见原因是大批量数据修改带来的压力，或者执行 SQL 不合理导致的锁问题。
- 后台任务、数据统计：后台任务和数据统计往往需要查询或者更新大量数据，导致操作系统 CPU 或 IO 使用率快速升高而报警。
- 数据库参数调整：在线数据库参数调整也有可能造成意外的影响。
- 其他误操作：各种人为的操作也是报警的常见原因。
操作系统层检查
接下来可以对操作系统和数据库的各项指标进行检查，进一步帮助自己定位故障。
在操作系统层，一般从下面几个角度进行排查。
-
系统进程：通过进程占用的 CPU 和内存情况，判断当前系统压力是否是由 MySQL 进程导致。检查是否存在其他的异常服务、是否有定时任务在运行等。
-
CPU：检查当前CPU的使用情况，并通过CPU主要等待时间消耗在哪里，来判断到底是 IO 能力不足,还是运算能力不足。
-
内存、SWAP：检查内存和 SWAP 的使用情况，来判断故障是否是由内存分配不当造成的。
-
IO：检查磁盘的 IO 吞吐量和IO使用率是否正常，可以辅助判断当前系统是否存在 IO 能力不足的问题,以及问题到底是由于 IO 吞吐量下降造成的,还是由于 IO 量过高造成的。
-
系统日志：从 dmsg、message、secure等系统日志中，可以辅助判断当前系统是否存在硬件故障、了解故障前系统都做过哪些操作等。
数据库层检查
- 连接：通过检查数据库当前的session，以及活跃状态的session，DBA可以了解到当前系统的连接数、连接的使用状况是否正常。
- 慢查询：通过慢查询分析，可以最直观地了解到，是否有大量消耗系统资源的SQL,从而判断问题是否由SQL导致,由哪些SQL导致。
- 锁等待：锁等待也是常见的导致连接问题或性能问题的原因，需要通过检查数据库中的锁持有和等待的情况、锁持续的时间、被阻塞的连接数量等指标来判断问题。
- QPS：通过检查数据库当前的QPS,可以帮助判断数据库的访问量是否正常，从而判断是否由于业务量波动或者缓存失效等原因导致数据库的请求量异常。
- 错误日志：部分实例错误的信息，需要通过MySQL错误日志来判断，虽然当前版本中 MySQL错误日志提供的信息还不够充分。未来随着MySQL版本的更新，错误日志也会越来越完善。
常见故障图示：
故障解决
通过定位，可以比较明确的确定导致故障的原因，针对不同的原因，需要不同的方法来解决。下面分析一些常见故障的解决方法。
1.慢 SQL
通过分析,如果最终确定故障是由于一个或多个 SQL 执行慢导致的,那解决故障就是优化 SQL 的执行效率。常见的导致慢 SQL 的原因及解决方案有以下几种。
选择条件上没有索引或者索引效率低。
如果表的数据量不太大,而 SQL 上也存在着选择度比较好的条件时,可以选择直接为SQL创建一个索引来解决故障。而如果表的数据量非常大,创建索引需要花的时间和资源也很大，这时可以先和其他团队沟通一下SQL涉及的具体业务逻辑，对于非核心业务，或许可以临时停掉这个SQL,这样可以快速恢复核心业务，并给DBA留下创建索引的时间窗口。对于核心业务的SQL,如果是由于SOL变更导致,可以考虑回滚应用程序版本。
此外,如果涉及的表上数据只有新增，没有修改，并且对于查询结果的准确性要求不高,可以通过创建一个空表,并和大表通过rename临时互换的方式，来加快服务恢复的速度。接下来，再跟产品和开发团队沟通SQL的具体需求，讨论是否可以通过其他高效率的方式来查询数据库，比如增加其他有索引的列作为查询条件等，从而更好地解决问题。
有索引,但没有用到索引,或者选择了错误的索引。
这种情况往往是由于表的统计信息不准确，或者 SQL 过于复杂导致,当然也不排除 MySQL 的 SQL 优化器有时候也会犯错误。这时还是需要优先恢复核心服务,然后通过收集统计信息,或者使用 SQL 改写插件来强制 SQL 走正确索引。如果故障影响可控,也可以选择等待应用程序修改 SQL 写法等方式来尝试让 SQL 的执行计划符合预期。
2.SQL 执行频率高
恶意攻击
大多数数据库都是存放在内网环境，因此直接受到恶意攻击的情况不多，但是针对 Web 和应用服务器的攻击，还是会通过 SQL 请求的方式传递到数据库。应对这种情况仅靠数据库层不容易处理，可以及时发现并反馈给应用开发和运维团队来解决，例如自动封禁掉异常访问的 IP 等。
缓存失效
如果应用层缓存设计不合理，导致有过多的请求穿透缓存到达数据库，那么也需要及时和应用团队沟通,改进缓存策略、加大缓存容量。
应用实现逻辑不合理
可以通过回滚等方式临时解决。此外，作为DBA，应该对自己负责业务线的关键业务逻辑有所了解，这样可以配合产品和开发团队一起，对应用逻辑做出合理的设计和调整。
业务量突增
这种情况相对比较棘手，可以首先考虑通过对部分重点 SQL 进行优化的方式，从一定程度上缓解压力，争取能够扛过业务峰值。之后再考虑对整个业务逻辑进行优化、数据库拆分或者对数据库集群资源进行扩容等长期方案。在紧急情况下，也可以放弃部分边缘业务，来保障核心业务的运行。
3.锁冲突
对于锁冲突带来的连接数过高的问题，一般来说可以通过临时加大最大连接数，或者手动杀掉一部分连接的方法，避免连接数达到限制，无法创建新连接的情况发生。同时应该联系开发和运维，从应用层限制锁冲突严重的接口的并发量。待服务稳定后，再根据导致锁冲突的不同原因，调整程序逻辑。下面分别是两种导致锁冲突的常见原因的解决思路。
- 大事务:通过优化逻辑，尽可能地拆分事务，调整事务内的 SQL 顺序，减少持有锁的时间。
- 热点问题：可以通过分散热点、减小事务、串行化等方式来缓解热点带来的锁冲突。
4.硬件问题
硬件问题涉及的可能性比较广，对于数据库这种 IO 密集型服务来说，更多地需要从 IO 层面来关注可能的硬件问题。
- RAID卡缓存问题:RAID 卡缓存设置不合理，或者电池充放电，都有可能导致 IO性能的波动.
- 硬盘损坏也是常见的造成 IO 性能下降的原因，需要及时更换损坏的硬盘，并且在 RAID 重建期间密切关注服务器的 IO 压力。
5.参数配置不合理
如果故障是由于操作系统或数据库参数设置不合理所导致，那么在尝试调整参数来解决故障的时候，一定要很清楚自己所做的调整可能带来的影响。对于不常操作的参数，建议先查阅文档，再通过搜索引擎了解一下其他人的处理经验，避免因为错误的参数调整，带来更多的问题。