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.

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:

locally applying

generating a binlog event

sending the binlog event to the slave(s)

adding the binlog event on the relay log

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 node1we add some statements to our transaction…

we commit the transaction and binary log events are generatedthose 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 independentlycertification can start as son as the transaction is receivedwhen 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 clientevery 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 certificationyou 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.

Related

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.

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?

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

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 ?

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