Search code examples
mysqlinnodbamazon-rds

Slow Large Innodb DB Import into Amazon RDS using mysqldump

OK I am experimenting with Amazon RDS and am having heaps of trouble loading an InnoDB database using mysqldump in a timely manner.

I am trying to get my local DB to the cloud.

Using 

mysqldump --single-transaction --opt -u root > file.sql

I can get a dump (~1.5GB) in around 3 minutes to my local file. The database is about 4G but mysqldump produces an sql file about 1.5G

When I use 

mysqldump -h localhost -u XXXX -pXXXX DBNAME --compress --single-transaction --quick  --opt --order-by-primary| mysql --host=AMAZONHOSTNAME --user=username --password DBNAME

it takes forever - I estimate based on the rate its going it will take 5 hours.

In terms of my bandwidth speed, on www.speedtest.net I get an upload speed of 0.67Mbps.

I cannot understand why its taking so long. I am : a) compressing the stream b) have all the options that the forums seems to require as well as the Amazon docs c) have a LARGE instance on Amazon RDS.

Can anyone help me here? Is there any way to improve the speed?

Solution

  • I have fixed it, or at least improved the speed by a large amount.

    I added --compress to the REMOTE side of the mysqldump command

    ie this

    mysqldump -h localhost -u XXXX -pXXXX DBNAME --compress --single-transaction --quick  --opt --order-by-primary| mysql --host=AMAZONHOSTNAME --user=username --password DBNAM

    became this

    mysqldump -h localhost -u XXXX -pXXXX DBNAME --compress --single-transaction --quick  --opt --order-by-primary| mysql --compress --host=AMAZONHOSTNAME --user=username --password DBNAM

    The data transfer rate (as measured by the increasing size of my remote database) went from about 13MB per minute to about 73MB per minute.