Thanks for the overwhelming response to SQLyog 8.1 release. We had highlighted the speed improvements in Data Synchronization in that post. As promised here are the benchmarking results. In this post, we will compare SQLyog 8.1’s data sync speeds against previous version of SQLyog (v 8.05) and Maatkit (v 3519).
We have done similar comparisions in the past. By comparing against Maatkit, we are not trying to imply that SQLyog is better than Maatkit or something similar. Both these tools target a different set of audiences. Maatkit has got some incredible tools that we use internally for a variety of tasks. Maatkit is command line, free and open source. SQLyog’s Data Sync Tools have GUI support, are closed source and are not free on Windows. We are comparing against Maatkit because we didn’t find any other 3rd party tool that does data sync at similar speeds. And just comparing the new version of SQLyog with the old version of SQLyog is not that interesting!
Finally, we would like to thank Baron ‘Xaprb’ Schwartz (author of Maatkit) for the Maatkit algorithms. SQLyog data sync algorithms are based on Maatkit algorithms.
Benchmarking Setup did consist of a source and a destination machine connected by a standard 100 Mbps network link.
Source: AMD Athlon 2.1 GHz Dual Core, 2GB RAM running Win XP Pro SP2
Destination: Intel Core 2 Duo 2.53 GHz, 2GB RAM running Win XP Pro SP2
To arrive at the benchmarking figures we took the average of 3 readings for each case. Below are the benchmarking results with the time taken to complete data sync for each of the 4 cases:
Source Rows: 3950400, Target Rows: 3950400
Inserted Rows: 49599, Updated Rows: 49500, Deleted Rows: 49599
Primary Key (INT)
Source Rows: 48025, Target Rows: 48775
Inserted Rows: 1225, Updated Rows: 1225, Deleted Rows: 1975
Primary Key (INT)
Source Rows:150404 , Target Rows: 152864
Inserted Rows: 12136, Updated Rows: 16236, Deleted Rows: 14596
Primary Key (VARCHAR, CHAR)
Note: In this case Maatkit could not complete data sync. We aborted the test after 20 minutes of wait.
Source Rows: 18209, Target Rows: 10000
Inserted: 9018, Updated Rows: 1001, Deleted Rows: 809
Primary Key (SMALLINT, BIGINT)
The performance improvements in Data Sync is mainly due to the optimization that we have done to reduce the number of I/O operations required over the network. As a result, speed improvements will be more visible when the source and destination servers are on different machines.
You can download the four test cases if you want to verify/test on your environment. And if you think it is not fair to compare with Maatkit on Windows, you can do the same on Linux with the SJA (SQLyog Job Agent) for Linux as well.
Download links for zip archives with SQL dumps for target and source:
Note that these dumps do not contain a USE statement. So be careful about where you import them!
Download SQLyog and SJA here. Note that data synchronization is included in SQLyog Enterprise and Enterprise Trial versions only – and in SJA for Linux.
Shameless Plug: We are using Visifire – Webyog’s open source charting component to draw these charts. If you want to put some visually stunning animated charts in your applications, check out Visifire!
Please feel free to share your thoughts on the comments below.