The Library

Related Documents

Schema Upgrades

Schema changes are of particular interest related to Galara Cluster. Schema changes are DDL statement executed on a database (e.g., CREATE TABLE, GRANT). These DDL statements change the database itself and are non-transactional.

Galera Cluster processes schema changes by three different methods:

  • Total Order Isolation: Abbreviated as TOI, these are schema changes made on all cluster nodes in the same total order sequence, preventing other transations from committing for the duration of the operation.
  • Rolling Schema Upgrade Known also as RSU, these are schema changes run locally, affecting only the node on which they are run. The changes do not replicate to the rest of the cluster.
  • Non-Blocking Operations: Abbreviated as NBO, these are schema changes made on all cluster nodes in the same total order sequence, preventing other transations from committing for the duration of the operation, with much more efficient locking strategy that the TOI method.

You can set the method for online schema changes by using the wsrep_OSU_method parameter in the configuration file, (my.ini or my.cnf`, depending on your build) or through the ``mysql client. Galera Cluster defaults to the Total Order Isolation method.

Note

If you’re using Galera Cluster for Percona XtraDB Cluster, see the the pt-online-schema-change in the Percona Toolkit.

../_images/support.jpg

Total Order Isolation

When you want an online schema change to replicate through the cluster and don’t care that other transactions will be blocked while the cluster processes the DDL statements, use the Total Order Isolation method. You can do this with a global SET statement, as follows:

SET GLOBAL wsrep_OSU_method='TOI';

The GLOBAL command does not change the “wsrep_OSU_method” for the running session. If you want to change it for the running session, use the session-based SET statement, as follows:

SET SESSION wsrep_OSU_method='TOI';

In Total Order Isolation, queries that change the schema replicate as statements to all nodes in the cluster. The nodes wait for all preceding transactions to commit simultaneously, then they execute the schema change in isolation. For the duration of the DDL processing, no other transactions can commit.

The main advantage of Total Order Isolation is its simplicity and predictability, which guarantees data consistency. Additionally, when using Total Order Isolation, you should take the following particularities into consideration:

  • From the perspective of certification, schema upgrades in Total Order Isolation never conflict with preceding transactions, given that they only execute after the cluster commits all preceding transactions. What this means is that the certification interval for schema changes using this method has a zero length. Therefore, schema changes will never fail certification and their execution is guaranteed.
  • Transactions that were in progress while the DDL was running and that involved the same database resource will get a deadlock error at commit time and will be rolled back.
  • The cluster replicates the schema change query as a statement before its execution. There is no way to know whether or not individual nodes succeed in processing the query. This prevents error checking on schema changes in Total Order Isolation.

Rolling Schema Upgrade

When you want to maintain high-availability during schema upgrades and can avoid conflicts between new and old schema definitions, use the Rolling Schema Upgrade method. You can do this with a global SET statement, as follows:

SET GLOBAL wsrep_OSU_method='RSU';

The GLOBAL command does not change the “wsrep_OSU_method” for the running session. If you want to change it for the running session, use the session-based SET statement, as follows:

SET SESSION wsrep_OSU_method='RSU';

In Rolling Schema Upgrade, queries that change the schema are only processed on the local node. While the node processes the schema change, it desynchronizes with the cluster. When it finishes processing the schema change, it applies delayed replication events and synchronizes itself with the cluster.

To change a schema cluster-wide, you must manually execute the query on each node in turn. Bear in mind that during a rolling schema change the cluster continues to operate, with some nodes using the old schema structure while others use the new schema structure.

The main advantage of the Rolling Schema Upgrade is that it only blocks one node at a time. The main disadvantage of the Rolling Schema Upgrade is that it is potentially unsafe, and may fail if the new and old schema definitions are incompatible at the replication event level.

Non-Blocking Operations (this feature is part of Galera Cluster Enterprise Edition)

When you want an online schema change to replicate through the cluster, but are worried that long-running DDL statements block cluster updates, use the Non-Blocking Operations method. You can do this with a global SET statement, as follows:

SET GLOBAL wsrep_OSU_method='NBO';

The GLOBAL command does not change the “wsrep_OSU_method” for the running session. If you want to change it for the running session, use the session-based SET statement, as follows:

SET SESSION wsrep_OSU_method='NBO';

The NBO method resembles the TOI method. Queries that change the schema replicate as statements to all nodes in the cluster. The nodes wait for all preceding transactions to commit simultaneously, then they execute the schema change in isolation. For the duration of the DDL processing, no other transactions can commit.

The main advantage of Non-Blocking Operations is that it significantly reduces the impact of DDL statements on the cluster. During DDL processing:

  • You can alter another table, using NBO
  • You can continue inserting data, excluding the table(s) you are altering
  • If one node crashes, the operation will continue on the other nodes, and if successful it will persist

When using Non-Blocking Operations, take the following particularities into consideration:

  • The supported statements are:

    • ALTER TABLE table_name LOCK = {SHARED|EXCLUSIVE} , alter_specification
    • ALTER TABLE table_name LOCK = {SHARED|EXCLUSIVE} PARTITION. The comma after LOCK=SHARED|EXCLUSIVE is not used for partition-management ALTERs.
    • ANALYZE TABLE
    • OPTIMIZE TABLE

  • The unsupported statements are:

    • ALTER TABLE LOCK = {DEFAULT|NONE}. This also means that ALTER TABLE without a LOCK clause is not supported, as is defaults to DEFAULT.
    • CREATE
    • RENAME
    • DROP
    • REPAIR

  • As some DDL statements, such as CREATE without a LOCK argument, return an error, it is not recommended to use NBO on a server-wide basis. Only use it for sessions that run compatible DDL statements.

  • You cannot perform writes on a table that is being altered under NBO. Write attempts are blocked, until the ALTER is complete. Under LOCK=SHARED, reading from the table is allowed. Under LOCK=EXCLUSIVE, read operations are also blocked.

  • Locking the tables at the beginning of the operation is a blocking operation. The cluster may block, if there is an ongoing long transaction against the table being altered. To avoid this, ensure that no clients have open transactions that include the table, prior to running the ALTER statement.

  • While a DDL operation is running, nodes cannot be donors for SST. Thus, a node cannot join or rejoin the cluster using SST while an NBO DDL is in progress.

  • If a node leaves the cluster while an NBO DDL operation is in progress, its data files will be inconsistent and it can only rejoin the cluster through SST, not IST.

  • If a DDL statement is expected to take one hour, SST will not be available for one hour, only IST. Set a high-enough value for the gcache.size so that there is sufficient cached data to use IST.

  • Do not use NBO with statements that operate on more than one table at a time.

  • Do not perform online schema upgrades using the RSU method while a statement is running under the NBO method.

Warning

To avoid conflicts between new and old schema definitions, execute SQL statements such as CREATE TABLE and DROP TABLE using the Total Order Isolation method.

Note

Contact Codership sales at sales@galeracluster.com for more information, and to get the Galera Cluster Enterprise Edition software.

Related Documents