8 core CPU
Slave: MySQL 5.7.22
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.
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
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
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?
SHOW SLAVE STATUS:
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
I'm leaning towards a combination of disk or network so I've been running
iotop. The disk that gets the binlogs written to on the master is always near 100% utilization, but the "wait" is only about 10ms. Still, when I run
scp it runs quickly, so I'm not sure why transferring the binlogs is about 200x slower.
There are also no errors on the Slave side, I'm monitoring the logs. Binlogs are disabled on the slave, and compressed binlogs are enabled.
Queries on the Slave are running fast, it's just that it keeps waiting on new relay logs to be transferred.
show slave statushere.
Seconds_Behind_Master: 0is not a good metric in this case, because it is non-zero only when there's a new relay log file to process. They process quickly so most of the times it's 0, but when there's a file it's around 2.5 days. Also, I can tell by comparing with
Show master status, the log file there is
mysql-bin.285972which a couple hundred files ahead. Also I can tell by running
SELECTqueries on both, checking for the most recent row.