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
SHOW VARIABLES LIKE 'innodb%';
?