Online DDL with Group Replication In Percona Server for MySQL 8.0.22 (and MySQL 8.0.23)

Online DDL with Group Replication MySQLWhile I was working on my grFailOver POC, I have also done some additional parallel testing. One of them was to see how online DDL is executed inside a Group Replication cluster.

The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Checking the Group Replication (GR) official documentation, I was trying to identify if any limitation exists, but the only thing I have found was this:

“Concurrent DDL versus DML Operations.  Concurrent data definition statements and data manipulation statements executing against the same object but on different servers is not supported when using multi-primary mode. During execution of Data Definition Language (DDL) statements on an object, executing concurrent Data Manipulation Language (DML) on the same object but on a different server instance has the risk of conflicting DDL executing on different instances not being detected.”

This impacts only when you have a multi-primary scenario, which is NOT recommended and not my case. So, in theory, GR should be able to handle the online DDL without problems. 

My scenario:

group replication MySQL

I have two DCs and I am going to do actions on my DC1 and see how it propagates all over, and what impact it will have.

The Test

To do the test, I will run and insert from select. 

And a select, on my Primary node gr1, while on another connection execute the ALTER:

As you may have noticed, I am EXPLICITLY asking for INPLACE and lock NONE. So in this case, MySQL cannot satisfy;  it should exit and not execute the command.

In the meantime, on all other nodes, I will run a check command to see WHEN my ALTER is taking place. Let us roll the ball:

On my Primary, the command to insert the data:

Again on Primary another session to execute the ALTER:

On other nodes to monitor when ALTER will start:

What Happens

Data is inserted by the loop.

Group Replication MySQL ALTER

ALTER starts, but I can still insert data in my table, and most importantly, the data is propagated to all nodes of the DC1 cluster.

No ALTER action on the other nodes.

Once ALTER is complete on the local node (Primary) it is then executed (broadcast) to all the nodes participating in the cluster.

But writes are suspended, waiting for:

And eventually, it will timeout.

The other point is that any write hangs until the slowest node had applied the ALTER. It is important to note that all nodes, not only the PRIMARY, remain pending waiting for the slow node: the slowest drives all.

GR3:

GR2:

Finally, when the last node in the GR cluster has applied the ALTER, the writes will resume, and the Replica node on DC2 will start its ALTER operation on PRIMARY first, then on the other nodes.

Summarizing:

  1. Writes are executed on Primary
  2. ALTER is executed on the Primary
    • DDL does not impact the write operation and respects the not blocking directive.
  3. ALTER is completed on Primary and passed to all nodes
    • Meta lock is raised on nodes
  4. ALL cluster waits for the slowest node to complete
  5. When all is done in the DC1 then the action is replicated to DC2
    • Goto point 2

Conclusion

It seems that at the moment we have partial coverage of the online ddl feature when using group_replication. Of course, to have to wait for the SECONDARY nodes is better and less impacting than to wait for PRIMARY first and then the SECONDARIES.

But it is confusing, given I was expecting to have either full online coverage (I had explicitly asked for that in the DDL command) or a message telling me it cannot be executed online.  Of course, I would prefer to have FULL online coverage. ;0) 

Keep in mind my setup was also pretty standard and that changing group_replication_consistency does not affect the outcome. But not sure I can classify this as a bug, more an unexpected undesirable behavior.

Share this post

Comments (7)

  • Shlomi Noach Reply

    Marco, thanks for this information! I’m curious: what happens if the migration completes on the PRIMARY, and one second later you kill the PRIMARY? Meaning, secondaries both began to run the replicated ALTER, but are blocking INSERTs. So, if you kill the PRIMARY, do all writes make it to the secondaries and will be eventually applied, or are INSERTs lost?

    April 18, 2021 at 8:18 am
    • Marco Reply

      Interesting question, I will comeback to you later with an answer. Did not track that specific one.

      April 19, 2021 at 3:14 am
    • Marco (the Grinch) Tusa Reply

      Shlomi,

      Results:

      After Primary had complete the local operation the SECONDARY nodes start the alter.
      – Write is on hold on Primary
      – Kill primary
      – SECONDARY NODES not able to elect Primary while applying the ALTER locally.
      – Writes is still locked (NO PRimary elected)
      – First SECONDARY complete the ALTER and BECOME PRIMARY, but remain locked by metalock
      – Second Secondary complete ALTER
      – Lock is raised
      – Writes continue.

      So we have still the lock but DATA inserted during the ONLINE segment on Primary is replicated correctly, then table is modified on SECONDARY nodes as expected and cluster continue without problems.

      April 19, 2021 at 10:02 am
      • Shlomi Noach Reply

        Thank you Marco! So, the system is leaderless for the duration of the ALTER on secondaries, because none of the secondaries would step up as primary, until all (both?) of them complete the ALTER, if I understand correctly. Meaning, if a 10 hour ALTER completes on the primary, the next 10 hours are an availability risk: if the primary goes down during those next 10 hours, the system is effectively read-only, again, if I understand correctly. Super interesting!

        April 19, 2021 at 1:07 pm
        • Marco Tusa Reply

          Correct.
          Anyhow from the tests I did, the fastest secondary will become Primary. But then wait for the metalock given the remaining secondary is still processing.

          April 19, 2021 at 1:51 pm
  • Ivan Baldo Reply

    Thanks for sharing!
    I respectfully disagree though, for me this is no doubt a bug.
    It should at least be documented and a warning raised…
    Hope it could be solved in the future, adding indexes is fairly common.

    April 18, 2021 at 11:47 am
    • Marco (the Grinch) Tusa Reply

      Ivan, if you refer to the fact we do not get an error when specifically asking for “ALGORITHM=INPLACE, LOCK=NONE” then I agree. We should get a message saying the Onlne DDL cannot be executed following our request.
      Said that the way DDL is processed (ONLINE on the primary) then LOCK has i