5

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 ?

  • 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
3
+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/

  • Thanks for your answer, i've edited the data in the question – edorian Sep 22 '10 at 8:14
1

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.

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

I. Add small but simple IO-optimizations for info-files

sync_relay_log_info=5000000

sync_master_info=5000000

II. Use multi-threading (can be worse results)

slave_parallel_workers=10

log_slave_updates=1

slave_preserve_commit_order=1

slave_parallel_type=LOGICAL_CLOCK

slave_pending_jobs_size_max=1047527424 # should be not less than your max_allowed_packet

  • For activation - restart

III. If you have bandwidth problems try to use compression

slave_compressed_protocol=1

  • For activation "STOP SLAVE; START SLAVE;"

IV. For some environments main point is - there are large IO on relay-log files.

It makes problem even for some SSD drives and of cause it's a killer for HDD.

So, we just move all to RAM and get up to few times performance increase.

  1. Make a RAM drive

For example on /tmp dir

Edit /etc/fstab to have something like:

tmpfs /tmp tmpfs defaults,noatime,nodiratime,nosuid,nodev,mode=1777,size=1500M 0 0

And init it

mount -o remount /tmp

  1. Add to /etc/mysql/my.cnf

relay_log_space_limit=500M # size limit for all log files to save your small tmpfs drive

max_relay_log_size=100M; # need to be at least twice less than space limit

relay-log = /tmp/YOUR_HOSTNAME-relay-bin

master-info-file = /tmp/master.info

relay-log-info-file = /tmp/relay-log.info

Purge\resize current relay log if it's too large before adding new-path statements

  1. Shutdown slave

  2. Move YOUR_HOSTNAME-relay-bin, master.info, relay-log.info from current location (default /var/lib/mysql) to /tmp

    • Don't forget to "chown mysql:mysql" that files

You may but possibly don't want to use this way for a long time due to aftermath of further reboots and rude methods to avoid errors after them (like slave-skip-errors = 1062,1032,1396)

But

  • it is a way to fast catch up straggled case

  • sometimes it's the only way due to lack of resources

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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