mysql re-partition table

Question

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:

Negative numbers? Is something (OS or MySQL) built 32-bit instead of 64-bit?? — Rick James
– Rick James, Commented Mar 6, 2018 at 17:42
If you are 32-bit, and cannot get away from it, please say so. That may be a valid excuse to PARTITION, but perhaps not the way you did it. — Rick James
– Rick James, Commented Mar 6, 2018 at 17:45

IGGt · Accepted Answer · 2018-03-01 09:29:29Z

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.

Helmut Januschka · Accepted Answer · 2018-03-01 12:46:07Z

i ended up doing it like this: (tested it 4 times with a live copy of my dataset, depending on machine I/O capabilites it run 15-50mins.)

-- check that nightly-db-container has been build (recently)
-- and is useable - in a local env. - e.g: search for latest article.
-- this is a total plan-C option, so chill!


-- do a mysql dump
mysqldump wp > 1.dmp

-- ensure table is safe! and produce a global lock
-- from this point, the db is locked, and frontend/backend and so on are going to hang
-- you might need to kill all running connections (get them with `show processlist` and kill with `kill %d`)
FLUSH TABLES WITH READ LOCK;


-- p46 in that case is the latest partition, you can get the latest via the DDL
-- its the one that has MAXVALUE
-- HINT: create a couple of partitions with high numbers in advance, so you don't need 
-- to run it again too soon.

ALTER TABLE wp_postmeta 
REORGANIZE PARTITION p46 INTO (
  PARTITION p46 VALUES LESS THAN (3000000),
  PARTITION p48 VALUES LESS THAN (4000000),
  PARTITION p49 VALUES LESS THAN (5000000),
  PARTITION p50 VALUES LESS THAN (6000000),
  PARTITION p51 VALUES LESS THAN (7000000),
  PARTITION p52 VALUES LESS THAN (8000000),
  PARTITION p53 VALUES LESS THAN (9000000),
  PARTITION p54 VALUES LESS THAN MAXVALUE
);

-- shrink down prev. large partition (in that case p46)

optimize table wp_postmeta;

-- unlock global lock
UNLOCK TABLES;



-- PARTITION an unpartitioned table:
-- double check before
select count(1) from wp_posts; -- '1315017'

ALTER TABLE wp_posts
  PARTITION by RANGE(id) (
    PARTITION p1 VALUES LESS THAN (1000000),
    PARTITION p2 VALUES LESS THAN (2000000),
    PARTITION p3 VALUES LESS THAN (3000000),
    PARTITION p4 VALUES LESS THAN (4000000),
    PARTITION p5 VALUES LESS THAN (5000000),
    PARTITION p6 VALUES LESS THAN (6000000),
    PARTITION p7 VALUES LESS THAN (7000000),
    PARTITION p8 VALUES LESS THAN (8000000),
    PARTITION p9 VALUES LESS THAN (9000000),
    PARTITION p10 VALUES LESS THAN (10000000),
    PARTITION p11 VALUES LESS THAN (11000000),
    PARTITION p12 VALUES LESS THAN (MAXVALUE)
  );

There is no need for the lengthy OPTIMIZE TABLE; the REORGANIZE effectively did such. Also no need for the FLUSH and UNLOCK. But, as I imply in my answer, you are only making your table slower and bigger -- no gain. — Rick James
– Rick James, Commented Mar 6, 2018 at 17:40
One more thing -- do the REORGANIZE before data gets into the MAXVALUE partition -- this makes it 'instantaneous'. — Rick James
– Rick James, Commented Mar 6, 2018 at 17:42

Rick James · Accepted Answer · 2018-03-06 17:43:54Z

There is a way to add a new partition with virtually zero down time. (Your answer almost had it.) But first, I am baffled as to why you thought partitioning was beneficial. Let me explain why it hurts performance and bloats the disk.

The CREATE TABLE is missing the PARTITION BY clause; I'll guess that it is PARTITION BY RANGE(meta_id). That particular partitioning will slow down all accesses the table except for new inserts! This is because the application SELECTs via meta_id, but must come at the table by other indexes -- necessitating checking all partitions. The more you have, the longer SELECTs involving it will take.

There are many problems with WP's postmeta schema. Adding partitioning just adds to the list of problems. Here are the other problems, plus solutions. I strongly recommend you abandon PARTITIONing and implement my 6 recommendations.

Also note that by removing partitioning, the disk space used will shrink. (It won't shrink by a lot, but at least some. I don't see the Data_free column in your output, but is is usually significant; most of that will be recouped.) Also, My suggestions reduce the number of indexes (while improving their utility); this will shrink the disk space significantly.

Stack Exchange Network

mysql re-partition table

3 Answers 3

Your Answer

Hot Network Questions

mysql re-partition table

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions