CODEWITHSUNDEEP
mysqldatabase-partitioning
Godzilla74
Godzilla74

Reputation: 2512

Adding a partition to already partitioned table

Whats the best way to add another partition to an already partitioned table?

The original CREATE TABLE statement looks like:

CREATE TABLE `command_log` (
  `id` bigint(20) NOT NULL,
  `insert_time` datetime NOT NULL,
  `start_time` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `command` varchar(255) NOT NULL,
  `parameters` varchar(255) DEFAULT NULL,
  `result` mediumblob,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(insert_time))
(PARTITION p001 VALUES LESS THAN (736237) ENGINE = InnoDB,
 PARTITION p002 VALUES LESS THAN (736268) ENGINE = InnoDB,
 PARTITION p003 VALUES LESS THAN (736298) ENGINE = InnoDB,
 ...
 PARTITION p064 VALUES LESS THAN (738156) ENGINE = InnoDB,
 PARTITION p065 VALUES LESS THAN (738187) ENGINE = InnoDB,
 PARTITION p066 VALUES LESS THAN (738215) ENGINE = InnoDB,
 PARTITION p067 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

Let's say I want to add just 1 additional partition, in this case p067. Does this require a whole ALTER TABLE statement, like:

ALTER TABLE command_log 
PARTITION by range (to_days(insert_time))
(
    partition p059 VALUES LESS THAN (to_days('2020-08-01'))
  , partition p060 VALUES LESS THAN (to_days('2020-09-01'))
  , partition p061 VALUES LESS THAN (to_days('2020-10-01'))
  , partition p062 VALUES LESS THAN (to_days('2020-11-01'))
  , partition p063 VALUES LESS THAN (to_days('2020-12-01'))
  , partition p064 VALUES LESS THAN (to_days('2021-01-01'))
  , partition p065 VALUES LESS THAN (to_days('2021-02-01'))
  , partition p066 VALUES LESS THAN (to_days('2021-03-01'))
  , partition p067 VALUES LESS THAN (to_days('2021-04-01'))
  , partition p068 VALUES LESS THAN (MAXVALUE)
);

If this is the case, what exactly happens?

Do my older partitions not included in this statement get dropped (for example p001 - p058)?

Does doing this wipe out any existing data in the table (for instance, data in p059)?

Thanks!

Upvotes: 6

Views: 12361

Answers (2)

Atul
Atul

Reputation: 3377

I have the same issue to create partitions dynamically in Mysql 8 for the existing table which do not have any partition initially.

I have to add partitions for the future dates and keeps adding.

Steps I followed to solve this:

  1. Get the latest partition name (create partition name such a way that it contains the date)

  2. If the partition date date is not present then create partition using "Range" base mechanism. The query is like to create partitions:

    ALTER TABLE <tableName> PARTITION BY RANGE (TO_DAYS(<columnName>)) (
PARTITION p20200527 VALUES LESS THAN (TO_DAYS('2020-05-28')),  
PARTITION p20200528 VALUES LESS THAN (TO_DAYS('2020-05-29')),  
PARTITION p20200529 VALUES LESS THAN (TO_DAYS('2020-05-30')),  
PARTITION p20200530 VALUES LESS THAN MAXVALUE);

  3. Once we have the partition on table then we have to just add the partitions using "Reorganize".

    ALTER TABLE <tableName> REORGANIZE PARTITION p20200730 INTO ( 
PARTITION p20200530 VALUES LESS THAN (TO_DAYS('2020-05-31')),  
PARTITION p20200531 VALUES LESS THAN (TO_DAYS('2020-06-01')),  
PARTITION p20200601 VALUES LESS THAN (TO_DAYS('2020-06-02')),  
PARTITION p20200602 VALUES LESS THAN MAXVALUE);

The bottom line is: If you do not have any partition on table and want to add it on any date column, you can add it anytime. Once you have partitions on table on date using "Range" based mechanism and you want to add more partitions then add those partitions using "Reorganize".

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562951

You don't have to redefine all the earlier partitions.

It's a common operation in range partitioned tables to split the last partition that handles values less than MAXVALUE into a few new partitions for specific ranges. You can do this with REORGANIZE PARTITION.

For example, to split the last partition into two new partitions for fixed date ranges, plus a new maxvalue partition at the end:

ALTER TABLE command_log REORGANIZE PARTITION p067 INTO (
  partition p067 VALUES LESS THAN (TO_DAYS('2021-04-01'))
, partition p068 VALUES LESS THAN (TO_DAYS('2021-05-01'))
, partition p069 VALUES LESS THAN (MAXVALUE)
);

All the earlier partitions will be untouched by this reorganize operation.

If you do this reorganize while the last partition is still empty (i.e. it contains zero rows), then no data copying is needed, and the operation should be virtually instantaneous.

If you forget to do the reorganize, and the last partition collects some rows, then it's not too late to reorganize it — but it will take a little bit of time, proportional to the number of rows in the partition you split. Reorganizing a partition that has data does require copying data rows, but only the rows of the partition you are reorganizing. Earlier partitions are still untouched.

See https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-range-list.html for more details.

Upvotes: 13

Related Questions