0

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.

2
  • "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." - This isn't likely the root cause for your slowness, as I mentioned in my comment on your other post, PARTITIONing is not really a tool meant for improving read performance. Also, 4 million rows is not many rows at all.
    – J.D.
    Oct 27, 2022 at 11:54
  • 1
    Let's see your main queries. I suspect they could be optimized, and you don't really need partitioning.
    – Rick James
    Oct 27, 2022 at 16:24

1 Answer 1

0

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

2
  • Ok i am aware it will take time, there are literally billions of rows in the 'current' partition, however my question remains, if i do the REORGANIZE, during the time it takes to partition, will the database or the table be LOCKED and denying changes? will it fill up a Que? will it accept queries and updates? Oct 27, 2022 at 18:16
  • @FernandoPérez - Queries will either immediately fail or queue up and time-out. Either way, you can't get anything done for the hour(s) it takes.
    – Rick James
    Oct 27, 2022 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.