Mysql – Any way to speed up reorganize partition of a very large table in MySQL

MySQLmysql-5.6partitioning

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.

Best Answer

You may want to try Percona's pt-online-schema-change

pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see --chunk-time). This process is very similar to how other tools, such as pt-table-checksum, work. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table.

When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

Related Question