I just wrapped up a test of the new mysql-parallel-dump and mysql-parallel-restore that Baron Schwartz has released as part of his toolkit. The toolkit is available from http://mysqltoolkit.sourceforge.net

I tested on an 8-core (dual processor/quad-core) server with 8 gigabytes of RAM. I baselined with the normal mysqldump command (no options) and then benchmarked variations of both the mysqldump command and the mysqlimport command against the mysql-parallel-dump and mysql-parallel-restore command.

The database tested was under two gigs in size so it wasn’t an ideal test but I was just trying to get an idea of how I can expect it to operate.

The results:

Test One: Parallel Dump & Restore

rm -rf /home/kmurphy/pdump/default/*time mysql-parallel-dump --databases pdump --tab --basedir=/home/kmurphy/pdump

mysql -e 'drop database pdump'

mysql -e 'create database pdump'

	run time
run one	0m22.166s
run two	0m22.348s
run three	0m21.625s
run four	0m22.723s
run five	0m16.744s
run six	0m24.603s
run seven	0m16.647s
run eight	0m21.449s
run nine	0m18.590s
run ten	0m21.975s
average
20.887s

time mysql-parallel-restore --tab /home/kmurphy/pdumpmysql -e 'reset master'

	run time
run one	4m3.048s
run two	4m24.352s
run three	4m25.403s
run four	4m8.525s
run five	4m18.319s
run six	4m7.668s
run seven	4m32.558s
run eight	4m29.318s
run nine	4m10.424s
run ten	4m53.700s
average
4m21.332s

Test Two: Parallel Dump & Restore w/Chunk Size of 100,000

rm -rf /home/kmurphy/pdump/default/*time mysql-parallel-dump --databases pdump --tab --chunksize=100000 --basedir=/home/kmurphy/pdump

mysql -e 'drop database pdump'

mysql -e 'create database pdump'

	run time
run one	0m16.207s
run two	0m14.899s
run three	0m14.988s
run four	0m15.331s
run five	0m15.112s
run six	0m16.124s
run seven	0m15.902s
run eight	0m14.763s
run nine	0m14.903s
run ten	0m15.261s
average
15.394s

time mysql-parallel-restore --tab /home/kmurphy/pdumpmysql -e 'reset master'

	run time
run one	4m27.080s
run two	4m43.456s
run three	4m32.554s
run four	4m27.986s
run five	5m3.333s
run six	4m48.599s
run seven	4m14.181s
run eight	5m7.383s
run nine	4m44.915s
run ten	5m3.911s
average
4 min 39.34s

Test Three: Default Dump & Restore

time mysqldump --databases pdump > /data/backup/testing/normal/dumptest.sql

	run time
run one	0m38.788s
run two	0m43.511s
run three	0m39.406s
run four	0m41.640s
run five	0m40.168s
run six	0m39.696s
run seven	0m43.360s
run eight	0m43.546s
run nine	0m43.370s
run ten	0m42.890s
average
41.638s

time mysql < /data/backup/testing/normal/opt/dumptest.sqlmysql -e 'reset master'

	run time
run one	9m33.572s
run two	9m32.136s
run three	9m28.766s
run four	9m32.804s
run five	9m31.367s
run six	9m32.018s
run seven	9m33.421s
run eight	9m30.748s
run nine	9m34.836s
run ten	9m29.171s
average
9m31.884s

Test Four: Optimized standard dump with grouped insert statements and restore

time mysqldump --opt -e --databases pdump > /home/kmurphy/dump/dumptest.sql

	run time
run one	0m43.553s
run two	0m44.514s
run three	0m39.177s
run four	0m41.644s
run five	0m41.356s
run six	0m40.577s
run seven	0m39.982s
run eight	0m39.553s
run nine	0m41.970s
run ten	0m41.008s
average
41.333s

time mysql < /home/kmurphy/dump/dumptest.sqlmysql -e 'reset master'

	run time
run one	9m17.282s
run two	9m28.965s
run three	9m29.265s
run four	9m35.659s
run five	9m31.757s
run six	9m32.542s
run seven	9m27.294s
run eight	9m28.672s
run nine	9m32.159s
run ten	9m27.542s
average
9m29.114s

Test Five: Optimized mysqldump and restore w/tab delimited files

time mysqldump -Q -T /home/kmurphy/dump/pdump  --opt --single-transaction pdumpcat /home/kmurphy/dump/pdump/*.sql > /home/kmurphy/dump/pdump/xyz.sql

time mysql pdump < /home/kmurphy/dump/pdump/xyz.sql

	run time
run one	38.188s
run two	33.321s
run three	33.663s
run four	33.096s
run five	33.268s
run six	33.203s
run seven	33.352s
run eight	33.073s
run nine	37.682s
run ten	33.196s
average
34.204s

time mysqlimport pdump /home/kmurphy/dump/pdump/*.txtrm /home/kmurphy/dump/pdump/xyz.sql

mysql -e 'reset master'

run time
run one	5.348s + 6m10.926s
run two	4.905s + 6m11+366s
run three	6.770s + 6m11.556s
run four	5.859s + 6m5.699s
run five	6.066s + 6m10.076s
run six	6.478s + 6m6.743s
run seven	5.422s + 6m7.818s
run eight	6.055s + 6m14.252s
run nine	5.016s + 6m11.458s
run ten	6.730s + 6m16.509s
average
6m16.505s

Test Six: Optimized mysqldump and restore w/tab delimited files and extended inserts

time mysqldump -Q -T /home/kmurphy/dump/pdump  --opt --single-transaction -e pdump

run time
run one	33.164s
run two	33.463s
run three	33.239s
run four	33.182s
run five	33.140s
run six	33.596s
run seven	33.115s
run eight	33.063s
average
33.245s

cat /home/kmurphy/dump/pdump/*.sql > /home/kmurphy/dump/pdump/xyz.sqltime

time mysql pdump < /home/kmurphy/dump/pdump/xyz.sql

time mysqlimport pdump /home/kmurphy/dump/pdump/*.txtrm /home/kmurphy/dump/pdump/xyz.sql

mysql -e 'reset master'

run time
run one	5.846s + 6m12.939s
run two	5.195s + 6m10.240s
run three	6.221s + 6m9.272s
run four	5.431s + 6m10.620s
run five	6.297s + 6m15.142s
run six	5.740s + 6m12.571s
run seven	6.551s + 6m15.873s
run eight	6.293s + 6m11.707s
average
6m18.242s

The results are quite impressive. The mysql-parallel-dump with chunksize option that I ran didn’t seem to make any difference. Don’t be fooled as this was on a smaller data set. If you have a multi-gigabyte table with millions of rows in innodb format this could make a huge difference. Don’t underestimate it.

The clear winner is the mysql-parallel-dump paired with mysql-parallel-restore. The parallel import/dump over a normal mysqldump w/various options is around twice as fast. Using mysqlimport with mysqldump brings the time difference down to around two minutes but it is still fairly significant. With a larger dataset I think the difference would become more pronounced.

In short, you should strongly consider using the parallel dump and restore tool for your backup needs if you are currently using mysqldump.

• Previous Entry: flush hosts command
• Next Entry: MySQL Magazine – Format/Policy Change??