Performing Online Schema Changes with Galera Cluster
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 |
---|---|
|
|
|
|
|
|
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
orEXCLUSIVE
locking mode.CREATE INDEX operations executed in
SHARED
orEXCLUSIVE
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 theSUPER
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 theSUPER
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