3

tl;dr - How can we get the maximum EBS throughput for a db.r3.2xlarge instance? We're only seeing about half of what it can supposedly do.

Apologies for the long length of this question. We've had quite a lot of back and forth on this, and I want to give as much information as possible.

We have a MySQL database that contains 3 large tables, ~300GB in total. We're looking to move these tables from a server in our office to an AWS RDS instance.

The data itself is not production data. It's used internally only and is more data warehouse kind of stuff, so we don't need to worry about keeping things in sync, etc. As such, we're taking the mysqldump and mysql ... < dump.sql approach.

We're not having issues with the actual import per se, our issue is that it's going slower than we'd expect. We've eliminated obvious factors, i.e. we're importing from an EC2 instance that contains the SQL dumps, bin log is disabled, auto-commit is off, etc, so we'd like to understand why this is.

We initially started with a db.r3.xlarge instance with 500GB of storage and 5000 provisioned IOPS. During the import, we saw write IOPS hovering around the ~2800/s mark, and EBS write throughput hovering ~58MB/s. Network was barely at ~1.3MB/s and CPU was ~13%.

As I come from a developer background, my infrastructure knowledge is OK but limited, so I wanted to understand what was the bottle neck. Based on the numbers I was seeing, I assumed it was disk IO, and after doing some research I believed this page backed up that theory.

The db.r3.xlarge comes with 500Mb/s dedicated EBS throughput, which works out to be 62.5MB/s, matching roughly the number we were seeing. So to get the throughput to increase, we assumed upgrading to the db.r3.2xlarge with its 1000Mb/s dedicated EBS throughput would give us write throughput of ~125MB/s.

This is where the confusion starts. We provisioned a db.r3.2xlarge instance with 800GB of storage and 8000 provisioned IOPS, but only saw an EBS write throughput of ~63MB/s, sometimes hitting ~70MB/s. Write IOPS were now ~3400/s and CPU was ~5%.

Based on this, my only assumption is that disk IO is now not the bottle neck, and something else is throttling the imports. Is this assumption correct, or is there something else going on re: EBS throughput we're not understanding?

We also triggered two separate table imports on two separate MySQL connections, and although the CPU and EBS write throughput, etc, went up, it only went up by a factor of ~5%.

As stated before, the import is happening fine, it just takes a while. We just want to understand why we're seeing the numbers we are seeing.

Thanks for your time.

Edit 1: After doing some more reading, I'm further inclined to believe that our import is being throttled by something. Our queue length never goes above 10 and hovers ~8. From what I've read, this means we're not generating enough IO to fully utilise what we have.

  • 1
    A serial SQL logical import will rarely use all database resources. Try to divide the SQL file into several pieces and import them separatelly (e.g. 8 threads, each on a different table). For the future, never use mysqldump again. – jynus Mar 5 '15 at 11:29
  • @jynus Care to expand why we shouldn't use mysqldump? – Stephen Melrose Mar 5 '15 at 14:00
  • 1
    I made a slightly larger argument here: dba.stackexchange.com/questions/94149/… Now that you are not limited by RDS you have better options; even logical ones like mydumper and mysqldbimport. – jynus Mar 5 '15 at 14:51
  • @jynus Why are we not limited by RDS? We're migrating to RDS. Also, one of the reason we went for mysqldump was to convert a table from MyISAM to InnoDB at the same time as the migration. – Stephen Melrose Mar 5 '15 at 14:54
  • Sorry, I misread that you were exporting from RDS to EC2. Those 2 logical tools I mentioned still stand, for my argument. You migration is ok, just try to load several tables at the same time, or if using InnoDB 5.6, to the same table in concurrency (which these tools automatize for you). – jynus Mar 5 '15 at 14:58

Your Answer

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

Browse other questions tagged or ask your own question.