I am trying to migrate from a MySQL AWS RDS instance with a huge SSD and too much excess space down to a small one, and data migration is the only method. There are four tables in the range of 330GB-450GB and executing mysqldump, in a single thread, while piped directly to the target RDS instance is estimated to take about 24 hours by pv (copying at 5 mbps).

I wrote a bash script that calls multiple mysqldump using ' & ' at the end and a calculated --where parameter, to simulate multithreading. This works and currently takes less than an hour with 28 threads.

However, I am concerned about any potential loss of performance while querying in the future, since I'll not be inserting in the sequence of the auto_increment id columns.

Can someone confirm whether this would be the case or whether I am being paranoid for no reasons.

What solution did you use for a single table that is in the 100s of GBs? Due to a particular reason, I want to avoid using AWS DMS and definitely don't want to use tools that haven't been maintained in a while.

  • Can't you just get Amazon to migrate for you? They have techniques that are much faster than mysqldump. – Rick James Oct 14 '20 at 23:50
  • They have AWS DMS, but that's error prone and I have had multiple issues with it in the past. – Shahid Thaika Oct 15 '20 at 11:00

You are correct that it will cause fragmentation of the clustered index. However, if it is an auto-incrementing column the data wasn't really sorted by anything meaningful. You went from a unsorted mess to a differently sorted unsorted mess.

Selecting/updating/reading a few rows at a time? Not a big deal - the B-tree will still know how to find the correct page without too much additional effort.

You'll have issues if you're trying to break up large updates/deletes by using ranges of the auto-incrementing column as the rows will be spread across pages.

If performance does become an issue, you can rebuild the index, the newer versions of MySQL should be able to do so without taking the table offline.

As an aside - did you attempt sorting the data by the auto-incrementing column then performing a bulk load?

  • The current data should have been inserted in the sequence of the auto_increment column, it's restore where I am having issues, since I am restoring in parallel with multiple WHERE parameters to mysqldump (piped directly to mysql for restore to the target instance). Any method of backup suffers from a long time to restore in single thread mode. – Shahid Thaika Oct 7 '20 at 7:57

Tables are by nature unsorted, so you will not have any performance loss on that site, after inserting your data, but we doen't how your instances is smaller, we can't tell the impact it will have.

Your index on that field will be sorted and so will find the wanted rows quite fast, at least faster as scanning the hole column.

  • by size, I meant the SSD size. Right now we have excess in TBs, so cutting down on size and cost – Shahid Thaika Oct 6 '20 at 11:19
  • that is no problem for the performance, if the temp file size still fits, but in my opinion databases grow at a certain level, so cutting down on the size of teh harddrive should be correctly calculated. – nbk Oct 6 '20 at 11:25
  • @nbk InnoDB does have a clustered index - so things are (attempted to be) sorted by that index. In this case the method of insert will fragment the table. – bbaird Oct 6 '20 at 12:59

Not a tall, there is no performance issue in any of the cause

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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