Shulou(Shulou.com)06/01 Report--

I don't know if you know anything about the similar MySQL5.7 replication delay. Today I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I'm sure you'll get something after reading the MySQL5.7 replication delay.

I. description

Master-slave replication of MySQL version 5.7, with a delay of tens of thousands of seconds in batch display.

II. Phenomenon

1 、 High utilization rate of io # iostat-dxm 1 1000Device: rrqm/s wrqm/s r 0.00vda s rMB/s wMB/s avgrq-sz avgqu-sz await svctm% utilscd0 0.00 0.00 0.00 . 00 0.00 0.00 0.00 0.00vdb 0.00 96.00 0.00 2596.00 0.00 8.54 6.74 1.33 0.51 0.37 95.30vdc 0.00 0.00 0.00 0 . 00 0.00 0.00 0.00 0.00vdd 0.00 0.00 0.00 11.00 0.00 0.06 11.64 0.00 0.09 0.09 0.10vde 0.00 0.00 0.00 7.00 0.00 0.00 0. 00 0.00 0.00 0.00 0.00vdf 0.00 0.00 0.00 511.00 0.00 0.00 0.00 0.05 0.09 0.09 4.60vdg 0.00 0.00 0.00 511.00 0.00 0.00 0.00 0.05 0.09 0 . 09 4.80dm-0 0.00 0.00 0.00 0.00dm-1 0.00 0.00 0.00 0.00dm-2 0.00 0.00 0.00 34.00 0.00 0.23 13.65 0.02 0.59 0.38 1.30dm-3 0.00 0.00 0.00 2144.00 0.00 8.38 8.00 1.40 0.65 0.45 97.20dm-4 0.00 0.00 0.00 0.00 0.00dm-5 0.00 0.00 0.00 0.002 、 Dm3 is the relay log and binlog partition $ls-l / dev/mappertotal 0lrwxrwxrwx 1 root root 7 Jul 23 23:20 backup-backup->.. / dm-0crw-rw---- 1 root root 10 58 Jul 23 23:20 controllrwxrwxrwx 1 root root 7 Jul 23 23:20 VG00-lv_root->.. / dm-4lrwxrwxrwx 1 root root 7 Jul 23 23:20 zxmysql-zxdba->.. / dm-1lrwxrwxrwx 1 root root 7 Jul 23 23:20 zxmysql-zxlog->.. / dm-33, slave status mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Queueing master event to the relay log slightly.. Connect_Retry: 60 Master_Log_File: mysql-bin.011494 Read_Master_Log_Pos: 21037034 Relay_Log_File: relay-log.001904 Relay_Log_Pos: 3154097 Relay_Master_Log_File: mysql-bin.011494 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_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: 0 Exec_Master_Log_Pos: 3153884 Relay_Log_Space: 21037535 Until_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 400011 Master_UUID: 0f8507ea-6da1-11e8-8646-005056873c4a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Reading event from the relay log Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 0f8507ea-6da1-11e8-8646-005056873c4a:14137114-19288497 Executed_Gtid_Set: 0f8507ea-6da1-11e8-8646-005056873c4a:1-19288446 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.01 sec) ERROR: No query specified

III. Analysis

Through the above phenomenon, it is found that the utilization rate of standby io is too high, more than 90%. The disk with too high io is the log disk, which stores relay log and binlog. Io thead is consistently writing relay log, calling fdatasync to write to disk. Here involves a parameter sync_relay_log, the default value is 10000, look at the current system parameter value is 1.

IV. Solution

Optimize io thread and sql thread threads. Sync_relay_log uses default values and mts optimizes 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

Is there any way to solve the MySQL5.7 replication delay after reading this article? what do you think? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.