Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.)

Some data:

  • 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)

Edit:

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 ?

share|improve this question
1  
you've pretty much answered your own question - it's the RAID array. You can experiment with mounting the MySQL data filesystem noatime as well as trying the Deadline scheduler, as well as disabling journalling if you're using ext3/4 to using a whole different filesystem type (xfs, etc.). Beyond that, you need to get a better RAID controller - in other words, start upgrading hardware. –  troyengel Sep 22 '10 at 13:35
    
Yeah @troyengel, i just really wanted to make sure that i didn't miss any other options (config settings or something) before going to the hardware level and then spending that amount of money –  edorian Sep 23 '10 at 10:38

2 Answers 2

up vote 2 down vote accepted
+50

This would be interesting to write down while catching up:

  • how much bandwidth is used

  • how much cpu utilization (especially i/o wait)

  • ram usage

In response to the new information about i/o wait being high; for innodb a lot can be done, take a look at. I've learned many things from mysqlperformanceblog. Here's some hints:

innodb_flush_method=O_DIRECT

"Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer."

innodb_flush_log_at_trx_commit=2

"If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions."

Those did WONDERS for us, but the drawback is that you might loose a second of written data. This is because instead of write (flush to disk) every record, you flush every second.

You can read more on: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

share|improve this answer
    
Thanks for your answer, i've edited the data in the question –  edorian Sep 22 '10 at 8:14

MySQL replication is fast, really fast. It's primary limitation is the link layer and then the rest of the hardware.

In your case, resuming replication will probably show the binary logs or slave IO catching up fairly quickly. If not, improve your link first. Otherwise, if it is the SQL, you're going to have the physical limitations of the server, which is going to vary between disk IO, RAM, and CPU depending upon the type of load.

share|improve this answer
    
Good answer. I would say it's probably disk in this case. CPU or RAM sounds unlikely when the slave is not used for running selects, joins etc. –  3molo Sep 21 '10 at 12:34
    
Thanks for you answer (little late, i know ;) ). Yes, the relay logs catch up to the masters binlogs in a matter of minutes so i guess that tells me that the network is fast enough –  edorian Sep 22 '10 at 8:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.