问题背景:
客户反馈slave每到凌晨就出现延迟现象,需要排查原因
1>首先查看master库每天凌晨有什么操作:
分析binlog日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000204 > mysql-bin.000204.sql
查看日志在凌晨有大量的delete操作
#191126 0:08:35 server id 1 end_log_pos 1073744234 CRC32 0x11bf4f5d Table_map: `user`.`table_name` mapped to number 4012691
# at 1073744234
#191126 0:08:35 server id 1 end_log_pos 1073744393 CRC32 0xa3229214 Delete_rows: table id 4012691 flags: STMT_END_F
### DELETE FROM `user`.`table_name`
### WHERE
### @1=259121
### @2=‘2019-11-25‘
### @3=1
### @4=1
### @5=0
### @6=‘2019-11-25‘
### @7=‘08:30‘
### @8=‘2019-11-25‘
### @9=‘17:30‘
### @10=540
### @11=‘‘
### @12=‘‘
### @13=NULL
### @14=‘‘
### @15=‘‘
### @16=NULL
### @17=0
### @18=0
### @19=0
### @20=0
### @21=0
### @22=540
### @23=0
### @24=0
### @25=0
### @26=0
### @27=0
### @28=‘{}‘
# at 1073744393
#191126 0:08:35 server id 1 end_log_pos 1073744424 CRC32 0x5a03e7aa Xid = 25909247548
2> 判断为大量的delete操作产出大量的binlog日志,slave应用不过来
一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发。简单说,
在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,
复制线程只有一个sql thread用于binlog的apply,所以slave在高并发时会远落后master。
查看slave复制方式
mysql> show variables like"%slave%";
+-------------------------------------------+----------------------+
| Variable_name | Value |
+-------------------------------------------+----------------------+
| init_slave | |
| log_slave_updates | ON |
| log_slow_slave_statements | OFF |
| pseudo_slave_mode | OFF |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | OFF |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 134217728 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | INDEX_SCAN,HASH_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+-------------------------------------------+----------------------+
27 rows in set (0.00 sec)
配置从服务器上的多线程并行复制的参数(此处为实现多线程复制的重要参数)在数据库配置文件 my.cnf中设置
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16 #16为设置的并发线程个数,之后根据项目对数据传输的具体要求再更改
#一个schema下,slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
注:变量slave-parallel-type可以有两个值
DATABASE 为默认值,意为基于库的并行复制方式;
LOGICAL_CLOCK:基于组提交的并行复制方式
1、下面查看复制类型和并行数量配置
mysql> show variables like ‘slave_parallel_type‘;
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.00 sec)
当前的复制类型是 DATABASE,也就是统一数据库下只有一个线程进行复制,不能并行复制。
mysql> show variables like ‘slave_parallel_workers‘;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.01 sec)
当前并行工作的进程数是 0
配置多线程
1、停止从节点复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
2、设置复制类型为 LOGICAL_CLOCK
mysql> set global slave_parallel_type=‘logical_clock‘;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘slave_parallel_type‘;
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.01 sec)
3、设置并行数量为 4
mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘slave_parallel_workers‘;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 4 |
+------------------------+-------+
1 row in set (0.00 sec)
4、启动从节点复制
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
Master库大量delete操作导致slave日志应用延迟
原文:https://www.cnblogs.com/sunkang-dba/p/11946904.html