从库落后过多问题排查及解决方法

mysql版本 :
mysql5.5.49

主从结构：
M1--->S1  (DB_08~09)
M2--->S2  (DB_04~07)
M3--->S3  (DB_00~03)

问题描述：

    公司某款游戏从库落后过多，其中S1落后M1时间为 T ，S2落后M2时间大概为1.8T ， S3落后M3时间大概为1.8T。


问题排查：

① S2和S3落后时间接近S1落后时间两倍的原因为 ， M2和M3的库量是M1的两倍

② 分析是SLAVE的哪个线程导致的落后(IO_THREAD 和 SQL_THREAD)

    登录salve上查看(show processlist;)

    Slave_IO_State: Waiting for master to send event
    Slave_SQL_Running_State: Reading event from the relay log

    由此可以判断出是 SQL线程的原因，故可以排除网络原因导致的binlog传输问题导致数据落后。即和主库以及网络无关

③ OS层面的IO策略调整 

    echo 5 > /proc/sys/vm/dirty_background_ratio (该值默认为10 ， 意思是当文件系统缓存的脏页大小占总内存10%的时候刷新到磁盘，异步过程)
    echo 10 > /proc/sys/vm/dirty_ratio （该值默认是20 ， 意思是当文件系统缓存的脏页大小占总内存20%的时候刷新 到磁盘，同步过程，即会阻塞其他的IO进程，和上面的区别）

    结果到了周末高峰期的时候从库又开始落后大量数据

④ mysql层面参数调整

    登录S1 S2 S3上查看 几个重要参数
     
    

    三个参数
    innodb_flush_method=O_DIRECT ，这个参数设置了mysql对于buffer中的数据文件以及日志文件的写入策略，O_DIRECT表示 数据文件的写入是从innodb buffer直接落到磁盘不经过OS层的缓冲，而日志文件需要经过OS层的缓冲。
    
    

    synv_binlog=1，这个参数设置了binlog的刷盘策略，0,1..N ，0表示binlog_buffer刷盘跟着系统来，N表示每提交N次事务就进行刷盘。
    innodb_flush_log_at_trx_commit=1，这个参数设定了redo log 的刷盘策略，0表示每一秒钟进行write以及fsync，1表示每次事务提交进行write以及fsync，2表示每次事务提交进行write但是每秒钟进行fsync。

    由上面参数的设置可以知道，当innodb_flush_method=O_DIRECT的时候，我们在os层面对参数 dirty_background_ratio 和 dirty_ratio的调整是没有意义的，因为数据文件的落入并没有经过OS缓冲。

    另外，这里是从库(并且并没有开启log_slave_updates参数)，sync_binlog的值不会对从库造成影响。

    故而，我们只有调整最后一个参数，那就是innodb_flush_log_at_trx_commit参数，最终调整为2，一个折中方案，调整之后，从库立马追上了主库。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31385648/viewspace-2127313/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31385648/viewspace-2127313/