In April 2021, I wrote an article about Online DDL and Group Replication. At that time we were dealing with MySQL 8.0.23 and also opened a bug report which did not have the right answer to the case presented.
Anyhow, in that article I have shown how an online DDL was de facto locking the whole cluster for a very long time even when using the consistency level set to EVENTUAL.
This article is to give justice to the work done by the MySQL/Oracle engineers to correct that annoying inconvenience.
Before going ahead, let us remember how an Online DDL was propagated in a group replication cluster, and identify the differences with what happens now, all with the consistency level set to EVENTUAL (see).
In MySQL 8.0.23 we were having:
 |  |  |
While in MySQL 8.0.27 we have:
|  |  |
As you can see from the images we have three different phases. Phase one is the same between version 8.0.23 and version 8.0.27.
Phases two and three, instead, are quite different. In MySQL 8.0.23 after the DDL is applied on the Primary, it is propagated to the other nodes, but a metalock was also acquired and the control was NOT returned. The result was that not only the session executing the DDL was kept on hold, but also all the other sessions performing modifications.
Only when the operation was over on all secondaries, the DDL was pushed to Binlog and disseminated for Asynchronous replication, lock raised and operation can restart.
Instead, in MySQL 8.0.27, once the operation is over on the primary the DDL is pushed to binlog, disseminated to the secondaries and control returned. The result is that the write operations on primary have no interruption whatsoever and the DDL is distributed to secondary and Asynchronous replication at the same time.
This is a fantastic improvement, available only with consistency level EVENTUAL, but still, fantastic.
Let’s See Some Numbers
To test the operation, I have used the same approach used in the previous tests in the article mentioned above.
1 2 3 4 5 6 7 8 9 10 11 12 | Connection 1: ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=NONE; ALTER TABLE windmills_test drop INDEX idx_1, ALGORITHM=INPLACE; Connection 2: while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/mysql-8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_large -e "insert into windmills_test select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmill7 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done Connection 3: while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/mysql-8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_large -e "insert into windmill8 select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmill7 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done Connections 4-5: while [ 1 = 1 ];do echo "$(date +'%T.%3N')";/opt/mysql_templates/mysql-8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_large -e "show full processlist;"|egrep -i -e "(windmills_test|windmills_large)"|grep -i -v localhost;sleep 1;done |
Modifying a table with ~5 million rows:
1 2 3 4 5 6 | node1-DC1 (root@localhost) [windmills_large]>select count(*) from windmills_test; +----------+ | count(*) | +----------+ | 5002909 | +----------+ |
The numbers below represent the time second/milliseconds taken by the operation to complete. While I was also catching the state of the ALTER on the other node I am not reporting it here given it is not relevant.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | EVENTUAL (on the primary only) ------------------- Node 1 same table: .184 .186 <--- no locking during alter on the same node .184 <snip> .184 .217 <--- moment of commit .186 .186 .186 .185 Node 1 another table : .189 .198 <--- no locking during alter on the same node .188 <snip> .191 .211 <--- moment of commit .194 |
As you can see there is just a very small delay at the moment of commit, but other impacts.
Now if we compare this with the recent tests I have done for Percona XtraDB Cluster (PXC) Non-Blocking operation (see A Look Into Percona XtraDB Cluster Non-Blocking Operation for Online Schema Upgrade) with the same number of rows and same kind of table/data:
| Action | Group Replication | PXC (NBO) |
|---|---|---|
| Time on hold for insert for altering table | ~ 0.217 sec | ~ 120 sec |
| Time on hold for insert for another table | ~ 0.211 sec | ~ 25 sec |
However, yes there is a however, PXC was maintaining consistency between the different nodes during the DDL execution, while MySQL 8.0.27 with Group Replication was postponing consistency on the secondaries, thus Primary and Secondary were not in sync until full DDL finalization on the secondaries.
Conclusions
MySQL 8.0.27 comes with this nice fix that significantly reduces the impact of an online DDL operation on a busy server. But we can still observe a significant misalignment of the data between the nodes when a DDL is executing.
On the other hand, PXC with NBO is a bit more “expensive” in time, but nodes remain aligned all the time.
In the end, is what is more important for you to choose one or the other solution, consistency vs. operational impact.
Great MySQL to all.
Sounds great! Marco, what happens if:
– I run
ADD COLUMN i INT ...– The migration completes on N1, returns
– Meanwhile asynchronously shipped to N2, N3
– I run
UPDATE my_table SET i=27Is that statement queued in N2 & N3 until the DDL is complete?