Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Sja Sync & Application

Tagged: ,

This topic contains 7 replies, has 0 voices, and was last updated by  gonzaloleon 6 years, 2 months ago.

  • Author
    Posts
  • #12781

    gonzaloleon
    Member

    Hi all!

    This is my first post, i'm from Argentina.

    I'm testing sja in windows and linux, and i'm having trouble with the concurrency of sja (executing) and application. When sja.exe is running (scheduled task), the application can't use mysql tables, timeout errors (in application).

    How can i use both synchronization and my application without problems? I have to sync some tables, every 5 minutes.

    Thanks!

    Gonzalo León.

  • #33744

    peterlaursen
    Participant

    What table type/engine do you use? MyISAM or InnoDB? If it is MyISAM I am afraid we cannot do anything about it. Unlike InnoDB, MyISAM does not have row-level locking – it looks the whole table.

    But how long does it take for the sync job to run? First time it could take some time, but if you shcedule it every 5 minutes it will probably take muich shorter the next times.

  • #33745

    gonzaloleon
    Member
    'peterlaursen' wrote:

    What table type/engine do you use? MyISAM or InnoDB? If it is MyISAM I am afraid we cannot do anything about it. Unlike InnoDB, MyISAM does not have row-level locking – it looks the whole table.

    But how long does it take for the sync job to run? First time it could take some time, but if you shcedule it every 5 minutes it will probably take muich shorter the next times.

    Hi Peter, thanks for the reply!

    I'm working with InnoDB. How can i change the lock mode? I want to execute both sync & application at the same time.

    First run, it's take long time, but then, it's take about 3 / 4 minutes every 5 minutes (there are some problems in the internet line), and we can't stop sales biggrin.png

    Thanks!

    Edit1: I can't make any INSERT/UPDATE when sja is running, but SELECT yes. I'm using TRANSACTION in my application.

  • #33746

    peterlaursen
    Participant

    SJA/Data Sync does not issue any explicit LOCKS. If they occur it is internal for the server or storage engine. There may be some settings. We will discuss this.

  • #33747

    gonzaloleon
    Member
    'peterlaursen' wrote:

    SJA/Data Sync does not issue any explicit LOCKS. If they occur it is internal for the server or storage engine. There may be some settings. We will discuss this.

    But SJA locks tables when two way sync?

  • #33748

    peterlaursen
    Participant

    NO .. a two way sync is simple implemented as a one way sync from to followed by a sync in opposite direction. If a LOCK occurs it is because of server or storage engine internals. We do not *ask for it*.

    Are you sure that what you experience is a LOCK and not just a slownesss/performance issue with the server?

    Do the problems you experience occur on or ? And during 1st or 2nd step of a 2-way sync?

    You could try to install MONyog 5.5 beta1 TRIAL (refer http://www.webyog.co…-been-released/). Maybe the new 'Real-Time' feature will expose what really happens.

  • #33749

    gonzaloleon
    Member
    'peterlaursen' wrote:

    NO .. a two way sync is simple implemented as a one way sync from to followed by a sync in opposite direction. If a LOCK occurs it is because of server or storage engine internals. We do not *ask for it*.

    Are you sure that what you experience is a LOCK and not just a slownesss/performance issue with the server?

    Do the problems you experience occur on or ? And during 1st or 2nd step of a 2-way sync?

    You could try to install MONyog 5.5 beta1 TRIAL (refer http://www.webyog.co…-been-released/). Maybe the new 'Real-Time' feature will expose what really happens.

    Now i'm having Deadlock error sad.png

    Edit1: The problem is occuring in the source, the localhost, i specified REMOTE HOST and LOCALHOST

  • #33750

    peterlaursen
    Participant

    I was not correct. In order to get consistent snapshots for checksums calculation we execute LOCK IN SHARE MODE on source and SELECT … FOR UPDATE on target.

    This results in LOCKS. However this was never (or rarely at least) reported as a problem before. I could imagine that if a lot of changes need to be done on target the 'LOCK-period' would be larger than if if only a few changes need to be done. To ensure that only a few changes are required simply schedule the job frequently (say every 2 minutes). Did you try this.

    Are you sure that your applicaiton(s) also don't try to achive a LOCK IN SHARED MODE etc?

    There is one more detail. SQLyog will (on target) use as big as possible BULK INSERTS. 'big as possible' means up to the size of 'max_allowed_packet' setting up to 16 MB. This could also have effect. What is your setting?

    We will check if we have any option to make the LOCKS more 'short-lived' and if 'max_allowed_packet' setting matters. We have no option to specify it currently, but it would not be hard to add (if there is a reason).

  • #33751

    gonzaloleon
    Member
    'peterlaursen' wrote:

    I was not correct. In order to get consistent snapshots for checksums calculation we execute LOCK IN SHARE MODE on source and SELECT … FOR UPDATE on target.

    This results in LOCKS. However this was never (or rarely at least) reported as a problem before. I could imagine that if a lot of changes need to be done on target the 'LOCK-period' would be larger than if if only a few changes need to be done. To ensure that only a few changes are required simply schedule the job frequently (say every 2 minutes). Did you try this.

    Are you sure that your applicaiton(s) also don't try to achive a LOCK IN SHARED MODE etc?

    There is one more detail. SQLyog will (on target) use as big as possible BULK INSERTS. 'big as possible' means up to the size of 'max_allowed_packet' setting up to 16 MB. This could also have effect. What is your setting?

    We will check if we have any option to make the LOCKS more 'short-lived' and if 'max_allowed_packet' setting matters. We have no option to specify it currently, but it would not be hard to add (if there is a reason).

    Hi! News?

    We are testing the sync tool and we continue having the LOCK problem. We reduce the time to run the sync tool to 5 or 2 minutes, and the problem is using remote mysql database, over internet.

You must be logged in to reply to this topic.