MySQL复制性能优化

MYSQL复制最容易出现的性能问题呢,就是主从延迟的问题,从前面的内容中我们可以知道,MYSQL复制是异步的,只是事务在

主库上执行完,并记录到二进制日志之后,从库才能从二进制日志中取已经执行完的事务,并把这些事务呢,保存在从库的

中继日志中,然后同步的SQL线程呢,才能够从中继日志中读取这些事件,并在从库中进行存放,从这个过程中来看延迟呢,

肯定是不可避免的,我们要的是如何减少这种主从复制的延迟,所以我们再来看看MYSQL工作的原理图,从这张图中我们可以

看到,影响主从复制的因素呢,可能有以下几个,首先是主库写入二进制日志的时间,或者是主库执行事务的时间,可能更加的

适合一点,由于从库呢只是简单的存放主库的SQL修改的事件,所以理论上,主库上执行需要多少时间,那么在从库上同样也需要

多少时间来执行这个事务,例如我们在主库上执行一个大的事务,在这个事务中呢,10万行的数据,这个事务在主库中,执行了

两分钟,只有这个大事务完全执行完并且提交后,才会记录到二进制的日志中,并传输到从服务器上,那么同样的,在从服务器上

执行也差不多,需要两分钟的时间,这个就是在基于SQL段的复制中,如果我们使用的是基于行的复制,可能在从库上,存放的时间

可能要快一点,但是相比于基于段的复制呢,表传输的二进制日志量呢,要大很多,所以在网络传输上要消耗一些时间,所以无论

是在哪里消耗了一些时间,这都会造成主从复制的一些延迟,最好的方法就是控制主库事务的大小,分割大事务,造多个小事务,

从上面的例子中来说,我们可以把几十万行的变更呢,修改为5千行一个提交,原来的10万行,修改需要两分钟,但是执行5千行可能

只需要6秒钟,这就大大缩短了可能造成主从的时间

我们还可以发现,影响主从复制的第二个因素呢,就是二进制日志从主库传输到从库并写入到从库工具日志的时间,

通常我们的主从服务器处在同一个IDC机房中,网络传输的速度是足够快的,同时在从服务器中,记录这个更新日志呢,对于

数据文件来说呢,需要的是顺序写,所以呢,磁盘IO性能呢也不会对写日志产生多大的影响,所以主要的影响因素呢,就是我们

传输的日志量的多少了,关于这一点我们可以通过下面的方式,一种是对二进制日志使用混合格式,这样大多数情况下呢,会使用

基于SQL段的日志记录,这样的话可以大大减少二进制日志的数据量,另外为了主从数据一致性的考虑呢,我们可能要使用这种行格式,

就是基于行的格式,那么在这个时候呢,我们就要设置,binlog_row_image这个参数呢,变成minimal的方式,来减少二进制日志量的

大小,那么MYSQL主从复制的最后一步呢,由从库的SQL线程,在从库中存放,这才是对MYSQL主从延迟最大的一步

这才是对MYSQL延迟影响最大的一步,由于默认情况下,在从库中,只有一个SQL线程,所以原本在主库上并发写入的事件呢,

同步到从库上变成了串行处理,虽然是串行处理,但是一般情况下,我们很少发现,长时间的主从延迟,这是为什么呢,这是因为

通常情况下,主上写的并发呢,其实也并不多,所以在从库上串行话后,由于避免了一些锁的应用,所以处理起来还是很快的,

但是如果在促销这种场景下,主上写并发突然中增加,由于这个原因会造成长时间的主从延迟,另外还有一种情况就是大事务

情况下,从上的SQL线程会被大事务所占用,所以这个大事务之后,在从上就会被阻塞掉,只有等这个大事务处理完成之后,才能

进行一些处理,前面我们讲过,我们可以把大事务分成多个小事务的方式呢,来缓解这个问题,但是大家有没有想过呢,如果是

修改表结构这样的操作呢,对于大表表结构的修改往往需要很长的时间,并且无法像大事务那样进行分割,所以这时SQL事务同样

还是会被占用,从而造成大量的延迟,为了解决这个问题呢,最好的方法就是使用5.6中多线程复制,来增加从上SQL的数量,当然呢

我们也有一些方法来减少修改表结构的占用时间,但是我们现在呢我们还是来看如何来配置多线程的复制,多线程复制是在MYSQL5.6

引入的新的功能,在MYSQL5.6时,每个SQL线程呢,只能处理一个从库中的一个数据库上的一种SQL存放,所以当我们只有一个数据库,

而且有时候,启用多线程复制可能还不如不用的情况,好在这种情况在MYSQL5.7中呢,有了改善,在MYSQL5.7中,可以按照逻辑时钟的

方式,来分配这种SQL线程,这样呢就使得多线程复制呢更加实用了,所以下面我们就以MYSQL5.7为例

讲解并演示一下如何在MYSQL5.7中,使用这种多线程复制,其实配置还是很简单的,在完成主从配置的基础之上,

只要一下四步就可以了,首先我们第一步是我们要在从上停止链路复制,第二步是设置链路并发的并发同步的类型

为逻辑时钟的方式,这个就决定了我们如何使用多线程复制,默认是MYSQL5.7之前的那种方式,每一个线程只能处理

一个数据库下的事务,这里我们要使用的是逻辑时钟的方式,所以也是在MYSQL5.7中呢,所新增的一种方式,接下来就是

设置我们所需要的复制线程的数量,这里设置的是4个线程,这个变量也同时决定了我们并非处理的线程数,最后我们再

启动这个复制就可以了,下面我们就以之前所配置的主从复制为基础来演示一下,如何使用多线程的主从复制

首先我们来到我们的演示环境中,就是我们刚才配置好的,现在我们来看一看,从库中我们可以看到,只有一个SQL线程

在运行的

show processlist;

我们这个时候按照刚才的步骤,进行多线程复制的配置,首先我们停止从库上的slave进程,大家可以看一看,

stop slave;

show slave status \G;

现在大家已经看到我们从库上的服务呢已经停止了,接下来看一下我们的配置参数,首先我们来看一下目前的slave,

并发配置的是什么

show variables like 'slave_parallel_type';

可以看到默认情况它是基于数据库的,那现在我们把它设置为基于逻辑时钟的,也就是在MYSQL5.7中新增加的这种方式,

set global slave_parallel_type='logic_clock';

下面我们要设置的就是并发所需的线程数量,我们看一看当前所需的线程数量是多少,大家看到这个设置是0

show variables like 'slave_parallel_workers';

我们把它改成4个线程

set global slave_parallel_workers=4;

这个时候我们再启动复制

start slave;

show slave status \G;

大家可以看到,主从复制重新的启动了

这个时候我们再来看看他的进程是多少

show processlist \G

大家可以看到,这里已经有4个IO线程了,这些都是在等待传输的IO线程