模拟MySQL数据库故障

转载

mob64ca140d2323 2024-09-14 14:15:54

文章标签 模拟MySQL数据库故障 mysql 数据库连接数 SQL 文章分类 MySQL 数据库

Mysql故障处理一般流程

一、查看操作系统层面指标

负载
CPU使用率
磁盘空间
IO使用率
SWAP使用情况

二、数据库层面的指标

数据库存活
连接数
慢SQL
主从延迟

一、查看操作系统层面指标

负载

负载是衡量一个服务器整体压力最直观的指标，代表平均有多少进程在等待被CPU调度，可以通过w、uptime、top等命令来获取。

[root@lichy ~]# uptime
 11:01:35 up 53 days,  1:28,  1 user,  load average: 0.08, 0.05, 0.07

load average后面的3个数值，分别代表1 min平均负载、5 min平均负载、15 min平均负载，假设服务器的CPU有n个核，那么所有的CPU都在满负荷运转时，负载就是n，一般来说，健康的系统，负载应该保持在n✖️0.7以下，负载超过n之后就意味着系统开始拥塞。

CPU使用率

CPU使用率可以通过top或者sar等命令获取：

[root@lichy ~]# sar -u -P ALL -C 1 1
Linux 3.10.0-1160.45.1.el7.x86_64 (lichy)       03/13/2022      _x86_64_        (2 CPU)

11:06:33 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
11:06:34 AM     all      0.50      0.00      0.00      0.00      0.00     99.50
11:06:34 AM       0      0.00      0.00      0.00      0.00      0.00    100.00
11:06:34 AM       1      0.00      0.00      0.00      0.00      0.00    100.00

Average:        CPU     %user     %nice   %system   %iowait    %steal     %idle
Average:        all      0.50      0.00      0.00      0.00      0.00     99.50
Average:          0      0.00      0.00      0.00      0.00      0.00    100.00
Average:          1      0.00      0.00      0.00      0.00      0.00    100.00

其中sar后的参数含义分别如下：

-u：统计CPU数据
-P：统计每个核的信息
ALL：展示CPU的所有指标

C：显示注释信息

1 1:每一秒做一次采集，共计采集1次
结果中%idle表示CPU的空闲率，100%减去%idle就是CPU的使用率。如果服务器有多个CPU，那么会分别计算每个CPU的使用率和所有CPU的平均使用率。对于CPU使用率的监控，一般优先考虑监控全部CPU的平均使用率即可。一旦发现全部CPU的平均使用率过高，再进一步分析是消耗在user、system还是iowait上。

磁盘空间

磁盘空间可以通过df -h命令来获取，-h表示结果用最佳可读方式展现。磁盘空间满会直接导致数据库服务不可用，如果是根目录满的话，还会导致登陆服务器困难，进一步影响故障处理速度。因此，建议对磁盘的所有分区都进行监控。

IO使用率

作为IO密集型服务，IO使用率是数据库监控中最重要的指标，IO使用率可以通过iostat命令来获取：

[root@lichy ~]# iostat -xd 2 2
Linux 3.10.0-1160.45.1.el7.x86_64 (lichy)       03/13/2022      _x86_64_        (2 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     1.59    0.01    2.32     0.10    17.21    14.89     0.01    2.45    0.99    2.46   0.65   0.15
scd0              0.00     0.00    0.00    0.00     0.00     0.00   102.08     0.00    0.68    0.68    0.00   0.49   0.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

其中，-xd表示显示扩展信息和设备的利用率信息（%util），“2 2”表示每2s统计一次，共计统计2次。重点关注最后一列%util，当这一列接近100%时，代表磁盘的IO能力已经达到极限。

SWAP使用情况

当系统内存不足时，操作系统会从SWAP分区中分配一部分空间来临时保存一部分原本计划保存在内存中的数据。由于SWAP分区实际上是保存在磁盘上，而磁盘的读写速度远远低于内存，因此，一旦发生SWAP，往往伴随着系统整体性能的大幅度下降。在Linux系统中，可以使用free或者top等命令来检查SWAP的使用情况。

[root@lichy ~]# free -g
              total        used        free      shared  buff/cache   available
Mem:              3           0           1           0           2           3
Swap:             0           0           0

其中，-g参数表示结果以GB为单位。由于数据库的性能对IO依赖比较大，理想的状况下，应该通过合适的设置内存参数，避免系统使用SWAP分区。

二、数据库层面的指标

数据库存活

存活状态是数据库的基本监控。由于Mysql的单进程架构，相对于Oracle等多进程架构的数据库来说，更容易因为某些异常或BUG导致数据库实例崩溃。

连接数

如果数据库出现连接数异常增长，监控需要能够及时发现，因为一旦连接数超过数据库参数中设定的最大连接数，就会出现无法建立连接，影响服务的情况。此外，由于每个连接都要消耗一定的内存，因此数据库总共的连接数应该控制在一个合理的范围内。对于连接数的监控，一般来说可以参考正常状况下的连接数来确定。数据库连接数可以通过information_schema.processlist表来确定：

mysql> select count(1) from information_schema.processlist;
+----------+
| count(1) |
+----------+
|     265  |
+----------+
1 row in set (0.00 sec)

慢SQL

慢SQL是导致数据库问题最常见因素之一，因此需要及时发现数据库中执行效率偏低的SQL。监控慢SQL的方法有很多种，常用的一种方法是开启慢查询日志，然后通过pt-digest工具来定期分析日志，这样可以获得慢SQL的详细信息。也可以简单地通过查询information_schema.processlist来监控当前是否有慢查询SQL存在：

mysql> select * from information_schema.processlist where user not in ('system user','event_scheduler') and Command <> 'Sleep' and Time > 10;
Empty set (0.00 sec)