From mituzas.lt
http://mituzas.lt/2009/02/03/mydumper/
“Last weekend I ended up working on small pet project – and today I’m kind of releasing it.
So, I had that idea that there’s no good tool to do logical dump of MySQL data for large sites – mysqldump doesn’t provide too much of I/O pressure, mk-parallel-dump is closer, but it doesn’t do consistent snapshots, uses same mysqldump, as well as is written in Perl (haha!), and… I just wanted something new to hack, as proof of concept. For a while to use it one had to edit constants in code, but my colleague Mark contributed options support and it doesn’t need recompiles anymore to run it 🙂
So, let me introduce mydumper. It doesn’t dump table definitions, all it does is extracting data and writing it to files, fast.
I took ~20GB-sized French Wikipedia core database (SHOW TABLE STATUS), and tried dumping it with three different methods – mysqldump, mk-parallel-dump and mydumper (used 32 thread, chunked backup setting for last two).
Dump times, smaller is better:
mysqldump: 75m18s maatkit: 8m13s mydumper: 6m44s \o/ WINNER \o/
There’s no cache skew – I restarted mysqld before every test, and it is using O_DIRECT.
At certain moments it seemed like gigabit network wasn’t enough for the test… It seems, it was using underlying I/O properly too:
avg-cpu: %user %nice %system %iowait %steal %idle
12.58 0.00 3.28 48.14 0.00 36.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
sda 19.00 26.00 3077.00 6.00 151720.00 306.00
avgrq-sz avgqu-sz await svctm %util
49.31 32.09 10.27 0.32 100.00
Though, once I tried from warm caches, and saw 2m rows read a second, I had a warm fuzzy feeling 🙂
Apparently the trick of having successful fast mysql dump was applying lots of pressure to underlying storage as well as using multi-processor capabilities. So we do, so can you!
Oh, and easiest way to start is:
bzr co lp:mydumper/0.1 cd mydumper make ./mydumper --help
Alternatively, one can use ‘lp:mydumper’ to get trunk – though various things (like startup options) can change. Feel free to file bugs, ask questions, and contribute with anything you think is worth contributing (thats why it ended up on Launchpad).
FAQs page can have answers to questions that might arise too 🙂
Update: Added also downloadable archive for bzr impaired at the downloads page.”
This is not my work. Pulled from the http://mituzas.lt/2009/02/03/mydumper/ site. But it was such a good tool, had to mention it here.
