0

We're running a MySQL master-slave replication set-up and the slave is very slow at catching up. There seems to be no high network or cpu usage while the slave is going through the masters' logs. Connection between master and slave is stable and fast (15 MB/s avg.) and the slaves' cpu-usage does not exceed 2 % on every core (quad core cpu).

We've done some testing by letting the slave catch up until its on the same position as the master and then executing a lot of queries. While the masters' cpu-usage goes way up, the slave is actually doing a very poor job at getting the queries from the master. The Relay_Log_Pos is increasing at a very slow tempo and Exec_Master_Log_Pos is very close to the Relay_Log_pos.

To me it seems to be networking related as the Relay_Log_Pos does not increase very fast, causing the slave to not have many queries to execute, or am I missing something here?

  • IO contention? How much time are the slave's CPU cores spending waiting for IO? – EEAA Dec 31 '15 at 23:45
  • iowait has an average of 46.94 % and a maximum of 96.32 % while the slave is working. I also have to mention that these values are measured by Munin and the maximum cpu is 400 % (96 % is not full cpu usage of course). – Qlii256 Dec 31 '15 at 23:52
  • 1
    Well, you've found your problem. If this behavior is problematic for you, you'll need to improve the slave's storage subsystem. – EEAA Dec 31 '15 at 23:52
  • 2
    Since this is a slave, SET GLOBAL innodb_flush_log_at_trx_commit = 2 should be a reasonable option on the slave... have you tried that? It can definitely be the difference between a slave that can't keep up, and one that can, when I/O blocking is an issue. dev.mysql.com/doc/refman/5.6/en/… – Michael - sqlbot Jan 1 '16 at 4:22
  • 1
    read about crash safe slaves, but in all honesty as long as you have the master you can always rebuild slave or resync slave with pt-table-checksum/pt-table-sync – jerichorivera Jan 1 '16 at 12:32
1

45%-96% iowait is quite high.

You'll need to improve your disk write speeds somehow. You could purchase faster disk or upgrade to a RAID controller that has a larger amount of write cache.

  • I was afraid of that. I've tried enable cache=writeback (the slave is a qemu-kvm virtual machine), but that does not really speed things up. I guess the host will need ssl drives or just faster disks overall... – Qlii256 Jan 1 '16 at 1:03

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.