11

We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master.

The bandwidth between the datacenter is pretty high (in network benchmarks we've done we can reach 15MB/second), but latency exists, it is around 28ms. It's not high by any means, but it is much higher than the sub-second latency in the same datacenter.

Occasionally, we experience serious lags (2000 seconds and more) with the remove slave, while the local slave stays up to date. When looking at the lagging remote slave, the SQL thread usually spends the time waiting for the IO thread to update the relay log. The master shows "waiting for net" or something of the sort at the same time.

So it means it's network, but we still have free bandwidth at the time this happens.

My question is: can the latency between the datacenters affect the performance of replication? Does the slave io thread just stream the events until the master stops sending them, or is it pooling the master somehow between events?

3
  • 2000 seconds? So, a 33 minute lag?
    – Richard
    Commented Aug 4, 2011 at 16:28
  • Yep... It goes up and down throughout the day.
    – shlomoid
    Commented Aug 5, 2011 at 14:50
  • 2
    +1 because I love these types of questions in this site. Please get the word out for others to come to this site with questions of this nature !!! Commented Aug 5, 2011 at 16:52

3 Answers 3

7

The direct answer to your question is Yes, but it depends on the version of MySQL you are running. Before MySQL 5.5, replication would operate as follows:

  • Master Executes SQL
  • Master Records SQL Event in its Binary Logs
  • Slave Reads SQL Event from Master Binary Logs
  • Slave Stores SQL Event in its Relay Logs via I/O Thread
  • Slave Reads Next SQL Event From Relay Log via SQL Thread
  • Slave Executes SQL
  • Slave Acknowledges Master of the Complete Execution of the SQL Event

As of MySQL 5.5, using Semisynchronous Replication, now replication would operate as follows:

  • Master Executes SQL
  • Master Records SQL Event in its Binary Logs
  • Slave Reads SQL Event from Master Binary Logs
  • Slave Acknowledges Master of the Receipt of the SQL Event
  • Slave Stores SQL Event in its Relay Logs via I/O Thread
  • Slave Reads Next SQL Event From Relay Log via SQL Thread
  • Slave Executes SQL
  • Slave Acknowledges Master of the Complete Execution of the SQL Event

This new paradigm will permit a Slave to be closer sync'd to its Master.

Notwithstanding, latency within the network could hamper MySQL Semisync Replication to the point where it reverts back to the old-style asynchronous replication. Why ? If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.

UPDATE 2011-08-08 14:22 EDT

The configuration of MySQL 5.5 Semisynchronous Replication is straightforward

Step 1) Add these four(4) lines to /etc/my.cnf

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL

service mysql restart

Step 3) Run these commands in the MySQL client

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL

service mysql restart

All Done !!! Now just setup MySQL Replication as usual.

4
  • I am not sure about the last stage of the asynchronous replication - I don't think the master knows how far every slave has come. They can ask for any part of the binary log they want, as far as I know - do you have some reference for this?
    – shlomoid
    Commented Aug 6, 2011 at 8:58
  • Also, we're using the default asynchronous replication in MySQL, not the asynchronous type - which needs to be enabled on purpose by installing plugins and the likes. What I'm trying to understand is whether events are piped net-cat style into the slave from the starting position in the log, or is there back and forth exchange between the master and slave for each event, which could suffer from such latency.
    – shlomoid
    Commented Aug 6, 2011 at 9:01
  • By all means, I highly recommend using MySQL 5.5 to take advantage of this new form of MySQL Replication as well as the enhancements of InnoDB. Commented Aug 7, 2011 at 3:22
  • 1
    Yes, of course we are using MySQL 5.5, but this is not the default replication type. You need to go through a whole configuration procedure, install plugins and such, to get it working in the semi-synchronous way.
    – shlomoid
    Commented Aug 7, 2011 at 6:59
2

I really like how Rolando described the sequence of operations a replication performs. However, I think it would be more clear if we add another component - client.

With client the sequence of operations for asynchronous replication could be the following:

  1. Client sends to the master the SQL query (for example, insert) using transactions

  2. Master executes the transaction. In case of success the record is stored on the disk, but the transaction is not committed yet.

  3. Master records the insertion event in the master binary log If the master could not store it in the binary log, the transaction rolled back.

  4. Client receives response from the master (success or rollback).

  5. In case of transaction success, the dump thread on the master reads the event from the binary log and send it to the slave I/O thread.

  6. Slave I/O thread receives the event and writes it to the end of the relay log file.

  7. Once the event got into relay log, the slave SQL thread executes
    the event to apply the changes to the database on the slave.

