一、描述
MySQL 5.7版本主从复制，批量时候显示延迟上万秒。

二、现象

1、io使用率高#iostat -dxm 1 1000Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz awaitsvctm%utilscd00.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00vda 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00vdb 0.0096.000.00 2596.00 0.00 8.54 6.74 1.330.51 0.3795.30vdc 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00vdd 0.00 0.000.00 11.00 0.00 0.0611.64 0.000.09 0.09 0.10vde 0.00 0.000.007.00 0.00 0.00 0.00 0.000.00 0.00 0.00vdf 0.00 0.000.00511.00 0.00 0.00 0.00 0.050.09 0.09 4.60vdg 0.00 0.000.00511.00 0.00 0.00 0.00 0.050.09 0.09 4.80dm-00.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00dm-10.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00dm-20.00 0.000.00 34.00 0.00 0.2313.65 0.020.59 0.38 1.30dm-30.00 0.000.00 2144.00 0.00 8.38 8.00 1.400.65 0.4597.20dm-40.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00dm-50.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.002、dm3是relay log 和binlog分区$ ls -l /dev/mappertotal 0lrwxrwxrwx 1 root root7 Jul 23 23:20 backup-backup -> ../dm-0crw-rw---- 1 root root 10, 58 Jul 23 23:20 controllrwxrwxrwx 1 root root7 Jul 23 23:20 VG00-lv_root -> ../dm-4lrwxrwxrwx 1 root root7 Jul 23 23:20 zxmysql-zxdba -> ../dm-1lrwxrwxrwx 1 root root7 Jul 23 23:20 zxmysql-zxlog -> ../dm-33、slave状态mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log略.........................................Connect_Retry: 60Master_Log_File: mysql-bin.011494Read_Master_Log_Pos: 21037034 Relay_Log_File: relay-log.001904Relay_Log_Pos: 3154097Relay_Master_Log_File: mysql-bin.011494 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:Last_Errno: 0 Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 3153884Relay_Log_Space: 21037535Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File:Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher:Master_SSL_Key: Seconds_Behind_Master: 471Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids:Master_Server_Id: 400011Master_UUID: 0f8507ea-6da1-11e8-8646-005056873c4a Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Reading event from the relay log Master_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 0f8507ea-6da1-11e8-8646-005056873c4a:14137114-19288497Executed_Gtid_Set: 0f8507ea-6da1-11e8-8646-005056873c4a:1-19288446Auto_Position: 1 Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version: 1 row in set (0.01 sec)ERROR: No query specified

三、分析
通过以上现象发现备库io使用率过高，超过90%。io过高的磁盘为日志盘，存放relay log和binlog。io thead一致在写relay log，调用fdatasync写磁盘。这里涉及到一个参数sync_relay_log，默认值为10000，查看当前系统参数值为1.

四、解决方案
优化io thread线程和sql thread线程。sync_relay_log使用默认值，使用mts优化sql thread。

stop slave;set global slave_parallel_type=logical_clock;set global slave_parallel_workers=8;set global sync_master_info=10000;set global sync_relay_log=10000;set global sync_relay_log_info=10000;start slave;