How to Avoid MariaDB Galera Cluster stalls on ALTER

If you have trouble when upgrading table schema or alter table on MariaDB Galera Cluster, that make your database not response and then connection will over limit.

We have experienced with a situation in which you attempt to execute an ALTER statement on one node, but it takes a long time to execute–longer than expected.

During execution of ALTER statement, periode all of the other nodes stall, leading to performance problems throughout the cluster.

To to avoid MariaDB Galera Cluster stalls on ALTER table, you need to run an ALTER statement in this manner, you will need to execute the ALTER statement between a paid of SET statements on each node like SQL Command below:

SET wsrep_OSU_method='RSU';
ALTER TABLE news ADD COLUMN views INT;
SET wsrep_OSU_method='TOI';

The explaination is the first SQL statement above will change the Schema Upgrade method to Rolling Schema Upgrade (i.e., RSU).

Then the second SQL statement represents an ALTER statement you want to execute.

Once that’s finished, the last statement will reset the Schema Upgrade method back to Total Order Isolation (i.e., TOI).

After you have done this on each node, the cluster will now run with the desired updates.