MySQL复制性能优化和常见问题分析

前言

系列文章：
1.MySQL主从复制
2.OneProxy实现MySQL读写分离
3.MySQL数据库结构设计
4.MySQL基于GTID主从复制的杂谈
5.MySQL复制性能优化和常见问题分析

先来说说影响MySQL复制性能的几个参数吧

二进制日志文件并不是每次写的时候都会同步到磁盘，当发生宕机的时候，可能会有最后一部分数据没有写入到binlog中，这给恢复和复制带来了问题。当sync_binlog=1表示每写缓冲一次就同步到磁盘，表示同步写磁盘的方式来写binlog。也就是说每当向MySQL提交一次事务，MySQL将进行一次fsync之类的磁盘同步命令来将binlog_cache的数据强制刷到磁盘中sync_binlog的值默认为0，sync_binlog=0时表示采用操作系统机制进行缓冲数据同步。采用sync_binlog=1时，会增加磁盘IO的次数，会影响写入性能。sync_binlog=1时，并不是100%安全，会存在相应的问题。比如说使用Innodb引擎时，在一个事务发出commit前，会将binlog立即刷到磁盘中。如果这时候已经写入到binlog中，但是还没有提交就已经挂了，那么MySQL重启时，会将通过Redo log、Undo log将这个事务回滚掉，但是binlog已经记入了该事务信息，不能回滚掉。所以我们需要设置innodb_support_xa=1确保MySQL服务层的binlog和MySQL存储引擎层的Redo log、Undo log之间的数据一致性。

sync_binlog=1

指定master_info和replay_log_info信息的存储方式为table。如果MySQL挂了的后，由于Innodb存储引擎的特点，可以对这2张表进行故障恢复，保证slave能从正确的位置进行数据恢复。

master_info_repository=TABLE
relay_log_info_repository=TABLE

可以看到slave_master_info这张表存储着master的binlog以及当前master写入binlog的位置等信息。

mysql> select * from slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.000001
        Master_log_pos: 914
                  Host: 192.168.10.21
             User_name: gtid
         User_password: gtid
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca: 
            Ssl_capath: 
              Ssl_cert: 
            Ssl_cipher: 
               Ssl_key: 
Ssl_verify_server_cert: 0
             Heartbeat: 30
                  Bind: 
    Ignored_server_ids: 0
                  Uuid: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
           Retry_count: 86400
               Ssl_crl: 
           Ssl_crlpath: 
 Enabled_auto_position: 1
          Channel_name: 
           Tls_version: 
1 row in set (0.00 sec)

可以看到slave_relay_log_info记录了slave的relay log的位置、master binlog的名称，master binlog当前偏移量，relay log当前偏移量等信息。

mysql> select * from slave_relay_log_info\G
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./mysql-relay-bin.000004
    Relay_log_pos: 831
  Master_log_name: mysql-bin.000001
   Master_log_pos: 914
        Sql_delay: 0
Number_of_workers: 16
               Id: 1
     Channel_name: 
1 row in set (0.00 sec)

当slave挂了后导致中继日志损坏后，导致一部分中继日志没有处理，则放弃所有未执行的relay log，并且重新从master获取日志，这样就保证了relay log的完整性。

relay_log_recovery=1

这些参数和sync_binlog参数一样,slave的IO线程每次接收到master发送过来的日志都要写入到系统缓冲区，然后再刷到磁盘中。这样master崩溃时，最多丢失一个事务。虽安全，但是会造成大量的磁盘IO。sync_relay_log、sync_relay_log_info、sync_master_info默认值都为10000

sync_relay_log=1
sync_relay_log_info=1
sync_master_info=1

image.png

默认是1，自动清空不需要的中继日志。

relay_log_purge=1

MySQL5.6有基于库的并行复制，可以设置slave-parallel-workers参数设置workers的个数。当开启并行复制功能后，那么SQL线程就变成了Coordinator线程。
不同schema下的表并发提交的数据不会有影响，slave会对relay log中不同schema分配workers线程，来回放relay log中master已经提交的事务，保持数据一致性。如果单实例仅有一个库，开启并行复制功能后，那么就无法实现并行回放，甚至性能会比以前的单线程更差。

在MySQL5.7，引入了基于组提交的并行复制，设置slave-parallel-type=LOGICAL_CLOCK。支持在同一个schema下，支持slave-parallel-worker个worker线程并发回放relay log中master的事务。一个组提交的事务是可以并行回放的。在slave中的relay log中具有相同的last_committed值(sequence_num不同)的事务是属于同一个组的。

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16

image.png

选择复制模式需要考虑的几个点：
1.所使用的MySQL。如果是5.6以上，可以考虑GTID复制。如果是5.6以下的，可以考虑binlog复制。
2.复制架构及主从切换的方式。如果是一主多从，还是推荐GTID复制，不会为新master的binlog偏移量而担心。
3.所使用的高可用管理组件。MMM只支持基于日志的方式。而MHA支持日志、GTID复制。

在MySQL5.7之前，一个从库只能有一个主库。MySQL5.7之后，支持一从多主架构。

一主多从的复制拓扑架构，配置简单，可以用多个从库分担读负载，使数据差异最小化。用途：1.为不同的业务使用不同的从库。2.将一台从库放到远程IDC，用于灾备恢复。3.分担主库的读负载。

