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

faster MySQL database backup and restore mydumper vs mysqldump

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 …

Pages: 1 2


Previous:
Next:



MaxCDN Site Acceleration

Get Cost Effective Content Delivery
Unbeatable speed, stats, & price
http://www.maxcdn.com



No Comments

Trackbacks/Pingbacks

  1. Gigabyte X79-UD5 - Intel LGA2011 | vbtechsupport.com - [...] in handy for Linux server virtualisation testing with up to 64GB DDR3 memory support! Update: First CentOS 6.2 64bit …
  2. MySQL Back up & Restore: Faster InnoDB database backup / restore using Mydumper and Myloader multi-threaded tools | vbtechsupport.com - [...] Myloader multi-threaded tools eva2000 9 Jan, 2012 in mysql, web tech | 0 commentsPrevious article compared MyISAM database backup …
  3. MariaDB 5.5 RPM builds ? | vbtechsupport.com - [...] for MariaDB 5.5.23, so going to test it out. Best of all, it seems this MariaDB 5.5 RPM works …
  4. MariaDB 5.5.24 Release working mydumper ! | vbtechsupport.com - [...] YUM repository.I decided to test and found out if the multi-threaded MySQL backup and restore tool, mydumper worked with …
Add Comment Register

Leave a Comment