The solution of Galera Cluster crater

After using Galera Cluster, some students will find some pits, such as:

1) the large table DDL operation will cause the whole cluster to be unavailable. No transactions can be written to the cluster until the DDL operation is completed, making the service unavailable.

Solution:

You can avoid this problem by using the pt-online-schema tool directly.

2) because Galera Cluster is Total Ordered Isolation (wsrep_OSU_method=TOI) when executing DDL, it is necessary to ensure that each node is executed simultaneously, of course, for those that are not DDL, it is also Total Order, because every transaction has the same GTID value, DDL is no exception, and DDL involves table locks, MDL locks (Meta Data Lock). As long as conflicts of MDL locks are encountered during execution, DDL takes precedence in all cases. Kill all transactions that use this object, whether read transaction or write transaction, the killed transaction will report a deadlock exception, so this is also a famous pit about DDL in Galera Cluster.

Solution:

You can use a rolling upgrade to perform a separate DDL operation on each node, so that the above problems can be avoided. Do the following:

SET wsrep_OSU_method='RSU'

ALTER TABLE test ADD COLUMN user_age tinyint

SET wsrep_OSU_method='TOI'

That is, first modify the instance upgrade method on the node to rolling upgrade (Rolling Schema Upgrade), then execute the DDL statement, and finally modify the instance upgrade method back.