MySQL Back up & Restore: Faster InnoDB database backup / restore using Mydumper and Myloader multi-threaded tools

Previous article compared MyISAM database backup methods for mysqldump and multi-threaded Mydumper/Myloader tools. This time testing a full 100% InnoDB database and comparing InnoDB backup and restore times with mysqldump non-blocking (–single-transaction ) backup and restore with Mydumper and Myloader multi-threaded backup tools.

When it comes to backup and restores, InnoDB databases and tables will generally be slower than MyISAM database – especially when it comes to InnoDB restore times. Let’s see how much faster Mydumper and Myloader can make the InnoDB database and tables backup and restore process.

Using the same system outlined in previous article so Mydumper and Myloader were tested with 12 cpu threads:

System Configuration:

• Intel Core i7 3930K Sandy Bridge-E (6 cpu cores/12 cpu threads)
• Gigabyte X79-UD5 F8d bios
• 8GB DDR3 @1600Mhz 9-9-9-24 1T (4x2GB A-Data 2000X)
• 4x 60GB Gskill Phoenix Pro SSD (~29% over-provisioning)
• Software Raid 1 /boot (md0) and Raid 0 for root / (md1)
• CentOS 6.2 64Bit
• Centmin Mod v1.2.2-eva2000.09
• – Nginx v1.1.12, PHP 5.3.8 php-fpm, APC Cache v3.1.9
• – Memcached v1.4.10, Python v2.7.2 and CSF Firewall
• – MariaDB 5.2.10 MySQL server
• – MyDumper v0.5.1 with 12 cpu threads test

Test InnoDB database size: I used Sysbench v0.50 to generate a test InnoDB based database called sbtest which consists of 48 tables with each table containing 2 million rows to total 22,435 MB in size with on disk size of 23GB.

+--------+--------+----------+------------+-----------+------------+---------+
| engine | tables | rows     | data       | idx       | total_size | idxfrac |
+--------+--------+----------+------------+-----------+------------+---------+
| InnoDB |     49 | 96004641 | 20118.45MB | 1824.86MB | 21943.31MB |    0.09 |
| MyISAM |     21 | 1992     | 0.52MB     | 0.09MB    | 0.61MB     |    0.17 |
| Aria   |      8 | NULL     | 0.06MB     | 0.06MB    | 0.13MB     |    1.00 |
| MEMORY |     44 | NULL     | 0.00MB     | 0.00MB    | 0.00MB     |    NULL |
| CSV    |      2 | 4        | 0.00MB     | 0.00MB    | 0.00MB     |    NULL |
+--------+--------+----------+------------+-----------+------------+---------+

Results

MySQL backup speed

• mysqldump backup: 22,435MB backed up in 444.21 seconds = 50.50 MB/s or 177.53 GB/hr
• mydumper backup 12 threads: 22,435MB backed up in 165.61 seconds = 135.46 MB/s or 476.22 GB/hr

MySQL restore speed

• mysql restore: 18,397 MB sql file restored in 1261.77 seconds = 14.58 MB/s or 51.25 GB/hr
• myloader restore 12 threads: 18,528 MB sql files cumulative size restored in 353.62 seconds = 53.92 MB/s or 189.56 GB/hr

Conclusion

InnoDB database backup and restore overall is slower than with MyISAM database backup and restore previously tested.

• mydumper was 2.68x times faster than mysqldump for MySQL InnoDB backup
• mydumper’s myloader tool was 3.7x times faster than mysql for MySQL InnoDB restore / import

Raw benchmark results on next page …