mysql-parallel-dump test
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.
2 Comments so far
Leave a reply
Keith, I wonder how the tab-delimited dumps would fare with –no-gzip? They will probably be faster yet.
[…] Honestly, I hope MySQL’s tools make this pair of tools obsolete in the future, but until then, they’re a good way to dump and reload data at higher speeds. Keith Murphy did some measurements on parallel dump and restore speeds. […]