bagnap bagnap - 1 year ago 151
MySQL Question

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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download