RDS restore from dump time

Doing recovery test for one of my clients. I think recovery times might be useful to not-so-general public 🙂

TLDR version

.sql.gz file size: 829M
DB size after restoring: 6.2G
RDS instance: db.m1.small, MySQL 5.5.20, 50G, us-east-1b, not Multi-AZ.
Recovery tests date: 2012-08-14
Network: MySQL client in same AZ
Instance provisioning time: 8 minutes
Dump loading time: 58 minutes

More info

RDS parameter group: default.mysql5.5

RDS backups configuration: 35 days of backups

Restore command: time pv X.2.sql.gz | gunzip | mysql X

Restore command exact times:

real 57m35.001s
user 2m27.085s
sys 0m22.113s

Backup command: mysqldump --add-drop-table --add-locks --create-options --create-options --extended-insert --single-transaction --quick

Backup time: about 28 minutes.

Client: m1.small

Load on client when restoring: about 0.01, above 90% CPU idle.

Tables sizes info after recovery: SELECT data_length / 1024 / 1024 as "data", index_length / 1024 / 1024 as "index" FROM information_schema.TABLES where table_schema = 'MASKED' :

+---------------+---------------+
| data          | index         |
+---------------+---------------+
|    0.01562500 |    0.03125000 |
|    0.01562500 |    0.01562500 |
|    0.01562500 |    0.00000000 |
|    0.01562500 |    0.00000000 |
|    0.01562500 |    0.00000000 |
|    0.01562500 |    0.01562500 |
|    0.01562500 |    0.01562500 |
|    0.01562500 |    0.00000000 |
|    0.00000000 |    0.00000000 |
|    3.51562500 |    0.00000000 |
|    0.01562500 |    0.04687500 |
|    0.01562500 |    0.03125000 |
|    0.01562500 |    0.01562500 |
|    0.01562500 |    0.01562500 |
|    0.19713211 |    0.01269531 |
|    0.20276260 |    0.04687500 |
|    0.01562500 |    0.00000000 |
|    0.12012100 |    0.03613281 |
|    0.00007629 |    0.00195313 |
|    0.01562500 |    0.00000000 |
|    1.51562500 |    1.51562500 |
| 2053.00000000 | 1946.00000000 |
|   28.56250000 |   31.09375000 |
|    0.01562500 |    0.00000000 |
|    0.26562500 |    0.12500000 |
|    0.01562500 |    0.01562500 |
|    0.00000000 |    0.00000000 |
|    0.00000000 |    0.00000000 |
|    0.00000000 |    0.00195313 |
|   12.56250000 |    0.00000000 |
|    0.34375000 |    0.00000000 |
|    0.46875000 |    0.00000000 |
|    0.01562500 |    0.00000000 |
| 2139.00000000 |    0.00000000 |
+---------------+---------------+

Graphs from AWS RDS console

Screenshots were taken few minutes after restore finished.

HTH, Ilya.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s