mysql 减小时,MySQL 减少从库延迟的方法

1,935 total views, 1 views today

从库延迟有两方面原因：

1、IO thread慢，主要是因为网络带宽不足。

在主从库开启启压缩参数slave_compressed_protocol减少压力。网上查看实验数据，压缩率大概是1/4(开启压缩7.14MB/s，不开启则是23.76MB/s)

如果CPU压力已经很大不建议开启压缩参数，毕竟压缩要消耗大量CPU资源。

2、 SQL thread慢。

SQL thread负责读取relay log，然后将主库传递过来的events 应用一遍。应用events涉及到写入数据、删除数据以及更新数据，使用到了事务。

优化方法：

<1> innodb_flush_log_at_trx_commit = 2

设置为0，重做日志从缓冲区刷到操作系统缓冲区每秒一次(innodb_flush_log_at_timeout控制)，操作系统缓冲区每秒将数据持久化到磁盘。设置为0很危险。

设置为1，每次事务提交都会将重做日志缓存区的数据刷入到操作系统缓冲区，然后立即调用fsync()将操作系统缓冲区中的数据持久化到磁盘中。

设置为2，事务提交时会将重做日志写入到操作系统缓冲区，但是并不会立马刷新持久化到磁盘中，而是每秒才会刷新持久化到磁盘。所以，服务器crash，数据有可能丢失。但是，数据库crash，并不会有数据丢失。

过于频发刷磁盘，会消耗大量IO资源，但是这也保证了数据安全。

将 innodb_flush_log_at_trx_commit 设置为2，可以有效减少IO压力，等大事务过去后，再将其调回0。

<2> sync_binlog = 0

控制binlog刷新磁盘的策略，关闭改参数，同样可以减少从库的IO压力，但是安全性大大降低，不建议关闭。

<3> log_slave_updates = 0

开启这个参数， SQL thread 应用relay log时会将数据变动情况写入到从库自身的binlog中，无疑增加了从库的io压力，也要关闭。

<4> innodb_flush_method = O_DIRECT

减少重复缓存，提升写入效率。

<5> slave_parallel_workers 开启多线程复制

如果条件允许，并且技术上可以驾驭，可以开启多线程复制 slave_parallel_workers。可提高从库处理relay log的效率。

参考：

https://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/