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
Community
  • 1
dr_
  • 1,334
  • 4
  • 19
  • 40

1 Answers1

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.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536