Categories: Database

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:

Related Post
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.

Recent Posts

Auto renew Let’s Encrypt SSL Certificate using Systemd and restart Nginx / Apache if success

On other post, we create an article how to obtain Let's Encrypt SSL Certificate on…

3 weeks ago

How to obtain Let’s Encrypt SSL Certificate for Apache or Nginx using Certbot

Now SSL is one of SEO (Search Engine Optimization) signal. Read it on Google Webmaster…

3 weeks ago

How to check UDP connection on Linux using Conntrack

This linux command line to check UDP connection on Linux using Conntrack command. # conntrack…

4 months ago

How to auto sync modified file to another server using Lsyncd on Centos 7

Lsyncd watches a local directory trees event monitor interface (inotify or fsevents). It aggregates and…

4 months ago

CSF Error: *WARNING* Binary location for [HOST] [/usr/bin/host] in /etc/csf/csf.conf is either incorrect, is not installed or is not executable

If you have error warning on CSF (ConfigServer Security & Firewall) on Linux servers when…

5 months ago

How to SELinux allow Apache or PHP-FPM connect remote MySQL

If you have trouble apache or PHP-FPM can not connect to remote MySQSL server, example…

6 months ago