Coupon code: mysql14
Hurry up, offer expires soon!
Blog

SQLyog Sets New Standards for Data Synchronization Speed.

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)

4 thoughts on “SQLyog Sets New Standards for Data Synchronization Speed.

  1. Pingback: Database Management » Blog Archive » SQLyog Sets New Standards for Data Synchronization Speed.

  2. Parent table:

    CREATE TABLE CAREERSEEKER(
    IDNUMBER int NOT NULL,
    FIRSTNAME varchar(25) NULL,
    LASTNAME varchar(25) NULL,
    BIRTHDATE varchar(25) NULL,
    CITY varchar(25) NULL,
    EMAIL varchar(50) NULL,
    HOMEPHONE varchar(25) NULL,
    WORKPHONE varchar(25) NULL,
    MOBILEPHONE varchar(50) NULL,
    FAXNUMBER varchar(50) NULL,
    PRIMARY KEY(IDNUMBER)
    )ENGINE=INNODB;

    Child Table:
    CREATE TABLE CVDATALANGUAGE(
    LANGUAGEID int NOT NULL DEFAULT 0,
    LANGUAGE1 varchar(50) NULL,
    LANGUAGE1PROFICENCY varchar(50) NULL,
    LANGUAGE2 varchar(50) NULL,
    LANGUAGE2PROFICENCY varchar(50) NULL,
    LANGUAGE3 varchar(50) NULL,
    LANGUAGE3PROFICENCY varchar(50) NULL,
    LANGUAGE4 varchar(50) NULL,
    LANGUAGE4PROFICENCY varchar(50) NULL,
    IDNUMBER int Not NULL,
    PRIMARY KEY(LANGUAGEID)
    FOREIGN KEY(IDNUMBER) REFERENCES CAREERSEEKER(IDNUMBER) ON DELETE CASCADE
    ) ENGINE=INNODB;

    i am getting error when creating the parent child relation ship between the above tables. I am using MySQL 5.1 version. Can anybody help me why i am getting errors..>?

  3. For Child table there is a syntax error , the corrected syntax for Child table is given below,

    CHILD TABLE DEFINITION :

    CREATE TABLE CVDATALANGUAGE(
    LANGUAGEID int NOT NULL DEFAULT 0,
    LANGUAGE1 varchar(50) NULL,
    LANGUAGE1PROFICENCY varchar(50) NULL,
    LANGUAGE2 varchar(50) NULL,
    LANGUAGE2PROFICENCY varchar(50) NULL,
    LANGUAGE3 varchar(50) NULL,
    LANGUAGE3PROFICENCY varchar(50) NULL,
    LANGUAGE4 varchar(50) NULL,
    LANGUAGE4PROFICENCY varchar(50) NULL,
    IDNUMBER int Not NULL,
    PRIMARY KEY(LANGUAGEID),
    CONSTRAINT `FK_IDNUMBER` FOREIGN KEY (`IDNUMBER`) REFERENCES `careerseeker` (`IDNUMBER`) ON DELETE CASCADE
    ) ENGINE=INNODB;

    Please refer MySQL Documentation for such queries. In future please use Webyog forums for quick assistance.

  4. Pingback: Webyog » SQLyog 8.1 - Fast and Furious

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>