Global variable wsrep_OSU_method=RSU ignored on MariaDB Galera Cluster 10.0.32

Saturday, January 20, 2018

Galera Cluster is great solution for application clustering but it has some limitations. One of the most critical limitations is how Galera Cluster performs model changes.

From Galera Cluster documentation:
http://galeracluster.com/documentation-webpages/clusterstallonalter.html

What’s happening is a side effect of a multi-master cluster with several appliers.
The cluster needs to control when a DDL statement ends in relation to other 
transactions, in order to deterministically detect conflicts and schedule
parallel appliers. Effectively, the DDL statement must execute in isolation.

Galera Cluster has a 65K window of tolerance for transactions applied in parallel,
but the cluster must wait when ALTER commands take too long.

Basically if you run DDL statement on slightly bigger table you will probably freeze your cluster for a duration of the DDL statement. And you cannot cancel this operation.

Built-in method to perform model changes is using "Rolling Schema Upgrade" method.

From MariaDB Galera Cluster documentation you have note on how to perform DDL changes on Galera Cluster.
https://mariadb.com/kb/en/library/tips-on-converting-to-galera/
A "fast" DDL may as well be done via TOI. This is a tentative list of such:
* CREATE/DROP/RENAME DATABASE/TABLE
* ALTER to change DEFAULT
* ALTER to change definition of ENUM or SET (see caveats in manual)
* Certain PARTITION ALTERs that are fast.
* DROP INDEX (other than PRIMARY KEY)
* ADD INDEX?
* Other ALTERs on 'small' tables.
* With 5.6 and especially 5.7 having a lot of ALTER ALGORITHM=INPLACE cases, 
check which ALTERs should be done which way.
Otherwise, use RSU. Do the following separately for each node: 
1. SET GLOBAL wsrep_OSU_method='RSU'; -- This also takes the node out of the cluster. 
2. ALTER TABLE ... 
3. SET GLOBAL wsrep_OSU_method='TOI'; -- Puts back in, leading to resync 
(hopefully a quick IST, not a slow SST)

Let’s test this solution.

My testing enviromnent:
Server version: 10.0.32-MariaDB-1~trusty-wsrep mariadb.org binary distribution, wsrep_25.20.rc3fc46e

I will add column to sbtest table.

Node1:
SET GLOBAL wsrep_OSU_method='RSU';

alter table sbtest add column text varchar(20);

In error.log I didn’t noticed any information about node being desynced from the cluster.

Node2 lock check:
MariaDB [(none)]> select trx_query,trx_tables_locked
                  from information_schema.innodb_trx;
+------------------------------------------------------+-------------------+
| trx_query                                            | trx_tables_locked |
+------------------------------------------------------+-------------------+
| alter table sbtest add column text varchar(20)       |                 1 |
+------------------------------------------------------+--------------------
1 row in set (0.00 sec)

Even though I globally enabled RSU method on Node1 with statement noted in documentation Node1 is not desynced from the cluster and DDL statement is executed on other nodes.
If this was production database I would have big problems during ALTER command.

But what if I enable RSU method using session level variable.

Node1:
SET SESSION wsrep_OSU_method=RSU;

alter table sbtest add column text1 varchar(20);

SET SESSION wsrep_OSU_method=TOI;

From error.log:
180120 11:26:34 [Note] WSREP: Member 0.0 (mariadb-test-1) desyncs itself from group
180120 11:26:34 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 8)
180120 11:26:34 [Note] WSREP: Provider paused at b95703dd-fd37-11e7-a31f-1af15b5193fa:8 (21)
180120 11:35:03 [Note] WSREP: resuming provider at 21
180120 11:35:03 [Note] WSREP: Provider resumed.
180120 11:35:03 [Note] WSREP: Member 0.0 (mariadb-test-1) resyncs itself to group
180120 11:35:03 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 8)
180120 11:35:03 [Note] WSREP: Member 0.0 (mariadb-test-1) synced with group.
180120 11:35:03 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 8)
180120 11:35:03 [Note] WSREP: Synchronized with group, ready for connections

Node2 lock check:
MariaDB [(none)]> select trx_query,trx_tables_locked 
                  from information_schema.innodb_trx;
Empty set (0.00 sec)

Now Node1 is desynced from the cluster and I am running ALTER statement locally without impact on the rest of the cluster.


I don’t know if this behaviour is expected for this version, or it is bug.

When running Galera Cluster in production it is VERY important to take some time and perform tests especially if you need to run ALTER statement on big tables.
Expect the unexpected even when running statements from documentation.



Share/Bookmark

0 comments:

Post a Comment