1

We need to restore a database from a 7-Gb mysqldump file to a RDS Amazon instance. The full restore takes between 3 and 4 hours (probably because of network latency since the dump has to reside on a EC2), and it's too long for us. For reference, a restore to a local mysqld would take less than 30 minutes.

Is there a Amazon-related solution that would allow us to reduce the import time?

Questions about optimizing a mysqldump restore time have been asked before, such as:

However, this question is different because relates to a RDS instance, so:

  • Additional constraints are applied, for instance some MySQL variables (e.g. innodb_doublewrite) cannot be changed to improve performances
  • There may be some Amazon-native solution or product that would solve this issue easily and nicely
Improve this question
1
  • What RDS Instance type do you use? What Storage Type and Size do you use? Is it a new or already used instance? If it's a new instance (just launched) you should warn up storage.
    – Alexey
    Oct 28, 2016 at 19:48

1 Answer 1

Reset to default
3

You must now think in terms of the RDS Instance

  • Provisoning more IOPs
  • Is it safe to use innodb_flush_log_at_trx_commit = 2
    • Set innodb_flush_log_at_trx_commit = 2
    • Set sync_binlog=0
  • Since max_allowed_packet = 1M by default in Amazon RDS, this could cause mysqldump's multirow inserts (extended inserts) could fail. You may want to mysqldump the database with --skip-extended-insert. This will guarantee the data being inserted one-by-one, but the dump will be much larger and take longer to import.

Please read What I learned while migrating a customer MySQL installation to Amazon RDS from Percona's Blog for even deeper insights.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.