MySQL(五) 主从复制 – 开启并发SQL线程复制

MySQL 主从复制 – 开启并发SQL线程复制

之前客户做实施的过程中由于用户量暴增，给数据库扩容并做读写分离，但在增加从哭的过程中遇到了一些问题，每天数据库写入的数据量有点大，导致增加从库后数据越追差的越多，果断的调整了一些参数并增加了多线程复制。

单独重启IO/SQL线程：

# 关闭从库的 IO线程
mysql> stop slave io_thread;
Quer
# 开启从库的 IO线程
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
# 关闭从库的 SQL线程
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

# 开启从库的 SQL线程
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

MySQL-SQL多线程复制：

• slave_parallel_workers: 该参数决定了slave上启动多个SQL thread线程来并行应用数据的。默认值是0代表不允许并行，取值范围可以是0~1024。

查看默认SQL线程配置：

# 默认不允许 SQL线程并行运行
mysql> show variables like '%slave_parallel_w%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

配置多线程：

vim /usr/local/mysql/conf/my.cnf

[mysqld]
slave_parallel-type=LOGICAL_CLOCK
#DATABASE 默认值，基于库的并行复制方式
#LOGICAL_CLOCK：基于组提交的并行复制方式，MySQL5.7特性，5.6 不支持
slave-parallel-workers=5
#并行的SQL线程数量，此参数只有设置   1<N的情况下才会才起N个线程进行SQL重做。
#经过测试对比发现， 如果主库的连接线程为M， 只有M < N的情况下， 备库的延迟才可以完全避免。
#否则，延迟一样的会存在，但是毕竟SQL重做线程从原来的一个，升级到现在的N个， 这个延迟在一定的主库TPS下会很短
master_info_repository=TABLE
relay_log_info_repository=TABLE
#两个参数的值有2种设置类型： file,table,将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。当设置为table 后，之前的文件：master.info、relay-log.info 将被删除消失。代之的是两个表，mysql.slave_master_info、mysql.slave_relay_log_info可以在表中进行查询相关信息。。
relay_log_recovery=ON
#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这#样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

查看修改后参数：

# 可以看到有一个 IO线程 和 5个 SQL线程
mysql> show processlist;
+----+-----------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User            | Host      | db    | Command | Time | State                                                  | Info             |
+----+-----------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
|  4 | event_scheduler | localhost | NULL  | Daemon  | 1572 | Waiting on empty queue                                 | NULL             |
|  8 | root            | localhost | mysql | Query   |    0 | starting                                               | show processlist |
| 37 | system user     |           | NULL  | Connect |   79 | Waiting for master to send event                       | NULL             |
| 38 | system user     |           | NULL  | Query   |   79 | Slave has read all relay log; waiting for more updates | NULL             |
| 39 | system user     |           | NULL  | Connect |   79 | Waiting for an event from Coordinator                  | NULL             |
| 40 | system user     |           | NULL  | Connect |   79 | Waiting for an event from Coordinator                  | NULL             |
| 41 | system user     |           | NULL  | Connect |   79 | Waiting for an event from Coordinator                  | NULL             |
| 42 | system user     |           | NULL  | Connect |   79 | Waiting for an event from Coordinator                  | NULL             |
| 43 | system user     |           | NULL  | Connect |   79 | Waiting for an event from Coordinator                  | NULL             |
+----+-----------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
9 rows in set (0.00 sec)

其他一些延迟优化方法：

• 增大从库参数innodb_buffer_pool_size的值，可以缓存更多数据，减少由于转换导致的IO压力。
• 增大参数innodb_log_file_size,innodb_log_file_in_group的值，减少buffer pool的磁盘IO，提升写入性能。
• 修改参数innodb_flush_method为 O_DIRECT，提升写入性能（在ssd下，或者磁盘IO能力强的时候推荐使用）.
• 如果可以的话，把从库binlog关掉，或者关掉参数log_slave_updates.
• 修改参数innodb_flush_log_at_trx_commit为0 或者2.
• 如果binlog没有关掉，修改sync_binlog参数为0或一个很大的数，减少磁盘压力。
• 如果binlog_format为ROW模式，并且被修改表没有主键，则需要加上主键。
• 如果binlog_format为ROW模式，则可以在从库中删掉一些不必要的索引，同步之后在加上。
• 了解清楚写库上的操作内容，适当地在从库中预热以下数据，可以减少在复制时等待的时间。
• 如果binlog_format为STATEMENT模式，或者存在DDL复制，则可以将tmpdir参数改到内存中，比如/dev/shm
• 修改参数master_info_info_repository,relay_log_info_repository为table，减少直接IO导致的磁盘压力。
• 升级硬件。