Performing Online Schema Changes with Galera Cluster

Topics on This Page:

Supported Methods for Online Schema Changes
Non-Blocking Operations
Dynamically Changing an Operation's Online Schema Change Method
Dynamically Changing the Server's Online Schema Change Method
Configuring the the Online Schema Change Method

Galera Cluster has multiple supported methods for performing online schema changes.

Supported Methods for Online Schema Changes

The method used for online schema changes is configured by the wsrep_osu_method system variable. It has the following supported values:

Value	Description
`TOI`	This method uses "Total Order Isolation" mode. In this mode, the DDL statement only needs to be executed on a single cluster node, and the cluster node will replicate it to all other cluster nodes. All of the cluster nodes will execute the DDL statement at the same location in the replication stream to ensure consistency. This method is very safe, but it can result in more locking than other modes. This is the default mode.
`RSU`	This method uses "Rolling Schema Upgrade" mode. In this mode, the DDL statement needs to be executed on each cluster node separately. If the DDL statement causes the table to change in a way that is not backward-compatible for replication purposes, then this mode can lead to replication errors. Therefore, it is only recommended to use this mode if the specific schema change is backward-compatible for replication purposes or if write activity for the specific table has been stopped.
`NBO`	This method uses "Non-Blocking Operations" mode. In this mode, the DDL statement only needs to be executed on a single cluster node, and the cluster node will replicate it to all other cluster nodes. All of the cluster nodes will execute the DDL statement at the same location in the replication stream to ensure consistency. In this way, it is similar to `TOI`. However, this mode uses much more efficient locking for some operations than `TOI` does. This mode is only supported in MariaDB Enterprise Server 10.5.

Non-Blocking Operations

In MariaDB Enterprise Server 10.5, the wsrep_osu_method system variable can be set to NBO to use the "Non-Blocking Operations" mode.

This mode is similar to "Total Order Isolation" (or TOI) mode. However, in the "Non-Blocking Operations" (NBO) mode, the following operations use much more efficient locking:

ALTER TABLE operations executed in SHARED or EXCLUSIVE locking mode.

CREATE INDEX operations executed in SHARED or EXCLUSIVE locking mode.

DROP INDEX operations.

OPTIMIZE TABLE operations.

Dynamically Changing an Operation's Online Schema Change Method

Galera Cluster's online schema change method for a specific operation can be changed dynamically by setting the session value of the wsrep_osu_method.

Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:
```
$ sudo mariadb
```
Set the wsrep_osu_method system variable to the new size using the SET SESSION statement.

For example:
```
SET SESSION wsrep_osu_method='NBO';
```
Perform the DDL operation in the same session.

For example:
```
CREATE INDEX ON hq_sales.invoices (branch_id);
```

Dynamically Changing the Server's Online Schema Change Method

Galera Cluster's online schema change method for the entire server can be changed dynamically by setting the global value of the wsrep_osu_method. To ensure that the change survives server restarts, the change should also be made in a configuration file.

Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:
```
$ sudo mariadb
```
Set the wsrep_osu_method system variable to the new size using the SET GLOBAL statement.

For example:
```
SET GLOBAL wsrep_osu_method='NBO';
```
Choose a configuration file in which to configure the wsrep_osu_method system variable.

It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.
- On RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf
- On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf
Set the wsrep_osu_method system variable in the configuration file.

It needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

For example:
```
[mariadb]
...
wsrep_osu_method=NBO
```

Configuring the the Online Schema Change Method

Galera Cluster's online schema change method is configured by the wsrep_osu_method system variable.

Choose a configuration file in which to configure the wsrep_osu_method system variable.

It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.
- On RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf
- On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf
Set the wsrep_osu_method system variable in the configuration file.

It needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

For example:
```
[mariadb]
...
wsrep_osu_method=NBO
```
Restart the server. You can avoid the need to restart in MariaDB Enterprise Server 10.5 by dynamically changing the number of purge threads.

To restart the server on operating systems that use systemd:
```
$ sudo systemctl restart mariadb
```
On systems that use Upstart:
```
$ sudo service mysql restart
```