mysql常见故障诊断

向右看齐 2021-12-16 11:11 369阅读 0赞

作为故障预警，应该尽量把问题扼杀在摇篮中，当出现问题及时处理，否则等到服务器宕机，再去”救火“工作，往往不能第一时间准确诊断。所以应建立监控流程，能够提前预警阀值，马上查看问题，降低风险。

# 影响性能的因素 #

*  磁盘I/O
    
    影响mysql innodb引擎性能最主要因素是磁盘I/O。最广泛使用的硬盘都是[机械硬盘][Link 1]，机械硬盘运作时，磁盘需进行寻址，随机读写速度大大你玩顺序读写。每次读写动作时，磁道可能存在较大的偏移，磁盘寻址时间的延迟，使I/O性能急剧下降。因此生产环境中，常常使用RAID5、RAID10磁盘阵列并行读写，或者采用[SSD固态硬盘][SSD]，大幅提升读写速度。
 *  内存
    
    Innodb引擎会在内存中创建缓存池innodb buffer pool，将数据和索引放在内存中读写，使用数据库性能非常高。因此innodb buffer pool的大小直接影响着mysql的运行性能，生产环境中，会将innodb buffer pool size调整为主机内存的80%左右。
 *  慢SQL
    
    mysql优化器并不会优化所有SQL，SQL不能合理的利用到索引，或者大量使用聚合运算，往往也是造成故障的元凶。

# 系统性能评估 #

## 影响系统性能的因素 ##

*  CPU性能
 *  内存性能及大小
 *  磁盘I/O

## 系统性能评估指标 ##

CPU性能指标  
cpu使用率  
cpu load队列负载  
cpu idle空闲度  
cpu wait I/O等待  
内存性能指标  
占用内存  
资源缓存  
文件缓冲  
空闲内存  
虚拟交换内存  
磁盘性能指标  
磁盘I/O等待时间  
I/O队列，队列过高，说明磁盘处理能力存在瓶颈  
平均等待时间  
每秒读写速度

## 性能监控工具 ##

linux下有很多知名的性能查看工具。多数工具都是通过读取系统目录/proc下的各种文件采集性能的。

我经常用的工具有top，sar，dstat，iostat，mpstat等。其中dstat功能丰富，支持查看CPU，内存，磁盘，交换内存，网络，TCP，mysql等指标，是一个全能型的性能查看工具。

dstat工具使用：

# dstat [option] [interval间隔] [count次数]
        -c: 查看cpu的使用情况     -m: 查看内存情况     -d: 查看磁盘IO读写情况     -n: 网络出入速度     -l: cpu负载     -t: 查看TCP连接统计     -s: 虚拟交换内存的使用情况     -p: 查看进程运行情况

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9

sar工具，可以查看一天中各时间段的性能统计信息。

# sar [option] [interval间隔] [count次数]
        -A: 查看所有信息汇总     -q: cpu load负载     -p: cpu使用情况     -r: 内存使用情况     -d: 查看磁盘IO读写情况     -n: 网络流量情况

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7

# 故障与处理 #

## 连接数过多 ##

连接数过多有多种原因，存在慢SQL造成后续的SQL阻塞，磁盘I/O，CPU繁忙，导致数据库死机。

1.  最直接的方法是，可以增加max\_connections的大小，提高数据库最大连接数，但维持会话连接是需要占用内存的，连接数太多，占用大师内存，也是治标不治本。
2.  Mysql数据库在处理完一条SQL后，会自动关闭空闲的会话连接，空闲会话关闭时间，取决于参数wait\_timeout的数值（单位:秒），尽量不要调得太大，造成资源浪费。根据生产环境实际情况，适当调整为100-300秒。
3.  如果在业务访问量非常大，但有时因故障需要重启的情况。重启后，内存缓存会被释放掉，这样大量用户访问时，首次查询数据因没有缓存，会比正常情况慢很多，短期内会有一个性能高峰，甚至导致连接数爆满。那么如何解决这个问题？是否可以让mysql自动加载数据到内存？Mysql innodb提供了一个预热功能，提前加载数据到缓存中。my.cnf配置以下参数：

# 在关机时导出内存缓存到磁盘
    innodb_buffer_pool_dump_at_shutdown = 1
    # 在启动时加载磁盘缓存到内存中
    innodb_buffer_pool_dump_at_startup = 1
    # 手动导出缓存到磁盘
    innodb_buffer_pool_dump_now = 1
    # 手动加载缓存到内存
    innodb_buffer_pool_load_now = 1

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8

