Faster alternative to MySQL mysqldump backup & restore with multi-threaded Mydumper & Myloader tool
mysqldump backup
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 |
mysql restore
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 |
mydumper backup
[root@sbe tools]# ./mydumperbench.sh mydumper ---------------------------------------------------------------------------------------- mydumperbench.sh 0.0.4 by George Liu (eva2000) http://vbtechsupport.com Sat Jan 7 01:24:04 EST 2012 ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- [sbtest total size: 18935 MB] [mydumper threads: 12 ] mydumper -t 12 -B sbtest [backup stats:] real: 76.20s user: 77.54s sys: 17.08s cpu: 124% maxmem: 76336 KB cswaits: 48093 Successfully backed database using mydumper using threads ---------------------------------------------------------------------------------------- [root@sbe tools]# cat /root/mydumperlogs/mydumpercsv_070112-012404.txt threads,real time, user time, system time, cpu %, max mem, context switch waits 12,76.20,77.54,17.08,124,76336,48093 |
myloader restore
[root@sbe tools]# ./mydumperbench.sh myloader ---------------------------------------------------------------------------------------- mydumperbench.sh 0.0.4 by George Liu (eva2000) http://vbtechsupport.com Sat Jan 7 02:20:57 EST 2012 ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- [myloader threads: 12 ] myloader -t 12 -d export-20120107-012606 -B sbtest -q 10000 [restore stats:] real: 174.57s user: 24.38s sys: 12.33s cpu: 21% maxmem: 69888 KB cswaits: 151576 Successfully restored mydumper backup using threads ---------------------------------------------------------------------------------------- [root@sbe tools]# cat /root/mydumperlogs/myloadercsv_070112-022057.txt threads,real time, user time, system time, cpu %, max mem, context switch waits 12,174.57,24.38,12.33,21,69888,151576 |
hdparm disk speed
[root@sbe tools]# hdparm -tT /dev/md0 /dev/md0: Timing cached reads: 27012 MB in 2.00 seconds = 13520.92 MB/sec Timing buffered disk reads: 398 MB in 2.31 seconds = 172.36 MB/sec [root@sbe tools]# hdparm -tT /dev/md1 /dev/md1: Timing cached reads: 26906 MB in 2.00 seconds = 13467.27 MB/sec Timing buffered disk reads: 2566 MB in 3.00 seconds = 855.04 MB/sec [root@sbe tools]# dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync 16384+0 records in 16384+0 records out 1073741824 bytes (1.1 GB) copied, 1.56635 s, 686 MB/s |
ioping.sh random disk I/O
----------------------------------------- ioping.sh 0.9.8 - http://vbtechsupport.com by George Liu (eva2000) ----------------------------------------- ioping.sh 0.9.8 MENU ----------------------------------------- 1. Install ioping 2. Re-install ioping 3. Run ioping default tests 4. Run ioping custom tests 5. Exit ----------------------------------------- Enter option [ 1 - 5 ] 3 ----------------------------------------- *************************************************** ioping code.google.com/p/ioping/ ioping.sh 0.9.8 shell wrapper script by George Liu (eva2000) http://vbtechsupport.com *************************************************** *************************************************** Disk Schedulers & Read Ahead (Queue Size x 2): *************************************************** [/dev/md0] - Read Ahead: 256 none ---------------------------------- [/dev/md1] - Read Ahead: 8192 none ---------------------------------- [/dev/md2] - Read Ahead: 8192 none ---------------------------------- [/dev/sda] - Read Ahead: 256 noop anticipatory [deadline] cfq ---------------------------------- [/dev/sdb] - Read Ahead: 256 noop anticipatory [deadline] cfq ---------------------------------- [/dev/sdc] - Read Ahead: 256 noop anticipatory [deadline] cfq ---------------------------------- [/dev/sdd] - Read Ahead: 256 noop anticipatory [deadline] cfq ---------------------------------- *************************************************** ********************************** dd (sequential disk speed test)... ********************************** dd if=/dev/zero of=testfilex bs=64k count=16k conv=fdatasync 16384+0 records in 16384+0 records out 1073741824 bytes (1.1 GB) copied, 1.52383 s, 705 MB/s ************************ starting ioping tests... *************************************************** ioping disk I/O test (default 1MB working set) *************************************************** disk I/O: /dev/md0 --- /dev/md0 (device 0.4 Gb) ioping statistics --- 5 requests completed in 4001.7 ms, 3965 iops, 15.5 mb/s min/avg/max/mdev = 0.2/0.3/0.3/0.0 ms disk I/O: /dev/md1 --- /dev/md1 (device 159.3 Gb) ioping statistics --- 5 requests completed in 4002.3 ms, 2746 iops, 10.7 mb/s min/avg/max/mdev = 0.1/0.4/1.1/0.4 ms disk I/O: /dev/md2 --- /dev/md2 (device 15.6 Gb) ioping statistics --- 5 requests completed in 4001.2 ms, 6570 iops, 25.7 mb/s min/avg/max/mdev = 0.1/0.2/0.2/0.0 ms ********************************************** seek rate test (default 1MB working set) ********************************************** seek rate: /dev/md0 --- /dev/md0 (device 0.4 Gb) ioping statistics --- 11009 requests completed in 3000.0 ms, 4563 iops, 17.8 mb/s min/avg/max/mdev = 0.1/0.2/3.1/0.0 ms seek rate: /dev/md1 --- /dev/md1 (device 159.3 Gb) ioping statistics --- 18363 requests completed in 3000.2 ms, 9250 iops, 36.1 mb/s min/avg/max/mdev = 0.1/0.1/3.0/0.1 ms seek rate: /dev/md2 --- /dev/md2 (device 15.6 Gb) ioping statistics --- 21847 requests completed in 3000.1 ms, 12080 iops, 47.2 mb/s min/avg/max/mdev = 0.1/0.1/2.0/0.0 ms ********************************************** sequential test (default 1MB working set) ********************************************** ----------------------- sequential: /dev/md0 --- /dev/md0 (device 0.4 Gb) ioping statistics --- 1781 requests completed in 3000.5 ms, 614 iops, 153.6 mb/s min/avg/max/mdev = 1.3/1.6/2.1/0.2 ms ----------------------- sequential cached I/O: /dev/md0 --- /dev/md0 (device 0.4 Gb) ioping statistics --- 39247 requests completed in 3000.0 ms, 40702 iops, 10175.5 mb/s min/avg/max/mdev = 0.0/0.0/0.0/0.0 ms ----------------------- sequential: /dev/md1 --- /dev/md1 (device 159.3 Gb) ioping statistics --- 2140 requests completed in 3001.1 ms, 743 iops, 185.7 mb/s min/avg/max/mdev = 1.1/1.3/3.1/0.2 ms ----------------------- sequential cached I/O: /dev/md1 --- /dev/md1 (device 159.3 Gb) ioping statistics --- 11883 requests completed in 3000.1 ms, 5051 iops, 1262.9 mb/s min/avg/max/mdev = 0.0/0.2/2.4/0.4 ms ----------------------- sequential: /dev/md2 --- /dev/md2 (device 15.6 Gb) ioping statistics --- 2616 requests completed in 3001.1 ms, 914 iops, 228.6 mb/s min/avg/max/mdev = 1.1/1.1/1.7/0.0 ms ----------------------- sequential cached I/O: /dev/md2 --- /dev/md2 (device 15.6 Gb) ioping statistics --- 12561 requests completed in 3000.1 ms, 5365 iops, 1341.2 mb/s min/avg/max/mdev = 0.0/0.2/4.4/0.4 ms |
