mysqldump on a 35GB table to a server in another region

Question

I'm in the process of potentially migrating my entire website from a dedicated physical server located in Ireland to a DigitalOcean server located in America (New York). We'll also be moving from MySQL 5.7 to MySQL 8.

When I first migrated the site (sans this 35GB table) it was almost 5GB and took maybe an hour and a half. I then had the site on the DigitalOcean server connect to the source server in Ireland for MySQL to test performance out of curiousity. There was about a 300-600ms delay connecting back to the server in Ireland. This could partly be caused by the weakish DigitalOcean droplet, which I plan on resizing before launch to a larger server.

My plan is to use mysqldump to send this remaining 35GB data but I am worried about:

• How long this will take. The table is pretty heavily used on the source server so I will probably have to lock it when sending.
• If the millisecond delay from Ireland to America will heavily affect this.
• Having it potentially time-out will be an issue, especially if the table is being locked and I then need to try it.

Are there any suggestions or optimization routes I can look into before I begin this task?

Answer 1

No one can answer this accurately for you. The size of the table can affect the speed of restoration, but the definition of the table can also be a factor. For example, it's slower to load data into a table with many indexes. But you haven't shared the table definition, so we can only guess at it. Likewise row size, column data types, etc. may affect load time.

So any answer given here will be a guess. A more accurate answer could be that you must test it yourself, using a subset of your table. You can use mysqldump --where="..." to dump a subset of data, say 10% of the table. Measure how much time it takes to transfer and load. Then multiply that result by 10 to get an estimate. And know that you may experience it getting slower to load data the larger the table gets, so it won't be exactly 10x the time to load the 10% subset.

Other tips that could potentially improve load time:

• Compress the dump file, transfer that compressed file to the other region, then uncompress it and load it locally.

• Don't create an SQL format dump file, and dump to CSV file instead. Loading plain CSV data is usually much faster than loading an SQL dump. See my presentation Load Data Fast!

• If the table is InnoDB, you can export a transportable tablespace, instead of using mysqldump. This is a means to move a physical copy of the tablespace, so you don't have to load at all. Of course try it with a small table to get comfortable with the process. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html.

  One thing I am not sure will work, however: exporting a tablespace from 5.7 and importing it to 8.0. I've never tried that, and I haven't found any documentation claiming it is supported. I would recommend doing some local testing to gain confidence before trying it with your production data.