AWS RDS (mysql) strange IO behavior

Question

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?

Answer 1

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:

• innodb_read_io_threads (Reboot Required)
  • Default for MySQL 5.6 is 4
  • Percona Server default is 8
  • Please set this is 8
• innodb_write_io_threads (Reboot Required)
  • Default for MySQL 5.6 is 4
  • Percona Server default is 8
  • Please set this to 8 or 16
• innodb_thread_concurrency (Dynamic Option, Reboot Not Required)
  • Default for MySQL 5.6 is 0
  • Make sure it is 0
  • Percona recommended using 64 if you are using MySQL 5.7.