I have a 5G mysqldump which takes 30 minutes to restore from backup. That means that when the database reaches 50G, it should take 30×10=5 hours to restore. Right? Wrong.
Mysqldump recovery time is not linear. Bigger tables, or tables with more indexes will always take more time to restore.
If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take:
|
1 |
Backup is 80G<br>Copy is at 70MB/s.<br>10G is already complete.<br>= ((80-10) * 1024)/70/60 = ~17 minutes<br> |
I can tell progress with mysqldump by monitoring the rate at which show global status like 'Handler_write'; increases and compare it to my knowledge of about how many rows are in each table. But progress != a magic number like “17 minutes”. Not unless I do a lot of complex modeling.
I am not saying a 5 hour recovery is good or bad. What I am saying is knowing remaining time is very important during disaster recovery. Being able to say “we’ll be back at 2PM” is much better than saying “we’ll be back between 1PM and 4PM.. maybe”.
Resources
RELATED POSTS
