mysql延迟优化

1. MYSQL 优化延迟方法

目录

• 1. MYSQL 优化延迟方法
  • 1.1. 造成延迟可能的原因分析：
    • 1.1.1. 从库配置方面分析
  • 1.2. 优化方案
    • 1.2.1. 方案1 优化IO/ sync_binlog数据刷频率（有风险）
    • 1.2.2. 双1参说明
    • 1.2.3. 方法二：查看中继日志刷新频率
    • 1.2.4. 方法三：slave_rows_search_algorithms
    • 1.2.5. 方法四：当有大量ACK等待确认的情况，
  • 1.3. 并行复制
    • 1.3.1. 5.6版本
    • 1.3.2. 5.7版本
    • 1.3.3. 并行复制原理
  • 1.4. 架构优化层面

1.1. 造成延迟可能的原因分析：

1. master上多为并发事务，salve上则多为单线程回放（MySQL 5.7起，支持的并行回放，有所缓解）
2. slave机器性能比master更弱
3. 表结构设计不合理,缺少主键或者唯一约束
4. slave上运行大量只读低效率的SQL，从库在同步数据的同时，可能跟其他查询的线程发生锁抢占的情况，此时也会发生延时。
5. 大量大事务,slave无法并行回放，大事务执行，如果主库的一个事务执行了10分钟，而binlog的写入必须要等待事务完成之后，才会传入备库，那么此时在开始执行的时候就已经延迟了10分钟了
6. 主库的写操作是顺序写binlog，从库单线程去主库顺序读binlog，从库取到binlog之后在本地执行。mysql的主从复制都是单线程的操作，但是由于主库是顺序写，所以效率很高，而从库也是顺序读取主库的日志，此时的效率也是比较高的，但是当数据拉取回来之后变成了随机的操作，而不是顺序的，所以此时成本会提高。
7. 当主库的TPS并发非常高的时候，产生的DDL数量超过了一个线程所能承受的范围的时候，那么也可能带来延迟
8. 在进行binlog日志传输的时候，如果网络带宽也不是很好，那么网络延迟也可能造成数据同步延迟

一般主从复制，有三个线程参与，都是单线程：Binlog Dump（主） ----->IO Thread （从） -----> SQL Thread（从）。复制出现延迟一般出在两个地方：
1）SQL线程忙不过来（可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；主要原因）
2）网络抖动导致IO线程复制延迟（次要原因）。

1.1.1. 从库配置方面分析

1、修改sync_binlog的参数的值
想要合理设置此参数的值必须要清楚地知道binlog的写盘的流程：

可以看到，每个线程有自己的binlog cache，但是共用同一份binlog。

图中的write，指的就是把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度快

图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占用磁盘的IOPS
而write和fsync的时机就是由参数sync_binlog来进行控制的。

1、当sync_binlog=0的时候，表示每次提交事务都只write，不fsync
2、当sync_binlog=1的时候，表示每次提交事务都执行fsync
3、当sync_binlog=N的时候，表示每次提交事务都write，但积累N个事务后才fsync。

我们建议将此参数的值设置为1，因为这样的话能够保证数据的安全性,但是如果出现主从复制的延迟问题，可以考虑将此值设置为100~1000中的某个数值，非常不建议设置为0，因为设置为0的时候没有办法控制丢失日志的数据量，但是如果是对安全性要求比较高的业务系统，这个参数产生的意义就不是那么大了。

2、直接禁用salve上的binlog
当从库的数据在做同步的时候，有可能从库的binlog也会进行记录，此时的话肯定也会消耗io的资源，因此可以考虑将其关闭，但是需要注意，如果你搭建的集群是级联的模式的话，那么此时的binlog也会发送到另外一台从库里方便进行数据同步，此时的话，这个配置项也不会起到太大的作用。

3、设置innodb_flush_log_at_trx_commit 属性
表示每一次的事务提交是否需要把日志都写入磁盘，这是很浪费时间的，一共有三个属性值，分别是
0（每次写到服务缓存，一秒钟刷写一次）
1（每次事务提交都刷写一次磁盘），
2（每次写到os缓存，一秒钟刷写一次），一般情况下我们推荐设置成2，这样就算mysql的服务宕机了，卸载os缓存中的数据也会进行持久化。

