Filed with Developers Support MyDumper (Faster MySQL dumping) [Case 59373]

[eva2000]

maybe cpanel should include mydumper support, much faster mysql database backup than mysqldump - 3x to 10x times faster due to mydumper being multi-threaded

from my own mydumper mysql backup tests

• mydumper = 1,008MB per minute that’s ~59GB per hour!
• mysqldump = 315MB per minute that’s ~18.5GB per hour

The test was done on virtualised Xeon W3550 3.06Ghz 1.5GB SATA disk based CentOS 5.5 64bit server but with only 2 cpu cores allocated. So it could potentially be much faster with more cpu cores/threads. Mydumper authors claim up to 10x times faster than mysqldump!.

Mydumper (aka. MySQL Data Dumper) is a high-performance multi-threaded backup (and restore) toolset for MySQL and Drizzle. The main developers originally worked as Support Engineers at MySQL (one has moved to Facebook and another to SkySQL) and this is how they would envisage mysqldump based on years of user feedback.

Mydumper features

• Lightweight C source
• Up to 10x faster dumps compared to mysqldump
• Consistent snapshots for transactional and non-transactional tables (in 0.2.2 onwards)
• File compression on-the-fly
• Binary log dumps
• Multi-threaded restore utility (in 0.2.1 onwards)
• Daemon mode for timed snapshots and continuous binary logs (in 0.5.0 onwards)
• Open Source! (GNU GPLv3)
• Mydumper is still under active development but is well tested/used in production on some large installations.

[cPanelDavidG]

Normally I'd keep the Mydumper post as part of this thread, but since this thread is so short and Mydumper can be used in places other than just the daily backup, I'm forking that onto its own thread for wider consideration.

[Infopro]

Count me in as interested in this. I'd like to hear other Administrator's opinions of course, but this does sound valuable to me.

[eva2000]

Sweet thanks for the consideration and splitting to it's own dedicated thread

Folks over at PalominoDB also did benchmarks for mydumper at http://palominodb.com/blog/2011/08/0...up-and-restore including database import speed comparisons

[bhanuprasad1981]

+1 for this , i hate to see complaints of slow sites while cpanel backups of large vb sites run.

[eva2000]

Yes it can take a while, had one client with 105GB sized database but that was before i found mydumper heh

[fkatzenb]

I would like to see this get bumped. This should go hand in hand with the pigz thread.

[InterServed]

Thumbs up for eva2000 ! Great additions that I weren't aware of (like pigz and now mydumper) !

+1 vote from me for this feature request.

[eva2000]

I would like to see this get bumped. This should go hand in hand with the pigz thread.

see you found this thread

Thumbs up for eva2000 ! Great additions that I weren't aware of (like pigz and now mydumper) !

+1 vote from me for this feature request.

Yes mydumper really speeds things up

[eva2000]

Finally got around to some more concrete benchmark comparisons between mysqldump vs mysqldumper mydumper vs mysqldump

Database size = 18,935 MB

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

I like

[Infopro]

Very nice, George. Well done article, again as always too!

[eva2000]

Thanks... just overlooked one factor need to update article. Due to Core i7 3930K Turbo mode, single threaded 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 mode 3.5Ghz cpu frequency. Could account for mysqldump speed not being too far off

[InterServed]

Hopefully cPanel will take the time to implement this as an optional selection to be used on mysql dumps during backups.

[fkatzenb]

No kidding. This is rather huge!

[eva2000]

Gets even more huge in terms of times when backing up and restoring InnoDB databases

MySQL InnoDB 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 InnoDB 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