I presented a talk at Percona Live Amsterdam a few weeks ago (simple recipes for your Galera cluster), and one of the topics was schema changes. I discussed the drawbacks and benefits of Total Order Isolation (TOI), Rolling Schema Upgrades (RSU) and pt-online-schema-change. One thing I forgot was online DDL from 5.6. Does it provide another way to perform online schema changes?

TL;DR

While the online DDL feature of MySQL 5.6 is not modified in PXC, it doesn’t work as one could expect because Galera doesn’t allow concurrent writes when an ALTER TABLE statement is run. So if you are using TOI, any ALTER TABLE will block all writes on all nodes, exactly as it did with PXC 5.5.

If you want an online ALTER TABLE, your best bet is pt-online-schema-change.

Online DDL with TOI

My test was simple: I set up a 3-node cluster, ran sysbench against node1 to insert records on a table and ran a concurrent ALTER TABLE on node2 (adding an index). Here is what the Com_insert status variable indicated during the run:

We can clearly see that while ALTER TABLE is running, all writes are stalled. What happened? If we refer to the online DDL documentation, we could expect that concurrent writes would be allowed.

The reason is that under TOI, Galera simply forbids applying concurrent transactions on the table that is being altered.

Do we see different results if we write to table t2 while changing the schema of table t1? No, we don’t. Again Galera prevents all writes during an ALTER TABLE statement.

As a sidenote, I’ve sometimes seen during my tests that concurrent writes were still working on node1, but not on other nodes. However after a few seconds, flow control kicked in and prevented all writes on the cluster. I’ve not been able to reproduce this behavior predictably, so I’m not sure at this point what happened in this case.

Online DDL with RSU

Online DDL with RSU is not as critical because in this case an ALTER TABLE statement will, at worst, block writes on the node where the schema change is running. And this node will not trigger flow control anyway.

The only good point of potentially allowing concurrent writes would be that replication will catch up faster. This can be important on busy clusters, when you need all nodes to handle the workload.

But again… an ALTER TABLE will block all concurrent writes, and you can’t benefit from the online DDL feature.

Conclusion

PXC 5.6 cannot benefit from the online DDL feature. So when you need to modify the schema of larger tables and when RSU is not an option, pt-online-schema-change is still your best friend.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
takaidohigasi

Hi

This is good article, so I translated the article into Japanese for users in Japan.
Translated one is as follows.

https://yakst.com/ja/posts/3362

If there is any problem, please contact me.
Thank you.

0