Online schema upgrade

Database schemas must change as applications change. The schema upgrade must occur for a cluster while the system is online. A synchronous cluster requires all active nodes to have the same data. Schema updates are performed using Data Definition Language (DDL) statements, such as ALTER TABLE <table_name> DROP COLUMN <column_name>.

The DDL statements are non-transactional, which means they cannot be rolled back once executed. These statements use up-front locking to avoid the chance of deadlocks. We recommend testing your schema changes thoroughly, especially if you must run an ALTER statement on large tables. Always verify the backups before updating the schemas in the production environment. A failure in a schema change can cause your cluster to drop nodes and lose data.

Percona XtraDB Cluster supports the following methods for making online schema changes:

Method Name	Reason for use	Description
TOI or Total Order Isolation	Consistency is important.	Other transactions are blocked while the cluster processes the DDL statements. This is the default method for the wsrep-OSU-method variable. The isolation of the DDL statement guarantees consistency. The DDL replication uses a statement format with each node processing the replicated DDL statement at the same position in the replication stream. All other writes must wait until the DDL statement is executed. Long-running transactions using the same resources may receive a deadlock error at commit and be rolled back. The pt-online-schema-change in the Percona Toolkit can alter the table without using locks. There are limitations: only InnoDB tables can be altered, and the wsrep_OSU_method must be TOI.
RSU or Rolling Schema Upgrade	This method guarantees high availability during the schema upgrades.	The node desynchronizes with the cluster and disables flow control during the execution of the DDL statement. The rest of the cluster is not affected. After the statement execution, the node applies delayed events and synchronizes with the cluster. Although the cluster remains active, some nodes have the newer schema and some nodes have the older schema during the process. The RSU method is a manual operation, requiring the gcache to be large enough to store the data for the duration of the DDL change.
NBO or Non-Blocking Operation	This method is used when consistency is important and uses a more efficient locking strategy.	This method is similar to TOI. DDL operations acquire an exclusive metadata lock on the table or schema at a late stage of the operation when updating the table or schema definition. Attempting a State Snapshot Transfer (SST) fails during the NBO operation. This mode uses a more efficient locking strategy and avoids the TOI issue of long-running DDL statements blocking other updates in the cluster.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert

---

Last update: 2024-07-15