Does further partitioning a MariaDB table require downtime?

Question

I need to add partitions to an already partitioned table, the table has RANGE partitions by year(timestamp), but the previous admin stopped partitioning awhile ago. Now the table has grown to 4.1 million rows and counting, making the queries very slow. The old partitions are rarely (if ever) queried, so I need to keep on partitioning but this is a production DB hosted on AWS RDS.

Table partition section:

...
PARTITION BY RANGE (year(`timestamp`))
(PARTITION `p10` VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION `p11` VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION `p12` VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION `p13` VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION `p14` VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION `p15` VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION `current` VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

My question is, if I run an:

ALTER TABLE mytable
    REORGANIZE PARTITION current INTO (
        PARTITION p16 VALUES LESS THAN (2017),
        ...
        PARTITION p22 VALUES LESS THAN (2023),
        PARTITION future VALUES LESS THAN MAXVALUE);

Will it cause any downtime to the database? This is a HOT production server.

Answer 1

Look at it this way. Changing the partitioning involves copying data from one (or more) partitions to another partition. The time it takes is [mostly] proportional to the number of rows involved.

Assuming the partition names match the data, you need to shovel all the data in current into the p16,...,p22.

Your query is optimal because it will involve shoveling the data only once.

It will take time. There is no shortcut with standard ALTERs.

In the future, do the REORGANIZE each Dec. 31 -- creating next year's partition while there are still zero rows in current. Oh, "current" should really be called "future". And the goal is to 'never' let rows get into future (except accidentally). That way, each year-end's `REORGANIZE will be very fast.

There may be a shortcut with pt-online-schema-change (see Percona Toolkit). But I worry that partitions may not allow for the Triggers that it needs to set.

More on this (and other things): Partition