作为一名DBA，我们在工作中经常会遇到一些MySQL主从同步延迟的问题。造成这些同步慢的问题的原因其实有很多：可能是主从网络问题，也可能是网络带宽问题，可能是大事务导致的，也可能是单线程复制导致的延迟。最近，笔者遇到了一个非常典型的同步延迟问题。在此写下分析过程，希望能为DBA排查同步延迟问题提供更系统的方法论。一、背景最近有一批DB出现了比较大的延迟。这组DB是专门用来存放监控数据的，每分钟会通过LoadData的方式导入大量的数据。为了节省空间，原来使用压缩表的InnoDB引擎被转换为TokuDB引擎。使用的版本和引擎如下：MySQLVersion:5.7存储引擎:TokuDB转换后，发现主从延迟逐渐增加，基本每天落后主机50条左右binlog1条左右，7.5小时左右数据延迟，主机每天产生160条左右的binlog，binlog列表如下图所示：由于对这个业务很熟悉，所以很快定位到主从同步延迟的原因，以及延迟问题很快就解决了。这里我们不直接说解决方案，而是想描述一套完整的解决主从延时问题的思维方法，和大家做一些系统的思考。带着一个问题去思考延迟的根本原因和解决方案可能更有意义。授人以鱼不如授人以渔。接下来，让我们一起来集思广益。2.思考首先，既然出现了主从延迟，就说明从机上的消费速度跟不上宿主机产生binlog的速度。我们先想想可能的原因，再根据现场的线索来验证猜想的正确性。事实上，所谓的故障排除就是提出可能存在的问题和猜想，然后继续证明的过程。不同的是每个人的经历不同，调查的质量也不尽相同，仅此而已。1、网络网络可能会造成主从延迟问题。比如master或者slave的带宽满了，master和slave之间的网络延迟很大，可能会导致master上的binlog没有完整的传到slave上，造成延迟。作者组DB的IO线程已经近乎实时的拉取了对应的binlog到从DB，基本消除了网络带来的延迟，也可以结合网络质量相关的监控进一步确认是网络问题。2.机器性能从机是否使用了坏机器？之前遇到过一些业务slave使用坏机器，造成master-slave延迟。比如host用SSD，slave还是用SATA。坏机当从机的观念需要改变。随着对DB自动切换的需求越来越大，尤其是笔者所在的金融行业，从机的配置至少应该不会比宿主机差。从机高负载？有很多业务在从机上做统计，让从机server进入高负载，导致从机延迟大，使用top命令可以快速发现。是不是从盘有问题？当磁盘、Raid卡或调度策略出现问题时，有时单个IO延迟会很高。例如Raid卡电池充放电时，如果没有设置强制回写，则必须将回写模式改为write。通过。使用iostat命令查看DB数据盘IO状态，单次IO执行时间是否很长，block大小，磁盘队列状态等，可以对比IO调度规则和block大小设置数据库磁盘。使用iostat查看IO运行状态：从IO状态来看，没有问题，单个IO延迟很小，IOPS很低，写带宽不大。调度规则（cat/sys/block/fioa/queue/scheduler）和blocksize与宿主机一致，排除磁盘问题。从运行指标来看，机器负载很低，机器性能也可以排除。3.大事件你经常有大事件吗？DBA可能更常遇到这种情况。比如在RBR模式下，执行大量的Delete操作，或者在MBR模式下删除的时候加入一个不确定的语句（类似limit）或者Alter操作等，都会导致延迟的发生。这个可以通过查看Processlist的相关信息，使用mysqlbinlog查看binlog中的SQL来快速确认。这个想法也被排除了。4、锁冲突的问题也可能导致从机的SQL线程执行缓慢，比如从机上有一些select....forupdateSQL，或者使用了MyISAM引擎。此类问题可以通过抓取Processlist并查看information_schema下与锁和事务相关的表来检查。经排查，没有发现锁的问题。5.参数如果使用InnoDB引擎，可以根据自己的使用环境调整innodb_flush_log_at_trx_commit和sync_binlog参数，提高复制速度。对于那组DB使用的TokuDB，可以优化tokudb_commit_sync、tokudb_fsync_log_period、sync_binlog等参数进行调整。这些参数调整后，对复制的时延会有一定的影响。备注：本次调整可能会影响数据安全，需结合业务考虑。6.多线程多线程问题可能是DBA们最常遇到的问题。在5.1和5.5版本之前，MySQL的单线程复制瓶颈被广泛批评。从5.6开始，MySQL正式支持多线程复制。很容易想到，如果是单线程同步，单线程存在写瓶颈，导致主从延迟。然后调成多线程先试试效果。可以通过ShowProcesslist查看是否有多个同步线程，也可以通过查看参数（showvariableslike'%slave_parallel%'）查看是否使用了多线程。当你看到上图的结果时，恭喜你，你使用的是单线程。您可以使用以下命令将其转换为多线程复制：STOPSLAVESQL_THREAD;SETGLOBALslave_parallel_type='LOGICAL_CLOCK';SETGLOBALslave_parallel_workers=8;STARTSLAVESQL_THREAD;改造后如下图所示：我的环境如上图所示，已经是多线程复制了。因此，问题的根源不在于是否启用多线程复制。但是当我使用ShowProcesslist查看复制状态时，大多数情况下，我发现只有一个SQL线程在执行，如下图所示：从上图可以发现基本上只有一个线程在执行，所以可以初步判断有多少线程的力量没有发挥出来。为了更有力的说明问题，想办法统计每个同步线程使用的比例。统计方法如下：1.开启onlineslave相关统计（出于性能考虑默认关闭），开启方法如下：UPDATEperformance_schema.setup_consumersSETENABLED='YES'WHERENAMELIKE'events_transactions%';UPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED='YES'WHERENAME='transaction';2、创建一个查看各同步线程使用量的视图，代码如下：USEtest;CREATEVIEWrep_thread_countASSELECTa.THREAD_IDASTHREAD_ID,a.COUNT_STARASCOUNT_STARFROMperformance_schema.events_transactions_summary_by_thread_by_event_nameaWHEREa.THREAD_IDin(SELECTb.THREAD_IDFROMperformance_schema.replication_applier_status_by_workerb);3、一段时间后，统计各个同步线程的使用率，SQL如下：SELECTSUM(COUNT_STAR)FROMrep_thread_countINTO@total;SELECT100*(COUNT_STAR/@total)ASthread_usageFROMrep_thread_count;结果如下：从上面的结果可以看出，在大多数情况下，一个线程在运行，在导入大量数据的监控场景中必然会出现瓶颈。如果能够提高每个线程并发执行的能力，或许可以改善同步延迟的情况，那么如何解决呢？7、分组提交我们不妨从多线程同步的原理来思考。在5.7中，多线程复制的功能有了很大的改进，支持了LOGICAL_CLOCK的方法。在这个方法中，只要并发执行的多个事务能够同时Commit，就说明线程之间不存在锁冲突，那么Master就可以标记这组事务，在slave机器上安全的并发执行。因此，尽可能让所有线程同时提交，可以大大提高slave的执行并行度，从而降低slave的延迟。有了这个猜想，自然而然会想到人为控制，尽可能让所有线程同时提交。其实官方已经给我们提供了类似的参数。参数如下：binlog_group_commit_sync_delay注意：该参数会延迟SQL的响应，对Latency敏感的环境需要特别注意，单位为微秒。参数说明见：https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_delaybinlog_group_commit_sync_no_delay_count备注：该参数有一定的保护作用。value，不管是否达到binlog_group_commit_sync_delay设置的阈值，立即commit。参数说明见：https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_no_delay_count由于是被监控的DB，所以主要是加载数据然后显示对于1秒左右导入延迟对业务没有影响，所以调整两个参数为：SETGLOBALbinlog_group_commit_sync_delay=1000000；SETGLOBALbinlog_group_commit_sync_no_delay_count=20；备注：请根据业务特点调整这两个参数，避免上线失败。为了防止导入的SQL堆积，将SETGLOBALbinlog_group_commit_sync_no_delay_count设置为20，当达到20个事务时，无论是否达到1秒都会提交，减少对业务的影响。设置完这两个参数后，发现并发复制瞬间提升了很多，很多时候8个线程都能跑满。所以把线程调到16。运行一段时间的事件后，再次统计各个同步线程的使用率，发现并发度增加了很多。新的比例如下图所示：通过showslavestatus查看，发现slave的延迟越来越小，已经完全赶上了，并稳定运行一周。3.小结最后做一个简短的小结。遇到主从延迟的问题，可以从以下几个地方打开思路，寻找蛛丝马迹，找到问题的根源，对症下药。排查范围包括但不限于以下几个方面：网络、性能、配置（参数优化）、大事务锁、多线程复制组提交通过以上对整个问题的回顾，希望可以帮助到广大的DBA在遇到类似于复制延迟的问题时彻底解决问题。参考资料https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.htmlhttps://www.percona.com/blog/2016/02/10/estimating-potential-对于-mysql-5-7-并行复制/
