i'm looking for pointers on how to improve the replication speed of a mysql slave. It is fast enough for normal production but needs a long time to catch up if it falls behind for some reason. (If the server was turned off for or replication stopped for some hours etc.)
- The database is around 2TB and the binlogs written daily are around 500GB.
- For normal production use the slave lag stays at 0 or 1 seconds
- While trying to catch up it come closer to master at around 1 second per second. (So catching up 10 hours takes 10 hours)
- The Slave is ONLY replaying the data as a failover machine, there are no selects running against it and it should be able to replace the master if he dies (everything we be slow, but thats ok)
- The servers are in the same physical network
What can be done to improve that speed ?
(Yes, improving the hardware is an option but we want to make sure we checked other options before spending lots of money)
Some data in response to @3molo question:
- Bandwith usage while catching up: ~2MB/s to 10MB/s, no problem there i guess.
- CPU usage is low, io wait is high (slave only has 2 cpus, arround 70 to 85% percent io wait while catching up. So this means the bottleneck is in the disks ?
- Ram usage is 1,5GB used of 4GB (the rest is 'cached'). So i don't see that much potential there ? (Since it's only write load anyways (?))
If the bottleneck is the disks (6 disk raid 10 setup) are there any options besides increasing the raid or do we have to go with that ?