MySQL Group Replication… synchronous or asynchronous replication ?

After some feedback we received from early adopters or discussions during events like FOSDEM, I realized that there is some misconception about the type of replication that MySQL Group Replication is using. And even experts can be confused as Vadim’s blog post illustrated it.

So, is MySQL Group Replication asynchronous or synchronous ??

… in fact it depends !

The short answer is that GR is asynchronous. The confusion here can be explained by the comparison with Galera that claims to be synchronous or virtually synchronous depending where and who claims it (Synchronous multi-master replication library, synchronous replication,  scalable synchronous replication solution, enables applications requiring synchronous replication of data, …) . But GR and Galera are not more synchronous than the other.

The more detailed answer is that it depends what do you call “replication”. In fact for years, in the MySQL world, replication defined the process of writing (or changing or deleting) data to a master and the appearance of that data on the slave. The full process is what we called replication. The fact of writing data on a master, adding that change in the binary log, sending it on the relay log of a slave and the slave applying that change… So “replication” is in fact 5 different steps:

  1. locally applying
  2. generating a binlog event
  3. sending the binlog event to the slave(s)
  4. adding the binlog event on the relay log
  5. applying the binlog event from the relay log

And indeed, in MySQL Group Replication and in Galera (even if binlog and relay log files are mostly replace by the galera cache), only the step #3 is synchronous… and in fact this step is the streaming of the binlog event (write set) to the slave(s)… the replication of the data to the other nodes.

So yes the process of sending (replicating, streaming) the data to the other nodes is synchronous. But the applying of these changes is still completely asynchronous.

For example if you create a large transaction (which is not recommended neither in InnoDB, Galera and Group Replication) that modifies a huge amount of records, when the transaction is committed, a huge binlog event is created and streamed everywhere. As soon as the other nodes of the cluster/group acknowledge the reception of the binlog event, the node where the transaction was created returns “success” to the client and the data on that particular node is ready. Meanwhile all the other nodes need to process the huge binlog and make all the necessary data modification…. and this can take a lot of time. So yes, if you try to read the data that is part of that huge transaction on another node than the one where the write was done… the data won’t be there immediately. Bigger is the transaction longer you will have to wait for your data to be applied on the slave(s).

Let’s check with some pictures to try to make this more clear, considering the vertical axis is Time, :

We have a MySQL Group Replication cluster of 3 nodes and we start a transaction on node1
we add some statements to our transaction…

we commit the transaction and  binary log events are generated
those binlog events are streamed/delivered synchronously to the other nodes and as soon as everybody (*) ack the reception of the binlog events, each node starts certifying them as soon as they can… but independently
certification can start as son as the transaction is received
when certification is done, on the writer, there is no need to wait for anything else from the other nodes and the commit result is sent back to the client
every other nodes consume from the apply queue the changes and start to apply them locally. This is again an asynchronous process like it was for certification
you can see that the transaction is committed on every node at different time

If you perform a lot of large transactions and you want to avoid inconsistent reads, with MySQL Group Replication, you need to wait by yourself and check if there is still some transaction to apply in the queue or verify the last GTID executed to know if the data you modified is present or not where you try to read it. By default this is the same with Galera. However, Galera implemented sync_wait that force the client to wait (until a timeout) for all the transaction in the apply queue to be executed before the current one.

The only synchronous replication solution for the moment is still MySQL Cluster, aka NDB.

 

(*) on Group Replication majority is enough.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

18 Comments

  1. Hi,
    If I understand correctly, GR is asynchronous in applying transactions, but synchronous in bin log events transfer.
    Isn’t “semi-sync” better description for GR then? Of course it should not be confused with semi-sync replication plugin.
    BTW, here is described the differences between replication types: async, semi-sinc and “semi-sync” group replication: https://dev.mysql.com/doc/refman/5.7/en/group-replication-replication-technologies.html

    • Hi Pavel, thank you for comment. GR can be seen like that, but don’t forget there is many other aspects to take in consideration. This is a high level simplification to try to explain easily why it’s not “synchronous replication”. Certification, flow control, quorum, etc… all that is also part of the solution which is inexistant in semi-sync.

  2. Fred,

    PXC and other Galera Replications have a mode where selects on the nodes wait for writes to be propagated to ensure there are no stale reads. Does Group Replication have the same option ?

    • Hi Peter,

      Thank you for your comment. Unfortunately GR doesn’t have this feature yet but we understand the need. Our development team is working on a solution.

  3. Fred,

    I am not confused, my point is that without sync_wait or other mechanism to ensure that you are not reading stale data – it is impractical to use Group Replication.
    How do you propose for the client that reads from Node2 to be sure that it is using most recent data?

    • Hi Vadim,

      I understand your need and it was not my goal to say you are confused 😉 And to be honest I was surprised about the bug you entered instead of a feature request 😀

      So we are working on some solution to have this in the server, currently you have two options:
      – with the connector, as in the OK packet you receive when you write a transaction, you also get the GTID of that trx. So if you really need to read back that data, you can check the last gtid executed. But this requires an application change.
      – with the application, you can check the apply queue lenght but this is not really precise

      Thank you for evaluating GR

  4. Hi,
    When Node 2 is applying the big transaction(1), it is possible to write another transaction (2) on the node 2 immediately or the transaction (2) will wait that the first big transaction(1) finish ?

  6. Say we have a 3 node cluster in single primary node. Node 1 is the master. Then we have the following:

    Node 1 commits a transaction
    Node 3 certifies it.
    Node 2 has not certified it.
    Node 1 finailizes the commit
    Node 1 fails
    Failover to Node 2.

    At this point node 2 has not certified the transaction from node 1, but node 1 is gone. Will node 2 receive the data and proceed with certification from node 3?

    If so, what is the proper way to to monitor node 2 to ensure it has received that transaction?

  9. I don’t know whether it’s just me or if everybody else experiencing problems with your blog.
    It looks like some of the written text on your content are running
    off the screen. Can somebody else please comment and let
    me know if this is happening to them as well? This could be a
    problem with my browser because I’ve had this
    happen previously. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue