首页 » linux » 正文

MySQL主从复制延迟问题

最近发现一台从库的数据同步延迟灰常大,以至于需要人工干预去手工加快同步速度。

话不多说,小伙伴们请看show slave status\G结果:

QQ图片20160107112603

 

从上图可看出Seconds_Behind_Master字段延迟相差182166秒(换算后大概2天左右 (⊙﹏⊙))

Seconds_Behind_Master字段:该字段表明备机“落后”主机多少的一个理论值,单位:秒。

出现以上延迟可能的原因有几种:

1、查询SQL长时间锁表

2、主机更新频率太快

3、磁盘IO性能低下

另外就是MySQL本身的问题,slave不支持并发。

MASTER如果一直在并发执行操作,SLAVE单线程操作必将会落后MASTER.

在5.6的版本中,MySQL已经支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制。不过,它只能支持一个实例下多个 database 间的并发复制,并不能真正做到多表并发复制,所以MySQL复制终究会是异步同步,而非大家认为的实时同步。

 

优化方法:

1、参数优化

sync_binlog

当事务执行完成后,刷新cache数据到物理磁盘的频率。

默认配置:1。即一次事务执行后,立马刷新数据至物理磁盘。建议如果做为从机可把该参数适当调大,或设为0(一直在cache,直到cache爆满才写入)

innodb_buffer_pool_size

Innodb的缓冲区,缓存数据和索引。传统上也称作“内存”。建议如果从机只做Mysql复制使用,适当调整为物理内存的60%-80%。

innodb_flush_log_at_trx_commit 

日志缓冲区刷新到物理磁盘的配置,MySQL官方建议与sync_binlog参数均设置为1,可完整的保证数据的安全性

当该值为1时,将日志缓冲区写入每个事务提交的日志文件中,并在日志文件中执行刷新到磁盘操作的操作。

当该值为2时,将日志缓冲区写入每个事务提交的日志文件中,但不执行该磁盘操作的刷新,而是在系统缓存中。如出现系统崩溃或物理故障,会导致丢失1~2秒的数据。

当该值为0时,将日志缓冲区写入每个事务提交的日志文件中,但不执行该磁盘操作的刷新,而是在数据库缓存中。如出现进程假死或被杀死,会导致丢失1~2秒的数据。

建议从机可将该参数设为2。

2、物理IO优化

可适当考虑使用高转速的机械硬盘或SSD。

3、数据结构优化

适当将一些流水、记录、日志等形式的数据表进行分表归档,提高更新效率

 

测试:

根据以上说到的一些优化方法,使用mysql自带性能测试工具mysqlslap简单的测试结果如下:

环境:一台物理设备同时运行2个数据库实例

实例A的innodb_flush_log_at_trx_commit =1

实例B的innodb_flush_log_at_trx_commit =2

(由于环境限制,本次仅对该参数进行了测试,之后如环境富裕统一调整参数再更新测试结果)

实例A:

分别插入1条和2000条数据的操作耗时

QQ截图20160107111744

分别插入2000条和更新2000条数据的耗时QQ截图20160107111324

实例B:

分别插入1条和2000条数据的操作耗时

QQ截图20160107111642

分别插入2000条和更新2000条数据的操作耗时 QQ截图20160107111234

如果测试结果可看出,修改后的结果基本上是之前的2倍左右,小伙伴们也动手448~

发表评论