1.2. 优化方案

1.2.1. 方案1 优化IO/ sync_binlog数据刷频率（有风险）

1. 查看参数设置

show variables like 'innodb_flush_neighbors';
show variables like 'innodb_io_capacity';
show variables like 'innodb_flush_log_at_trx_commit';
show variables like 'sync_binlog';

2. 参数临时调整：

set global innodb_flush_log_at_trx_commit=2;
set global innodb_io_capacity_max=20000; #根据实际情况调整
set global innodb_io_capacity=20000; #根据实际情况调整
set global innodb_flush_neighbors=2;
set global sync_binlog=1000;

3. 追平之后调整回来：

set global innodb_flush_log_at_trx_commit=1;
set global sync_binlog=1;

1.2.2. 双1参说明

set global innodb_flush_log_at_trx_commit=1;
set global sync_binlog=1;

1.在单机下一定要配置为双1，避免因为宕机而导致数据丢失
2.在主从服务器下，备库可以配置为以下值，临时解决延迟问题

set global sync_binlog=0;
set global innodb_flush_log_at_trx_commit=0;

1.2.3. 方法二：查看中继日志刷新频率

（该方法适用于备库的io压力较大，备库读写太频繁的情况，有风险）：

1. 查看参数值是否合理

select @@sync_relay_log_info, @@sync_relay_log,@@sync_master_info;

2. 调整参数值

set global sync_relay_log_info=10000;
set global sync_master_info=10000;
set global sync_relay_log=10000;

2. 追平之后修改回原值

set global sync_relay_log_info=1;
set global sync_master_info=1;
set global sync_relay_log=1;

1.2.4. 方法三：slave_rows_search_algorithms

参数配置：

set global slave_rows_search_algorithms='INDEX_SCAN,HASH_SCAN';

这个参数大部分时间是没有用的, 官网有明确说明限制条件
slave_rows_search_algorithms由三个值的组合组成：TABLE_SCAN，INDEX_SCAN， HASH_SCAN。
TABLE_SCAN,INDEX_SCAN (默认配置，表示如果有索引就用索引，否则使用全表扫描)

适用场景：
HASH_SCAN可以部分解决无主键表导致的复制延迟问题。

当表上无主键或唯一键时，那么对于在该表上做的DML，如果是以ROW模式复制，则每一个行记录前镜像在备库都可能产生一次全表扫描（或者二级索引扫描）， 大多数情况下，这种开销都是非常不可接受的，并且会产生大量的延迟。
简单的讲，在apply rows_log_event时，会将 log_event 中对行的更新缓存在两个结构中，分别是：m_hash, m_distinct_key_list。 m_hash：主要用来缓存更新的行记录的起始位置，是一个hash表； m_distinct_key_list：如果有索引，则将索引的值push 到m_distinct_key_list，如果表没有索引，则不使用这个List结构； 其中预扫描整个调用过程如下： Log_event::apply_event

从执行过程上可以看出，当使用hash_scan时，只会全表扫描一次，虽然会多次遍历m_hash这个hash表，但是这个扫描是O(1),所以，代价很小，因此可以降低扫描次数，提高执行效率。

1.2.5. 方法四：当有大量ACK等待确认的情况，

可以暂时关闭半同步复制
临时关闭半同步复制

异步复制的缺点在于主库只负责将数据库的增，改，查操作记录到 binary log中，而从库在通过日志对操作进行回放同步之后，主库对从库的同步内容不检测，不知道是否同步,这里引入半同步复制解决这一问题.
半同步复制在协议中添加了一个同步步骤。 这意味着主节点在提交时需要等待从
节点确认它已经接收到事务。只有这样,主节点才能继续提交操作。
这种模式下主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长。

1.3. 并行复制

1.3.1. 5.6版本

MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。
其实从mysql的5.6版本开始就已经支持了并行复制，只是支持的粒度是按库并行，这也是为什么现在的版本中可以选择类型为database，其实说的就是支持按照库进行并行复制。

