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:
Backup is 80G
Copy is at 70MB/s.
10G is already complete.
= ((80-10) * 1024)/70/60 = ~17 minutes
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”.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.