# Altering range partition at higload table in Galera multimaster cluster

**URL:** <https://forums.percona.com/t/altering-range-partition-at-higload-table-in-galera-multimaster-cluster/6919>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [March 22, 2019, 8:59am UTC](https://forums.percona.com/t/altering-range-partition-at-higload-table-in-galera-multimaster-cluster/6919 "2019-03-22T08:59:59Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![fomichev](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/fomichev/32/1257_2.png) [@fomichev](https://forums.percona.com/u/fomichev)\
**Post date:** [March 22, 2019, 8:59am UTC](https://forums.percona.com/t/altering-range-partition-at-higload-table-in-galera-multimaster-cluster/6919/1 "2019-03-22T08:59:59Z")

</div>

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.

 ![1321](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/5/559e71bfe23d61ce62751f0d5cd6018a74ba915c.png)  
 ![1320](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/a/a921eef488ec25c54d70e8a6d337106a99610aed.png)