By 兰春

Updated:

Contents
  1. 1. 一、MySQL为什么会延迟
    1. 1.1. 1.1 MySQL复制的架构
    2. 1.2. 1.2 大事务导致的延迟
    3. 1.3. 1.3 IO线程导致的延迟
    4. 1.4. 1.4 SQL线程导致的延迟
    5. 1.5. 1.5 网络问题导致的延迟
    6. 1.6. 1.6 硬件问题导致的延迟
  2. 2. 二、延迟场景的解决方案
    1. 2.1. 2.1 DDL
      1. 2.1.0.1. 2.1.1 ddl的最佳实践
  3. 2.2. 2.2 大事务
    1. 2.2.0.1. 2.2.1 大事务拆小事务
  • 2.3. 2.3 大量并发事务
    1. 2.3.0.1. 2.3.1 调整安全参数
    2. 2.3.0.2. 2.3.2 MTS(enhanced multi-threaded slave)
  • 3. 终极大招

    • 一、MySQL为什么会延迟

    数据延迟： 是指master执行了N个事务，slave却只执行了N-M个事务，说明master和slave之间产生了延迟

    延迟原因：延迟的原因很多种，大部分情况下是 slave的处理能力跟不上master导致

    接下来，我们从各种角度分析下延迟的原因

    1.1 MySQL复制的架构

    delay

    通过架构图，可以直观的看到数据延迟的点有哪些，当然也就可以知道如何优化了

    1.2 大事务导致的延迟

    大家都知道，binlog的写入时机是在commit的时候，redo的写入时机是在事务执行阶段就开始。

    Oracle是通过物理复制，我们姑且认为是redo的复制，因为redo是事务执行阶段就开始写入的，所以，oracle的复制几乎没有延迟

    MySQL是基于binlog复制的，如果有一个非常大的事务，如果需要1个小时，那么master在1小时候后才会生成binlog，而此时，slave就比master慢了至少1个小时，还不算是binlog传输时间

    这是第一种延迟原因，破解方法后面说

    PS: DDL虽然不是事务，但是特性跟大事务一样，都是在master上执行了一个巨大无比的操作才写的binlog

    1.3 IO线程导致的延迟

    根据复制的架构，Master写完binlog后，需要通过网络传输给slave（这部分我们需要网络的支持）
    然后呢，IO thread会将binlog写到slave的relay log中，这部分工作由IO thread完成

    好了，这里我们分析下瓶颈：

    1. io thread 是单线程的
    2. io thread 写入 relay log的速度

    经过分析以及大量的实战，IO thread并不是我们的瓶颈，因为relay log是顺序的写入，非常快，几乎碰不到瓶颈

    1.4 SQL线程导致的延迟

    master 上面的事务是可以进行并发的，然后binlog传输到slave后，slave是却以单线程的模式读取和执行relay log
    这是典型的消费能力不足

    1.5 网络问题导致的延迟

    网络问题不用多说了吧，如果要复制良好，一个稳定的网路环境是在所难免的

    1.6 硬件问题导致的延迟

    如果master是SSD，但是slave还是机械硬盘，这样的架构存在延迟也不足为奇

    二、延迟场景的解决方案

    2.1 DDL

    2.1.1 ddl的最佳实践

    1. 通过pt-osc 或者 gh-ost 来让ddl拆分成一个个小事务，并且还有流控功能

    2. 在slave上先ddl，然后master-slave切换，然后再old master上进行ddl，从而完美的解决了这个问题

    2.2 大事务

    2.2.1 大事务拆小事务

    如果说大事务对于binlog的产生有极大的影响，那么我们认为定义小事务，大事务不允许执行

    有大事务的监控，可以基于时间，可以基于数据量，监控到不符合规范的trx自动kill

    2.3 大量并发事务

    2.3.1 调整安全参数

    sync_binlog = 0 && innodb_flush_trx_commit = 0 可以极大的提高事务处理的吞吐量，因为IO fsync的次数变少了，可以非常有效的降低数据延迟

    风险：如果slave挂了，需要重做slave

    2.3.2 MTS(enhanced multi-threaded slave)

    之前有深入讨论过MTS的文章，它主要的功能就是让slave拥有比master更快的并行能力，从而有效的让延迟缩短，甚至无延迟

    终极大招

    半同步： 半同步可以让延迟为0，但是半同步有自动切换为异步复制的可能

    全同步： MySQL的group replication 就是这类的代表，这个话题以后再聊

    最后，以上就是关于MySQL延迟优化的方法，几乎涵盖了90%的方案，如果大家还有更好的方案，不妨拿出来大家一起探讨

    Contents
    1. 1. 一、MySQL为什么会延迟
      1. 1.1. 1.1 MySQL复制的架构
      2. 1.2. 1.2 大事务导致的延迟
      3. 1.3. 1.3 IO线程导致的延迟
      4. 1.4. 1.4 SQL线程导致的延迟
      5. 1.5. 1.5 网络问题导致的延迟
      6. 1.6. 1.6 硬件问题导致的延迟
    2. 2. 二、延迟场景的解决方案
      1. 2.1. 2.1 DDL
        1. 2.1.0.1. 2.1.1 ddl的最佳实践
    3. 2.2. 2.2 大事务
      1. 2.2.0.1. 2.2.1 大事务拆小事务
  • 2.3. 2.3 大量并发事务
    1. 2.3.0.1. 2.3.1 调整安全参数
    2. 2.3.0.2. 2.3.2 MTS(enhanced multi-threaded slave)
  • 3. 终极大招

    • 幸福,不在于得到的多

    而在于计较的少