1.  在使用Innodb引擎时，同时也要保证innodb\_buffer\_pool\_size大小充足，innodb\_buffer\_pool\_size/data\_size的比值越大越好，如果过小，会增加磁盘I/O的压力，降低SQL的执行效率，从而连接数过多。

## 慢SQL ##

慢SQL会占用大量系统资源，比如大量使用子查询，聚合函数。具体将在后续详细说明性能优化。

## 事务SQL量的突增 ##

有时候因业务量的突然上涨，引起的某事务，某SQL的执行次数增多，造成数据库压力过大。此时就需要通过查看日志分析突增的SQL，给开发作以优化。

## 虚拟交换内存不足 ##

合理增加swap内存的大小，以免内存用尽时死机。

对内存进行监控，能够的交易预警，作出处理。

## ibdata数据文件误删除 ##

因操作失误误删数据文件，此时千万不能关掉mysqld进程，进程杀死，内存缓存，fd文件被释放，无法进行恢复。

1.  找到Mysql进程pid
2.  进入目录/proc/pid/fd内
3.  ls -l |grep ib\_，会看到几个fd的链接文件，也就是之前删除掉的数据文件，此时还在内存中。
4.  在数据库中执行flush tables with read lock进行锁库，防止数据写入，以便恢复
5.  查看show master status，等待file及pos点不在变化
6.  把第3步的几个文件，用cp命令复制到数据库目录即可
7.  重启mysql服务，恢复完成。

# 主从复制故障处理 #

主从复制时，经常会出现错误，大部分人面对错误时，直接进行跳过，其实可能会造成主从数据不一致，对以后故障切换造成潜在的风险。而导出导入数据，重新做主从同步，又太耗费时间。所以，以下是针对这一方面问题的处理，作出的详细总结。

## 在master上删除数据出现的故障 ##

在master上删除数据后，slave因找不到相应记录而报错，这种情况由于主库已经将记录删除，所以从库也可以删除，直接跳过错误即可。

stop slave; set global sql_slave_skip_counter=1;start slave;

*  1

## 主键重复 ##

主键重复要插入的记录，在从库上已经存在了。可以将这条记录删除，从主库上导出信息，插入从库表中，重启同步进程。

stop slave; set global sql_slave_skip_counter=1;start slave;

*  1

## 在master上更新记录，slave找不到记录 ##

从主库上导出信息，插入从库表中，重启同步进程。

stop slave; set global sql_slave_skip_counter=1;start slave;

*  1

## slave宕机，导致relay-log损坏，同步出错 ##

如果slave死机，重启后可能同步报错`error initializing relay log position:I/O reading...`，

这是因为relayl-log损坏了。mysql 5.5之后版本，配置relay-log-recover=1参数就可以自动恢复日志同步。

## slave落后延迟过多 ##

有时查看从库同步状态，会发现有一项指标，seconds\_behind\_master的数值较大，这个数值代表落后主库的秒数，通常由慢SQL，慢事务导致。因此避免使用大的耗时事务，及时优化SQL，是保证从库不被拖跨的一个措施。

## slave自动跳过错误 ##

同步时经常出现的错误代码1023（记录不存在），1062(主键重复)，这些错误[无关紧要][Link 2]，每次都手动处理会很繁琐，浪费不必要的时间，我们可以将其设置自动跳过。  
在配置文件中增加：

# 自动跳过错误
    slave_skip_errors=1023,1062

*  1
 *  2

## 主从数据库版本不一致引起出错 ##

如果主从版本不一致，可能会导致同步错误。因为低版本的mysql不兼容高版本中的某些语法或特性，则主库版本高，从库版本低时，从库会报错，而主库版本低，从库版本高时，同步正常。生产环境中，应该保持数据库版本一致，避免增加故障隐患。

转载于:https://www.cnblogs.com/applelife/p/10511314.html

[Link 1]: https://www.baidu.com/s?wd=%E6%9C%BA%E6%A2%B0%E7%A1%AC%E7%9B%98&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[SSD]: https://www.baidu.com/s?wd=SSD%E5%9B%BA%E6%80%81%E7%A1%AC%E7%9B%98&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E6%97%A0%E5%85%B3%E7%B4%A7%E8%A6%81&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd