SQLyog Sets New Standards for Data Synchronization Speed

SQLyog Sets New Standards for Data Synchronization Speed.
peter_laursen June 21st 2008 Posted to MySQL, SQLyog
In this BLOG post I will concentrate on the achievements with data synchronization with SQLyog 7.0 (beta 1 released just before the weekend).

SQLyog 7 provides 2-8 times speed improvement (depending on data) with a ‘mixed-type’ of syc-job (involving both INSERTS, UPDATES and DELETES) as compared to SQLyog 6. A few comparison examples (SQLyog 6, SQLyog 7 and Maatkit) with 4 testcases:

a)
Source Rows: 3950400, Target Rows: 3950400, Inserted Rows: 49599, Updated Rows: 49500, Deleted Rows:49599. Primary Col(INT). InnoDB
SQLyog 6: 1120 sec
SQLyog 7: 267 sec
Maatkit : 530 sec

b)
Source Rows: 48025 , Target Rows: 48775, Inserted Rows: 1225 , Updated Rows:1225, Deleted Rows : 1975. Primary Col(INT), InnoDB.
SQLyog 6: 30 sec
SQLyog 7 : 8 sec
Maatkit : 19 sec

c)
Source Rows:150404 , Target Rows: 152864, Inserted Rows: 12136, Updated Rows: 16236, Deleted Rows : 14596. Primary Cols(VARCHAR, CHAR). InnoDB.
SQLyog 6: 320 sec
SQlyog 7 : 70 sec
Maatkit : Maatkit did not finish after 20 minutes. We cannot tell if it ever will with this example.

d)
Source Rows: 18209, Target Rows: 10000, Inserted: 9018, Updated Rows: 1001, Deleted Rows 809. Primary cols(SMALLINT, BIGINT). MyISAM.
SQLyog 6: 32 sec
SQLyog 7: 8 sec
Maatkit : 24 sec

(all those examples use a ‘developer machine’ configuration as defined by the configuration wizard bundled with the windows installer for the MySQL server 5.0.51b. Sync is between two databases on the same server. Client and server running on the same machine - a 3Ghz Intel Pentium4 with 1G RAM Running Windows XP SP2 - and with no other significant load at the time of sync. Maatkit version is 1877. Perl environment for running Maatkit was created with ActivePerl for Windows).

We would like to credit Baron ‘Xaprb’ Schwartz (author of Maatkit) for the Maatkit algorithms though (this post in particular was a challenge for us) from which we learned a lot. For the most typical PK-setup (a single column integer PK) the somewhat better performance of SQLyog 7 as compared to Maatkit is probably only due to the fact that SQLyog/SJA is a multithreaded compiled (C++) binary and not a server-side (Perl) script. However with other (more unusual) PK setups the difference is bigger to the advantage of SQLyog.

When syncing to an empty table we use a special high-speed codebrach (a rather simple copy, actually). Other sync tools (also Maatkit) waste lot of time looping while testing things that really need not be tested in this situation! SQLyog will be even faster the larger the max_allowed_packet setting in server configuration as this setting will be detected and BULK INSERTS as large as possible will be generated (optionally). With the above test SQLyog was ~50 times faster than Maatkit when syncing to an empty target (but with the 2 largest cases we interrupted Maatkit before it had finished, though!)

You can download the four testcases 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 (zip archives with SQL dumps for target and source for each case):
case1 (~20 MB): http:webyog.com/downloads/blogresources/datasynctest/datasync_testcase_1.zip
case2 (~3 MB): http:webyog.com/downloads/blogresources/datasynctest/datasync_testcase_2.zip
case3 (~2 MB): http:webyog.com/downloads/blogresources/datasynctest/datasync_testcase_3.zip
case4 (~350 KB): http:webyog.com/downloads/blogresources/datasynctest/datasync_testcase_4.zip
(one detail to observe is that those dumps do not contain a USE statement - be careful about where you import them!)

Download SQLyog and SJA from: http://webyog.com/en/downloads.php
(note that data synchronization is included in SQLyog Enterprise and Enterprise Trial versions only - and in SJA for Linux)