MySQL Replication binary relay logs transfer too slowly

Master server

MySQL 5.6.28

Ubuntu 14.04

8 core CPU

Slave server

Slave: MySQL 5.7.22

Ubuntu 18.04

16 core CPU

Both of them have 64GB of RAM and plenty of disk space.

What I did up to this point

I made a dump of the master database, copied it over to the slave server and set up a Slave database there. The replication works, but it's too slow.

The slave started approx. 2.5 days after the initial dump and isn't catching up. Looking at the relay log files, it seems that they're filling up too slowly (approx 1MB every few seconds). This is on a 100GB+ database.

I've tried

Checked the disk io with iotop - They're good on both the Master and the Slave. They're not SSDs but they don't seem to be the bottleneck.

Checked network speeds with bmon - they're barely scratching the surface. Both machines are on a Gigabit network. I've tried running scp (with the replication in progress) and I'm getting up to 100MB/s transfer. The relay logs seem to be transfering at less than 1MB/s.

Checked the CPU - both servers have plenty to spare.

I made sure the innodb settings are the same on both servers. All tables are innodb.

Looking at SHOW SLAVE STATUS\G I see that most of the time is spent waiting for new relay logs to transfer. There is no delay on the SQL side, it's always caught up.

Worth mentioning that the binlog_format is ROW.

tl;dr:

What could be limiting the speed at which the binlogs are relayed from Master to Slave, if there is still plenty bandwidth, CPU and disk I/O available?

EDIT 1:

Results of SHOW SLAVE STATUS:

`
Slave_IO_State: Waiting for master to send event
Master_Host: master.server
Master_User: sqlslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.285479
Read_Master_Log_Pos: 87535361
Relay_Log_File: slave-relay-bin.001588
Relay_Log_Pos: 87535479
Relay_Master_Log_File

Ok, in my case the constraint was the Disk I/O. The binlogs are written to the hard disk which slows the whole thing down a bit.

The reason I couldn't see this at first was that MySQL was not using the Disk I/O to it's full potential, possibly due to settings or "niceness" of the MySQL process.

In the end it took a few days for the Slave to get caught up, and now it's fine. But potential, better solutions in the future:

• Write Binlogs to the SSD to reduce delays due to Disk I/O

• Try changing MySQL options such as sysvar_innodb_io_capacity https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_io_capacity

• Write to Hard Disk but ones in a RAID setup

• Increase the nice value for the mysql process to give it priority

• Switch to STATEMENT based binlog format, to reduce the binlog size.

One or many of the above might help fix similar issues.

StackExchange Database Administrators Q#213557, answer score: 2