Online Schema Changes with MariaDB Enterprise Cluster

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:

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.

  1. Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:

    $ sudo mariadb
    
  2. Set the wsrep_osu_method system variable to the new size using the SET SESSION statement.

    For example:

    SET SESSION wsrep_osu_method='NBO';
    
  3. 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:

  1. Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:

    $ sudo mariadb
    
  2. Set the wsrep_osu_method system variable to the new size using the SET GLOBAL statement.

    For example:

    SET GLOBAL wsrep_osu_method='NBO';
    
  3. 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

  4. 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:

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

  2. 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
    
  3. 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