Online Schema Changes with MariaDB Enterprise Cluster

This page is part of MariaDB's Documentation.

The parent of this page is: Online Schema Changes with MariaDB Enterprise Server

Topics on this page:

Overview
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
Troubleshooting
- wsrep_OSU_method NBO not supported for query

Overview

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 `TOI` 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. The `TOI` method is very safe, but it can result in more locking than other modes. `TOI` is the default mode.
`RSU`	This method uses "Rolling Schema Upgrade" mode. In `RSU` 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 `RSU` mode can lead to replication errors. Therefore, it is only recommended to use `RSU` 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. `NBO` mode is supported in MariaDB Enterprise Server 10.5 and later. In `NBO` 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, `NBO` mode uses much more efficient locking for some operations than `TOI` does.

Non-Blocking Operations

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

NBO 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 branch_id_index ON hq_sales.invoices (branch_id), LOCK=SHARED;
```
Some operations require the LOCK clause to be specified with either a SHARED or EXCLUSIVE lock. For additional information, see "wsrep_OSU_method NBO not supported for query".

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.

MariaDB recommends leaving the global value of wsrep_osu_method as the default value to avoid errors. It is generally safer to change the value for individual sessions, as-needed. However, if you need to dynamically change the global value, follow the procedure below:

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 is 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.

MariaDB recommends leaving the global value of wsrep_osu_method as the default value to avoid errors. It is generally safer to change the value for individual sessions, as-needed. However, if you need to configure the global value, follow the procedure below:

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

It is 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:
```
$ sudo systemctl restart mariadb
```

Troubleshooting

wsrep_OSU_method NBO not supported for query

Cause	When `wsrep_osu_method=NBO` is set, some operations require the `LOCK` clause to be specified with a `SHARED` or `EXCLUSIVE` lock.
Effect	The operation fails with the `ER_NOT_SUPPORTED_YET` error code and the error message shown below.
Solution	Add the `LOCK` clause and specify either a `SHARED` or `EXCLUSIVE` lock.
Example Error	ERROR 1235 (42000): wsrep_OSU_method NBO not supported for query