2

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.

enter image description here

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
Improve this question
8
  • Sorry I kept deleting and undeleting this because some of the queries were faster than others. But overall the MySQL portion was 35% slower – andrewtweber Mar 3 '17 at 21:03
  • What information have you been able to get our of the sys table as far as analyzing inserts? have you looked at any instrumentation to compare performance on the production versus the dev servers? – JPeck89 Mar 3 '17 at 21:57
  • What values are different in SHOW VARIABLES LIKE 'innodb%';? – Rick James Mar 4 '17 at 4:15
  • @RickJames there wasn't much different, I added to the question – andrewtweber Mar 4 '17 at 6:18
  • @JPeck89 not much I'm afraid. Only using MySQL Workbench to watch the server stats (in the screenshot) as the queries are running – andrewtweber Mar 4 '17 at 6:19
2

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.

Improve this answer
2
  • 1
    A guess on tying limits together: Simplicity in provisioning, sales, etc. – Rick James Mar 4 '17 at 23:50
  • 1
    A guess on "how the IOPs went over 300": Being more strict would probably involve low level hacks into the I/O, and would probably impact the performance more than is worth the effort. – Rick James Mar 4 '17 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.