On this page you can see the results of the comparative performance testing. Previously only PHP-scripts were compared , but the new Sypex Dumper 2 is so fast that we also decided to include desktop applications to the testing.
During testing, each participant was to create a backup copy of a test database, without compression, default settings.
Tests were run on Apache 2.2.11, MySQL 5.1.39 and PHP 5.2.11, running Windows 7 x64. Computer parameters Core 2 Quad Q6600 @ 3 GHz, 8 GB DDR2.
Round 1.
For the test, a database of real site was chosen. In the process of testing we had to reduce the number of entries, because many participants started working too slow.
MySQL test database:
-
CMS tables (including 20 000 news and 4 000 articles, with total of more than 60 MB);
-
other tables, including statistics (more than 18 MB).
So it was a total of 25 tables, 177 258 entries occupying 78 MB, with one part of the tables in cp1251, and the other in UTF8.
Results have been sorted by the export speed, since export is done more frequently.
| Software | Export (sec) | Import (sec) | Size (KB) |
|---|---|---|---|
| 2 | 5 | 83 033 | |
| mysqldump 10.13* + mysqlcli | 3 | 12 | 140 007 |
| 5 | 8 | 83 926 | |
| SQLyog Enterprize 8.14 | 6 | 12 | 140 018 |
| Navicat Premium 8.2.15 | 7 | 36 | 148 771 |
| phpMyAdmin 3.2.2.1 | 10 | 21 | 141 005 |
| MySQL Dumper 1.24 | 14 | 87 | 155 449 |
| dbForge Studio for MySQL 3.60.351.1 | 15 | 76 | 146 790 |
| MySQL-Front 5.1.4.13 | 16 | 128 | 146 400 |
| MySQL Administrator 1.2.17 | 34 | 18 | 140 398 |
| HeidiSQL 4.0.2677 | 63 | -** | 141 232 |
* - mysqldump run with --opt option.
** - execution aborted because of an error.
Comments on the results
Sypex Dumper is the only participant which applies automatic change of the encoding, depending on the encoding of objects. Due to this, it exports the data without conversion, which saves additional time. In addition, a special optimized file format is used, and a smart recovery; thanks to that the dumper minimizes the number of requests to the server and imports each object in the ‘native’ encoding.
Many participants did not know how to work in the mode ‘Extended Inserts’, which makes the importing of tables several times slower.
Round 2: Clash of the Titans.
The main problem of Sypex Dumper Lite 1.0.8 was that it begins to slow down on the tables with a very large number of records. This is clearly seen in the next test.
So, we decided to conduct further testing among the leaders, taking a database of a large size. The database is the same, but not reduced like in the previous test.
Same 25 tables, but already 242 321 records with total of 672 MB, some tables in cp1251, some in UTF8.
| Software | Export (sec) | Import (sec) | Size (KB) |
|---|---|---|---|
| 12 | 50 | 706 417 | |
| mysqldump 10.13 + mysqlcli | 52 | 90 | 1 251 329 |
| SQLyog Enterprize 8.14 | 57 | 92 | 1 251 497 |
| 87 | 54 | 709 243 |
Comments about mysqldump, and why the dumper is faster.
- Naturally, it is due to the fact that the dumper is working smarter with the encoding, and does not convert everything to UTF8 (if you make a separate backup of cp1251 tables, and a separate one for utf8, with making exact data encoding settings in mysqldump, the difference will be less).
- Dumper is greatly optimized; it does not have a couple of dozen of options like mysqldump (which leads the latter into many unnecessary tests in the process of export), so dumper makes backup in an optimal mode, in terms of speed.
- While testing it was noticed that mysqldump processes faster the tables with short records (i.e. with the average length of tens or hundreds of bytes) and there are hundreds of thousands of such records, in which case mysqldump will work even faster.
- While importing, the dumper parses the file faster as it is customized for "right" dumps, while mysqlcli is for handling any requests.
- Testing was done on Windows, and it is possible for the results to vary on other systems.
In the future, it is to be tested how the programs work with the new objects (views, triggers, and others). Because it turned out that even the official MySQL Administrator is unable to properly backup dependent representations (i.e., if view1 utilizes view2, but in the dump they are stored alphabetically, you will get an error when importing). Sypex Dumper 2 takes into account the dependence of representations, and in the example shown above, first the dump will have view2, and then view1.
