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.
mysqldump
was to convert a table from MyISAM to InnoDB at the same time as the migration.