I'm dealing with s db.r3.4xlarge RDS instance with 2000GB all purpose SSD (theoretically capable of up to 6000 IOPS), I'm restoring a bunch of table backup files of over 200GB. There are 26 simultaneous threads running, but Write IOPS is stuck at about 800. Queue depth is at 2 (give or take) and CPU is at 4-5%. Does this make sense? Why isn't the server using more IOPS available to it?
Mysql – AWS RDS (thesql) strange IO behavior
amazon-rdsMySQL
Best Answer
In your DB Parameter Group change innodb_flush_log_at_trx_commit from 1 to 0. This should speed up the writes since the default value writes completed transactions to the Redo Log Files (ib_logfile0, ib_logilfe1).
If you have binary logs enabled (when you enable Automated Backups), you can also change sync_binlog from 1 to 0.
You will not need to reboot since these two options are dynamic.
When your restore is complete, set them back their original values (innodb_flush_log_at_trx_commit back to 1, sync_binlog back to 1).
Give it a Try !!!
UPDATE 2018-02-02 14:27 EST
The only other thing I can think of would to increase the threads the CPUs use
If you look back at my 6.5 year old post Possible to make MySQL use more than one core?, I mention the following options:
488488or160064if you are using MySQL 5.7.