但是其实用过的同学应该都知道，这个策略的并行效果，取决于压力模型。如果在主库上有多个DB，并且各个DB的压力均衡，使用这个策略的效果会很好，但是如果主库的所有表都放在同一DB上，那么所有的操作都会分发给一个worker，变成单线程操作了，那么这个策略的效果就不好了，因此在实际的生产环境中，用的并不是特别多。

1.3.2. 5.7版本

mysql5.7版本的时候，根据mariaDB的并行复制策略，做了相应的优化调整后，提供了自己的并行复制策略，并且可以通过参数slave-parallel-type来控制并行复制的策略：
1、当配置的值为DATABASE的时候，表示使用5.6版本的按库并行策略；
2、当配置的值为LOGICAL_CLOCK的时候，表示跟mariaDB相同的策略。

MySQL 5.7开启Enhanced Multi-Threaded Slave配置：

临时：
-- 查看并行的slave的线程的个数，默认是0.表示单线程
show global variables like 'slave_parallel_workers';
-- 根据实际情况保证开启多少线程
set global slave_parallel_workers = 4;
-- 设置并发复制的方式，默认是一个线程处理一个库，值为database
show global variables like '%slave_parallel_type%';
-- 停止slave
stop slave;
-- 设置属性值
set global slave_parallel_type='logical_check';
-- 开启slave
start slave
-- 查看线程数
show full processlist;

永久：
 slave-parallel-type=LOGICAL_CLOCK  #组复制的方式
 slave-parallel-workers=16   #开启16个线程
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
 relay_log_recovery=ON

1.3.3. 并行复制原理

通过上图我们可以发现其实所谓的并行复制，就是在中间添加了一个分发的环节，也就是说原来的sql_thread变成了现在的coordinator组件，当日志来了之后，coordinator负责读取日志信息以及分发事务，真正的日志执行的过程是放在了worker线程上，由多个线程并行的去执行。
并行复制常用语句：

其核心思想是：不同schema下的表并发提交时的数据不会相互影响，即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致。

在MySQL 5.7中，引入了基于组提交的并行复制（Enhanced Multi-threaded Slaves），设置参数slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支持一个schema下，slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。
其核心思想：一个组提交的事务都是可以并行回放（配合binary log group commit）；
slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。
　　其中，变量slave-parallel-type可以有两个值：

　　DATABASE 默认值，基于库的并行复制方式；
　　LOGICAL_CLOCK：基于组提交的并行复制方式  

coordinator在进行分发的时候，需要遵循的策略是什么？

1、不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
2、同一个事务不能被拆开，必须放到同一个worker中。

此时，大家需要思考一个问题：同时处于执行状态的所有事务，是否可以并行？
答案是不行的，因为多个执行中的事务是有可能出现锁冲突的，锁冲突之后就会产生锁等待问题。

在mariaDB中，所有处于commit状态的事务是可以并行，因为如果能commit的话就说明已经没有锁的问题，但是大家回想下，我们mysql的日志提交是两阶段提交，如下图，其实只要处于prepare状态就已经表示没有锁的问题了。

因此，mysql5.7的并行复制策略的思想是：

1、同时处于prepare状态的事务，在备库执行是可以并行的。
2、处于prepare状态的事务，与处于commit状态的事务之间，在备库上执行也是可以并行的。

基于这样的处理机制，我们可以将大部分的日志处于prepare状态，因此可以设置

1、binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
2、binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

1.4. 架构优化层面

1、业务的持久化层的实现采用分库架构，让不同的业务请求分散到不同的数据库服务上，分散单台机器的压力
2、服务的基础架构在业务和mysql之间加入缓存层，减少mysql的读的压力，但是需要注意的是，如果数据经常要发生修改，那么这种设计是不合理的，因为需要频繁地去更新缓存中的数据，保持数据的一致性，导致缓存的命中率很低，所以此时就要慎用缓存了
3、使用更好的硬件设备，比如cpu，ssd等，但是这种方案一般对于公司而言不太能接受，原因也很简单，会增加公司的成本，而一般公司其实都很抠门，所以意义也不大，但是你要知道这也是解决问题的一个方法，只不过你需要评估的是投入产出比而已。