Thesql re-partition table

MySQLpartitioning

i have the following table. and i wan't to repartition it, adding a few more partitions to shard the overall size.

however i don't find any information on how to add partitions in this situation.

i always did it with downtime, dumping it, truncating it, dropping it, recreating it with new partitions, and importing the dump, BUT as the table grows, the downtime grows, so i am asking if there is a re-partition command in mysql, with no-to-low downtime. ?

thx in advance.

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191)),
  KEY `bk1` (`post_id`,`meta_key`(191),`meta_value`(191)),
  KEY `bk2` (`meta_key`(191),`meta_value`(191))
) ENGINE=InnoDB AUTO_INCREMENT=12833303 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
(PARTITION p1 VALUES LESS THAN (50000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (150000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (250000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (350000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (450000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (550000) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (600000) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (650000) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (700000) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (750000) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (800000) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (850000) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (900000) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (950000) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p21 VALUES LESS THAN (1050000) ENGINE = InnoDB,
 PARTITION p22 VALUES LESS THAN (1100000) ENGINE = InnoDB,
 PARTITION p23 VALUES LESS THAN (1150000) ENGINE = InnoDB,
 PARTITION p24 VALUES LESS THAN (1200000) ENGINE = InnoDB,
 PARTITION p25 VALUES LESS THAN (1250000) ENGINE = InnoDB,
 PARTITION p26 VALUES LESS THAN (1300000) ENGINE = InnoDB,
 PARTITION p27 VALUES LESS THAN (1350000) ENGINE = InnoDB,
 PARTITION p28 VALUES LESS THAN (1400000) ENGINE = InnoDB,
 PARTITION p29 VALUES LESS THAN (1450000) ENGINE = InnoDB,
 PARTITION p30 VALUES LESS THAN (1500000) ENGINE = InnoDB,
 PARTITION p31 VALUES LESS THAN (1550000) ENGINE = InnoDB,
 PARTITION p32 VALUES LESS THAN (1600000) ENGINE = InnoDB,
 PARTITION p33 VALUES LESS THAN (1650000) ENGINE = InnoDB,
 PARTITION p34 VALUES LESS THAN (1700000) ENGINE = InnoDB,
 PARTITION p35 VALUES LESS THAN (1750000) ENGINE = InnoDB,
 PARTITION p36 VALUES LESS THAN (1800000) ENGINE = InnoDB,
 PARTITION p37 VALUES LESS THAN (1850000) ENGINE = InnoDB,
 PARTITION p38 VALUES LESS THAN (1900000) ENGINE = InnoDB,
 PARTITION p39 VALUES LESS THAN (1950000) ENGINE = InnoDB,
 PARTITION p40 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p41 VALUES LESS THAN (2050000) ENGINE = InnoDB,
 PARTITION p42 VALUES LESS THAN (2100000) ENGINE = InnoDB,
 PARTITION p43 VALUES LESS THAN (2150000) ENGINE = InnoDB,
 PARTITION p44 VALUES LESS THAN (2200000) ENGINE = InnoDB,
 PARTITION p45 VALUES LESS THAN (2250000) ENGINE = InnoDB,
 PARTITION p46 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

for more details:

partitions

Best Answer

What I would do is set it up with sufficient partitions so that your top (MAXVALUE) partition never gets used. In this way when the time comes to add the next partition, you are going to re-partition the 'empty' MAXVALUE partition which should be a lot quicker.

So you would start by adding a partition for VALUES LESS THAN (2300000) which replaces the MAXVALUE partition.

Then when you get close to 2300000 entries, you would add a new partition for VALUES LESS THAN (2350000). And because you would be splitting the empty MAXVALUE partition it will be relatively quick.

Related Question