Why is my dev server faster than a much more powerful RDS? I'm running a huge data migration. Probably ~100 million rows total across a few dozen tables.

There is a lot of reformatting. About 50% of it is done through PHP while the rest uses INSERT INTO... SELECT FROM statements with multiple joins.

When I run the script on my dev server (MySQL server is localhost), it takes about 2 hours and 14 minutes total. This machine has 8 GB of RAM, 200 GB storage, and a 4-core 3.1 GHz CPU.

I set up an EC2 instance and an RDS in the same zone (us-east-1a). The RDS is a db.r3.2xlarge instance (vCPU - 8 (cores?), ECU 26, 61 GB RAM, 100 GB storage). I SSH into the EC2 instance and run the script. The PHP portion took 70-80% longer. I expected that, because of network latency connecting to an RDS vs. localhost.

What's more disappointing is that the pure MySQL INSERT queries also took longer. Some of them were the same speed, some were a little faster, but overall they were 35% slower.

The IOPS seemed really low, and the InnoDB buffer usage as well. It really seemed like it wasn't using the resources it has.



Any suggestions? Is this something that Provisioned IOPS could help with?



Update

Different innodb_* variables:

Variable         | Dev server   | RDS
-----------------|--------------|------------
buffer_pool_size | 128 M        | 44 G
flush_method     | (blank)      | O_DIRECT
log_file_size    | 48 M         | 128 M
version          | 5.6.33       | 5.6.19
Looks like my initial theory was correct. 

A standard RDS is capped at 3 IOPS per GB of storage, hence why my writes were hovering around 332 on a 100 GB instance. Not sure how they went over 300; maybe Amazon is not strict about it or maybe MySQL Workbench just had latency and calculated it slightly off. 

Anyways we launched a new RDS with provisioned IOPS set to 1,000. Now the queries are running 3x faster than they were before.

It does seem strange to me that the limit is based on the storage size. Those two aren't necessarily directly related in my opinion.