Faster alternative to MySQL mysqldump backup & restore with multi-threaded Mydumper & Myloader tool
Mydumper is a multi-threaded MySQL backup and restore tool written in C by MySQL engineers who worked for MySQL and later moved to Facebook, SkySQL etc. Mydumper claims to be up to 10x times faster than mysqldump for backups. I’ve even suggested to WHM/Cpanel folks to incorporate Mydumper for faster MySQL database backups and restores and lately the topic of Mydumper has come up at various community forums I frequent. Update: For InnoDB back and restore comparisons check out Mydumper/Myloader article here.
So I decided to do a proper comparison benchmark between MySQL default backup and restore method via mysqldump/mysql which is single threaded in nature with MyDumper. I setup a local built test server for this test as financially can’t afford to rent a server with >8 cpu threads just for benchmarks such as these and my MySQL fork comparison tests I plan to revisit.
I used Sysbench v0.50 to generate a test MyISAM based database called sbtest which consists of 48 tables with each table containing 2 million rows to total 18,935 MB in size.
I then used a script I wrote, mydumperbench.sh to run and time the benchmark tests for MySQL mysqldump/mysql and then MyDumper’s mydumper/myloader 12 cpu thread tests. All disk caches were cleared and MySQL server were restarted for each test. The raw results along with csv formatted version are outlined below along with system configuration used to run these tests.
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 MyISAM database size: 18,935 MB
Results
MySQL backup speed
- mysqldump backup: 18,935MB backed up in 208.78 seconds = 90.694 MB/s or 318.8 GB/hr
- mydumper backup 12 threads: 18,935 backed up in 76.20 seconds = 248.49 MB/s or 873.6 GB/hr
MySQL restore speed
- mysql restore: 18,398 MB sql file restored in 856.40 seconds = 21.48 MB/s or 75.5 GB/hr
- myloader restore 12 threads: restored in 174.57 seconds = 105.39 MB/s or 370.5 GB/hr
Conclusion
Mydumper’s default action is to backup and restore all databases in MySQL data directory which fullty utilises the multi-threaded nature of the tool. You’ll most likely see much higher speed up gains compared to mysqldump –all-databases method. But the single database backup and restore tests reveal:
- mydumper was 2.74x times faster than mysqldump for MySQL backup
- mydumper’s myloader tool was 4.9x times faster than mysql for MySQL restore / import
Notes:
Mydumper, currently does not work with MySQL.com Community release 5.5 and only works with MySQL.com Community release 5.1 and MariaDB 5.2, 5.3 and 5.5 (confirmed).
Due to Intel Core i7 3930K Turbo mode, single threaded mysqldump backup/restore task would of Turbo boosted cpu frequency for single thread task to 3.8Ghz vs all 12 cpu threads engaging 6 cpu cores at Turbo boost frequency of 3.5Ghz cpu frequency. Could account for mysqldump speed not being too far off from mydumper in the above comparison tests.
Real time cpu frequency variations due to Intel Turbo boost mode for Intel Core i7 3930K Sandy Bridge-E based processor with single thread mysqldump.
Cpu speed from cpuinfo 3199.00Mhz cpuinfo might be wrong if cpufreq is enabled. To guess correctly try estimating via tsc Linux inbuilt cpu_khz code emulated now True Frequency (without accounting Turbo) 3199 MHz CPU Multiplier 32x || Bus clock frequency (BCLK) 99.97 MHz Socket [0] - [physical cores=6, logical cores=12, max online cores ever=6] TURBO ENABLED on 6 Cores, Hyper Threading ON True Frequency 3298.97 MHz (99.97 x [33]) Max TURBO Multiplier (if Enabled) with 1/2/3/4/5/6 Cores is 38x/38x/37x/36x/35x/35x Current Frequency 3756.44 MHz [99.97 x 37.58] (Max of below) Core [core-id] :Actual Freq (Mult.) C0% Halt(C1)% C3 % C6 % C7 % Temp Core 1 [0]: 3756.44 (37.58x) 1 53.6 9.16 0 36.1 33 Core 2 [1]: 3705.77 (37.07x) 1 94.2 0 0 5.67 38 Core 3 [2]: 3734.13 (37.35x) 1 6.64 1.47 0 91.6 28 Core 4 [3]: 3733.75 (37.35x) 1 0.398 1 0 98.5 31 Core 5 [4]: 3737.30 (37.38x) 1 1.52 0 0 98.3 29 Core 6 [5]: 3716.14 (37.17x) 1 1.74 1 0 97.1 23 C0 = Processor running without halting C1 = Processor running with halts (States >C0 are power saver) C3 = Cores running with PLL turned off and core cache turned off C6 = Everything in C3 + core state saved to last level cache |
Cpu frequency when using mydumper with 12 cpu threads, seems cpu frequency fluctuates between 3.58Ghz and 3.67Ghz
Cpu speed from cpuinfo 3199.00Mhz cpuinfo might be wrong if cpufreq is enabled. To guess correctly try estimating via tsc Linux inbuilt cpu_khz code emulated now True Frequency (without accounting Turbo) 3200 MHz CPU Multiplier 32x || Bus clock frequency (BCLK) 100.00 MHz Socket [0] - [physical cores=6, logical cores=12, max online cores ever=6] TURBO ENABLED on 6 Cores, Hyper Threading ON True Frequency 3300.00 MHz (100.00 x [33]) Max TURBO Multiplier (if Enabled) with 1/2/3/4/5/6 Cores is 38x/38x/37x/36x/35x/35x Current Frequency 3674.95 MHz [100.00 x 36.75] (Max of below) Core [core-id] :Actual Freq (Mult.) C0% Halt(C1)% C3 % C6 % C7 % Temp Core 1 [0]: 3674.95 (36.75x) 49.1 36.6 1.83 1 4.11 35 Core 2 [1]: 3654.22 (36.54x) 35 47.5 5.41 1 6.08 32 Core 3 [2]: 3607.09 (36.07x) 23.6 8.96 3.15 1 60.3 25 Core 4 [3]: 3590.89 (35.91x) 17.6 19.8 2.14 1.09 57.2 31 Core 5 [4]: 3587.04 (35.87x) 8.6 14.3 19.9 0 56.2 27 Core 6 [5]: 3580.93 (35.81x) 9.03 2.18 1 0 86.7 22 C0 = Processor running without halting C1 = Processor running with halts (States >C0 are power saver) C3 = Cores running with PLL turned off and core cache turned off C6 = Everything in C3 + core state saved to last level cache Above values in table are in percentage over the last 1 sec |
Raw benchmark results on next page …
