Altering range partition at higload table in Galera multimaster cluster

We have 4 nodes in a cluster and all application connections going to first node through load balance. If first node goes down connection switch to second node and so on. We need to add some new RANGE patritions on highloaded (~1000qpm) 110Gb table (later mentioned as t) without any service downtime and loosing cluster integrity.

Several questions:

  1. Out current OSU method is TOI, can it handle such update smoothly witout any locks?

  2. If we will use RSU is this scheme vaild?
    a) Move cluster node out of balancer, forcing traffic to go to second node
    b) SET GLOBAL wsrep_OSU_method=‘RSU’; – This also takes the node out of the cluster.
    c) ALTER TABLE t ADD PARTITION …;
    d) SET GLOBAL wsrep_OSU_method=‘TOI’; – Puts back in, leading to resync (hopefully a quick IST, not a slow SST)

  3. Can you provide outher methods to do this DDL manipulation on all nodes of the cluster

Details:

We have large (110Gb) partitioned table t with 37 fields including date, and this type of RANGE partition on date field:
CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT,


date datetime NOT NULL,


PRIMARY KEY (date,id),

KEY id_index (id),

KEY date_index (date),


) ENGINE=InnoDB AUTO_INCREMENT=870592203 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

/*!50100 PARTITION BY RANGE (TO_DAYS(date))
(PARTITION 2012_2014 VALUES LESS THAN (735964) ENGINE = InnoDB,
PARTITION 2015_q1 VALUES LESS THAN (736054) ENGINE = InnoDB,
PARTITION 2015_q2 VALUES LESS THAN (736145) ENGINE = InnoDB,
PARTITION 2015_q3 VALUES LESS THAN (736237) ENGINE = InnoDB,
PARTITION 2015_q4 VALUES LESS THAN (736329) ENGINE = InnoDB,
PARTITION 2016_q1 VALUES LESS THAN (736420) ENGINE = InnoDB,
PARTITION 2016_q2 VALUES LESS THAN (736511) ENGINE = InnoDB,
PARTITION 2016_q3 VALUES LESS THAN (736603) ENGINE = InnoDB,
PARTITION 2016_q4 VALUES LESS THAN (736695) ENGINE = InnoDB,
PARTITION 2017_q1 VALUES LESS THAN (736785) ENGINE = InnoDB,
PARTITION 2017_q2 VALUES LESS THAN (736876) ENGINE = InnoDB,
PARTITION 2017_q3 VALUES LESS THAN (736968) ENGINE = InnoDB,
PARTITION 2017_q4 VALUES LESS THAN (737060) ENGINE = InnoDB,
PARTITION 2018_q1 VALUES LESS THAN (737150) ENGINE = InnoDB,
PARTITION 2018_q2 VALUES LESS THAN (737241) ENGINE = InnoDB,
PARTITION 2018_q3 VALUES LESS THAN (737333) ENGINE = InnoDB,
PARTITION 2018_q4 VALUES LESS THAN (737425) ENGINE = InnoDB,
PARTITION 2019_q1 VALUES LESS THAN (737515) ENGINE = InnoDB,
PARTITION 20XX_qX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

We need to add several new partitions to it like so

ALTER TABLE t ADD PARTITION (PARTITION 2019_q2 VALUES LESS THAN (‘2019-07-01’)), ADD PARTITION (PARTITION 2019_q3 VALUES LESS THAN (‘2019-10-01’)), ALTER TABLE t ADD PARTITION (PARTITION 2019_q4 VALUES LESS THAN (‘2020-01-01’));

We can’t use pt-online-schema-change here for obvious reasons.


1320