2

I'm experiencing some replication issues. I have a Master-Slave replication with mysql; the slave had binlog-format = ROW, Last sunday (3 days ago) I changed the format to MIXED (this is master too; so I expected better performance for his slave). The thing is, it appeared to work fine sunday and monday; but starting tuesday this slave began to lag and kept falling behind for some hours... It catched up around midnight, and today again from nowhere started to lag and kept behind many hours again; trying to understand what's happening I've been asking around and it seems like the workload is the same like past weeks, nothing new that could be causing this behavior. I noticed that the Disk Usage has incremented considerably lately, which is strange, I thought that this change would cause less disk writes...

Disk Usage

I'm attaching the graphic, you can see a lot of reads december 18, that was some Cold Backup, so it's not that.... The question is: Could the change of binlog-format be the cause of this behavior? Is it normal that it is writing a lot more to disk? And is there a way to improve this?

Thanks

Server1:

Master binlog_format=MIXED

Server2:

Server1's slave binlog_format=MIXED (This is the one changed, and where the I/O is up).

Server3:

Server2's slave

  • Interesting case, i would think that RBR is the most write intensive type of replication, changing to MIXED should lower or equal disk writes. Is there any other recent change that you made to thar particular MySQL instance? – kriegu Dec 22 '16 at 0:37
  • @kriegu There was some changes in the app, that writes to the master, but the replication lag started Tuesday around 12 o'clock, and the changes to the app were applied untill 6 in the afternoon. So no much to look there, I think. – Mauricio Cacho Dec 22 '16 at 12:47
  • Is the graph for Server 2? – Rick James Dec 26 '16 at 18:15
  • SHOW VARIABLES LIKE 'innodb%'; – Rick James Dec 26 '16 at 18:17
  • @RickJames thanks, so the server stopped doing that suddenly on Saturday 24th and it's been working fine since then; looks like there was indeed someone doing major changes to the DB but no one admitted it. Thanks a lot for your support. – Mauricio Cacho Dec 28 '16 at 13:37
0

When a complex UPDATE is run on the master, and it results in only a few rows being updated, then one of these happens:

  • SBR: Re-execute the UPDATE on the Slave. This stalls replication, waiting for the complex query to finish.
  • RBR: Only the changed rows are sent to the Slave. These can be applied much more rapidly.

A simple UPDATE that modifies a million rows:

  • SBR: Again, slow on the slave -- because of all the I/O do update the million rows.
  • RBR: This is also slow, but for a different reason: A million records are sent through the replication stream. This involves I/O at both ends, plus network traffic.

DELETE, INSERT...SELECT, and a few other things have similar characteristics.

I can't be more specific without knowing what, exactly, you were replicating. I hope my examples give you some clues of what could be happening.

  • Thanks for the answer, I understand that, but the change of the binlog_format was in the Server2, and the replication lag and the Disk I/O is in the same server, so it shouldn't affect on how it runs querys comming from his master, since there wasn't any changes, the change was how it writes his own binlog in order to send to his slave. – Mauricio Cacho Dec 23 '16 at 18:23
  • Which machine is Server2? I don't understand "lag and I/O is in same server". – Rick James Dec 23 '16 at 19:57
  • I edited the post to be clearer with my scenario. In the Server2 is where I changed the binlog_format. So it's getting behind from Server1, though there was no changes in binlog_format from Server1. Thanks! – Mauricio Cacho Dec 26 '16 at 13:06

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.