1

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?

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:

  • Thanks, but I already did that and it didn't make a difference – Mordechai Feb 7 '18 at 0:49
  • update - my instance had all the suggestions you made except for #of threads and thread concurrency which were default. I started another instance with the same exact configuration (leaving defaults in place) and it performed a lot better, though still didn't reach 6K throughput. I have a case open with AWS. will update as they get back to me. – Mordechai Feb 11 '18 at 15:53

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.