In this scenario master does not care about the slave and client only knows that something is wrong on the slave by manually executing "SHOW SLAVE STATUS" command.

n case of a semi-synchronous replication the sequence of operations could be the following:

  1. Client sends to the master the SQL query (for example, insert) using transactions.

  2. Master executes the transaction. In case of success the record is stored on the disk, but the transaction is not committed.

  3. Master records the insertion event in the master binary log If the master could not store it in the binary log, the transaction rolled back and client receives the response only in the case of rollback.

  4. Due to the success of the transaction on the master, the dump thread on the master reads the event from the binary log and send it to the slave I/O thread.

  5. Slave I/O thread receives the event and writes it to the end of the relay log file.

  6. Slave Acknowledges Master of the recording the event in the relay log file.

  7. Master commits the insertion transaction.

  8. Client receives the response from the master (success).

  9. Once the event got into relay log, the slave SQL thread executes
    the event. Master and client don't know whether the execution was successful or not.

The semi-synchronous replication solved one important case when slave or network died and master continued to proceed. Then master dies and you want to restart the old slave as new master just because you fixed that node.

So you started that node as new master, you fixed the old master and now you want to use it as slave. That node still has the data, but if the new slave starts from the position where the new master started there will be duplicate records.

If waiting period is infinite, master binary log position always will be in sync with the slave relay log position assuming that all queries on the slave were successful. How realistic this assumption?

I think it is very realistic. One of the most common case of the slave query failure is "duplicate record". Where the duplicate record came to the slave if master did not have it? It came from wrong position given to the slave to start to replicate. The starting replication position included the record that was already replicated. In case of semi-synchronous replication this situation will not happened.

Jacob Nikom

1

Qualifier: I'm not a MySQL user, so mostly, this is just my research on the internet.

As I'm sure you know, the biggest limitation of MySQL replication is that it's single threaded. So, while the thread is busy sending data to the in-house slave, it won't be able to send data to the remote slave. This is per here.


Per here:

One thing that you need to make sure to do is reduce your transaction time. This lets your replication thread have an opportunity to catch up with what's going on in the database. You want your transactions to be as short as possible.

One way to do this is through query chopping; limit the rows changed by the UPDATE or DELETE through the use of WHERE clauses. If you stick that inside a loop, you can iterate through the list, starting and committing the transaction each time. (UPDATE/DELETE the first third, the second third, then the final third each in its own transaction.) I personally would strongly advise against doing this because you open yourself up to the possibility of the data in the table changing between transactions. But, it is a possibility to improve this performance if you are sure that no one else is messing with the table (and never will).

Another possibility is to not replicate those long running transactions, but rather, run them on both the master (which replicates to the local slave) and then run them on the remote slave separately. This would free up the replication thread so that it's not bogging down to the 30+ minute mark.


Per here:

One final possibility would be to tune the size of your TCP buffers. The goal is to reduce the number of communications that you're making between the master and slave. This could help reduce the latency.

Personally, I would try this if all else fails. I suspect that the problem is more caused by the single threaded replication system rather than a network latency. Networks would normally time out long before the 30 minute mark. (30 minutes?!)


JHammerb's Delicious bookmarks has several links for mysql replication that you may want to check out as well.

I hope that helps.

3
  • 1
    You get a +1 for mentioning how MySQL Replication is single-threaded, but I need to qualify your statement as follows: MySQL Replication is dual threaded using an I/O Thread for downloading SQL Events From Master to Slave and an SQL Thread for processing the SQL Events locally on the Slave. Yet, transmission of the SQL Events is single-threaded, which is contextually correct for this question. Commented Aug 5, 2011 at 16:07
  • 2
    BTW Please do not use LIMIT with UPDATE and DELETE statements because the order of rows being updated or deleted may not be the same on the Slave as is on the Master. If fact, warning messages about this appear something like "Statement Not BinLog-Safe" in the error log. Commented Aug 5, 2011 at 16:13
  • Ooh, good point about not using LIMIT with UPDATE and DELETE. I'll modify my answer to remove that.
    – Richard
    Commented Aug 7, 2011 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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