So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added. The partitions are based off a timestamp which are created by month and the last partition is from March. So we have about 4 month's worth of data in the pMax partition which is nearing 700GB. This table is of course business critical and we can't have much downtime.
I have a smaller table that had about 80G in the pMax partition and it took about 6 hours to reorganize and pull out 13G of data for one month. So it would easily take around 3 days to fix up the big table.
This is what I'm using to reorganize and add partitions:
alter table db.table reorganize partition pMax into (partition p20150201 values less than ('2015-03-01'), partition p20150301 values less than ('2015-04-01'), partition pMax values less than (maxvalue));
Would it be faster to create a new table with partitions created for the next year, then insert data from the current table and rename once it's completed? I'm still thinking this would take forever given the size of the table... but don't know what other options I have, if any....
Has anyone use pt-online-schema-change to reorganize a partition? Sounds like I should be able to do it online.. just need to double our storage mount.