欢迎光临
我们每日更新

常见mysql数据库故障实例诊断及处理方法

数据库作为网站数据存储的重要仓库,一旦崩溃,对网站来说是一场灾难,所以应该尽量把问题扼杀在摇篮中,当出现问题及时处理,否则等到服务器宕机,再去”救火“工作,往往不能第一时间准确诊断。下面我们就来说说常见mysql数据库故障实例有哪些,该怎么处理。

影响性能的因素

  • 磁盘I/O

    影响mysql innodb引擎性能最主要因素是磁盘I/O。最广泛使用的硬盘都是机械硬盘,机械硬盘运作时,磁盘需进行寻址,随机读写速度大大你玩顺序读写。每次读写动作时,磁道可能存在较大的偏移,磁盘寻址时间的延迟,使I/O性能急剧下降。因此生产环境中,常常使用RAID5、RAID10磁盘阵列并行读写,或者采用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: 查看进程运行情况

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

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

故障与处理

连接数过多

连接数过多有多种原因,存在慢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. 在使用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;

主键重复

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

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

在master上更新记录,slave找不到记录

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

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

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(主键重复),这些错误无关紧要,每次都手动处理会很繁琐,浪费不必要的时间,我们可以将其设置自动跳过。
在配置文件中增加:

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

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

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

赞(0)
未经允许不得转载:站长技术网 » 常见mysql数据库故障实例诊断及处理方法
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址