Master库大量delete操作导致slave日志应用延迟

问题背景： 客户反馈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)