Online schema upgrade¶
Database schemas must change as applications change. For a cluster, the schema upgrade must occur while the system is online. A synchronous cluster requires all active nodes 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, so these statements use up-front locking to avoid the chance of deadlocks and cannot be rolled back. We recommend that you test your schema changes, especially if you must run an
ALTER statement on large tables. 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. Each node processes the replicated DDL statement at same position in the replication stream. All other writes must wait until the DDL statement is executed. While a DDL statement is running, any long-running transactions in progress and using the same resource receive a deadlock error at commit and are 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
|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 is active, during the process some nodes have the newer schema and some nodes have the older schema. The RSU method is a manual operation. For this method, the
|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