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

Question

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.

Answer 1

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

Answer 2

This is not a perfect solution, but maybe better than a 100% downtime:

Make a script (bash/sql) to first rename the db.table to db.tableBig, and immediately recreate db.table. This will happen in a split second, so inserts to your table can continue.

Do your repartitioning. 'Alter table' or 'csv dump + load data', I don't know what is fastest. For the dump/load, first create tableNew with correct partitioning and then do the dump/load of tableBig to tableNew:

$ echo "select * from db.tableBig" | mysql -u... > tableDump.csv
$ echo "load data local infile 'tableDump.csv' into table db.tableNew FIELDS TERMINATED BY '\t';" > importDump.sql
$ mysql --local-infile -u... < importDump.sql

(For 3GB tables this took me around 20min to dump and load, much faster and less intensive for mysql than partioning. But for 700GB?)

After the partitioning, again a script to do this as fast as possible: 1. rename table to tableNewData; 2. rename tableNew to table; 3. insert into table select * from tableNewData.

• You can keep on collecting data in the meantime.
• You can data-crunch on the old data in tableBig (new data not included)
• You can do some lookup on the new data if necessary
• (I don't know if this is advisable on a 1.5TB table, but use UNION to select of the new and old data?)

I hope you get the idea. There might be some mistakes in the sql and bash that I've put in between, so please test first on a test-table and review the sql/bash.