主主(双机热备)复制拓扑架构，并不能分担写负载。缺点：1.经常产生数据冲突从而造成复制链路中断。2.耗费大量的时间，造成数据丢失。所以建议两个主库所操作的表最好能够分开，使用auto_increment_increment和auto_increment_offset=1|2参数控制自增ID的生成。

主主(单机热备)复制拓扑架构，只有一台主服务器对外提供服务，另一台服务器处于只读状态并且只作为热备使用。在对外提供服务的主库出现故障或是计划性的维护时才会进行切换。使用这种拓扑架构，需要注意以下几点：
1.确保两台服务器上的初始数据相同。
2.确保两台服务器上已经启动binlog，并且有不同的server_id。
3.在两台服务器上启动log_slave_updates参数。
4.在初始备库上启动ready_only。

影响主从延迟的因素：
1.主库写入到二进制的时间。
2.控制主库的事务大小，分割大事务。
3.二进制日志传输时间取决于传输日志量的多少。推荐使用mixed日志格式。
4.默认情况下从库只有一个sql线程。也就是说master上并发的修改在slave变成了串行。可以采用多线程复制(设置slave-parallel-type=LOGICAL_CLOCK、slave-parallel-workers=16)。

在MySQL主从复制过程中，要注意以下问题：
1.主库意外重启或者主库的二进制文件损坏。我们可以在slave上通过change master命令来重新指定binlog偏移量，同时配置sync_binlog=1使每次写入对binlog进行同步，落地磁盘，减少宕机所丢失的事务数。
2.从库上的中继日志损坏。
3.从库宕机，引起master.info文件没有及时更新到磁盘上。master.info记录着从库同步主库的相关信息，会引起从库重复同步的操作。
4.在从库上进行数据修改造成的主从复制错误。
5.不唯一的server_id或者server_uuid。
6.max_allowed_packet设置引起的主从复制错误。

一般我们可以采用以下几种方法解决主从复制问题：
1.跳过二进制日志文件。
2.注入空事务的方式先恢复被中断的复制链路
再使用其他方法来对比主从服务器上的数据。

MySQL复制无法解决的问题有哪些：
1.分担主数据库的写负载。
2.自行进行故障转移及主从切换。
3.提供读写分离功能。

在这里，我们要可以引出一个概念，高可用。高可用性(HA,High Availability)指的是通过尽量缩短因日常维护操作(计划)和突发的系统崩溃(非计划)所导致的停机时间，以提高系统和应用的可用性。

我们应该避免导致系统不可用的因素，减少系统不可用的时间。
1.严重的主从延迟。
2.主从复制中断。
3.锁引起的大量阻塞。
4.服务器磁盘空间耗尽，比如备份或者各种查询日志快速增长导致磁盘空被占满。或者是MySQL由于无法记录二进制日志，无法处理新的请求而产生的系统不可用的故障。
5.性能糟糕的sql。
6.表结构和索引没有优化。
7.主从数据不一致。
8.人为的操作失败等等。

那如何实现高可用呢？
1.建立完善的监控及报警系统。
2.对备份数据进行恢复测试。
3.正确配置数据库环境。
4.对不需要的数据进行归档和清理。
5.增加系统冗余，保证发生系统不可用时可以尽快恢复。(避免存在单点故障，可以进行主从切换及故障转移)

在这里我们又要引出一个概念，单点故障。它是指在一个系统中提供相同功能的组件只有一个，如果这个组件失效了，就会影响整个系统功能的正常使用，组成应用系统的各个组件都有可能成为单点。那么我们如何避免MySQL单点故障呢？
1.利用SUN共享存储或者DRBD磁盘复制来解决MySQL单点故障。
2.MySQL主从复制(关于主从复制，我们该考虑3个点。一是主服务器切换后，该如何通知应用新master的ip地址。二是如何检查MySQL主服务器是否可用。三是如何处理从服务器和新主服务器之间的那种复制关系)

为了解决MySQL主从复制管理的痛点，才衍生出了MMM，MHA这些高可用的管理组件。在本文章的末尾，简单介绍一下MMM是什么。

MMM的主要作用是监控和管理MySQL的主主复制拓扑结构，并在当前的主服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作。

MMM可以在主库出现宕机时进行故障转移并且自动配置其他从服务器对新主服务器的复制。

使用MMM要考虑两点：一是如何找到从库对应的新主服务器的日志同步点。二是如果存在多个从库出现数据不一致的情况，如何处理。在一个繁忙的系统中，使用MMM有可能会造成数据丢失。

MMM提供了读，写虚拟IP。在主从服务器出现问题时可以自动迁移虚拟IP。

尾言

大家好，我是cmazxiaoma(寓意是沉梦昂志的小马)，感谢各位阅读本文章。
小弟不才。
如果您对这篇文章有什么意见或者错误需要改进的地方,欢迎与我讨论。
如果您觉得还不错的话,希望你们可以点个赞。
希望我的文章对你能有所帮助。
有什么意见、见解或疑惑，欢迎留言讨论。

最后送上：心之所向，素履以往。生如逆旅，一苇以航。

saoqi.png