Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-8327

Switch on MyBatis batch processing mode

    Details

    • Type: Task
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 7.8.0, 7.8.0-alpha6
    • Component/s: engine
    • Labels:
      None

      Description

      MyBatis has the special mode of pprocessing SQL statements in batches.

      The task is:

      • to switch it on
      • to measure performance improvement

        Issue Links

          Activity

          svetlana.dorokhova Svetlana Dorokhova created issue -
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Fix Version/s 7.8.0 [ 14894 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Summary Switch on MyBatis "reuse prepared statements" mode Switch on MyBatis batch processing mode
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Description MyBatis has the special mode of reusing prepared statements when executing queries.

          The task is:
          * to switch it on
          * to measure performance improvement
          MyBatis has the special mode of pprocessing SQL statements in batches.

          The task is:
          * to switch it on
          * to measure performance improvement
          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited

          Performance test consisted of the following:

          1. FULL history level
          2. Process looks like this:

          BpmnModelInstance process = Bpmn.createExecutableProcess("process")
                .startEvent()
                .serviceTask()
                .camundaClass(NoopDelegate.class.getName())
                .camundaAsyncAfter()
                .serviceTask()
                .camundaClass(UpdateVarDelegate.class.getName())
                .camundaAsyncAfter()
                .serviceTask()
                .camundaClass(DeleteVarDelegate.class.getName())
                .endEvent()
                .done();
          

          3. Process is started with 500 process variables. 1st Service task does nothing, but the transaction is committed after it is completed (asyncAfter). 2nd service task update all 500 process variables with new values and the transaction is committed afterwards. 3rd service task removes all the variables and process finishes.

          The testing was done with the help of QA Performance test suite. It was running the test in 2 threads with 50 repeations. The result are the following:

          - Oracle 12 Mysql 5.7.17 SQL Server 2014
          SIMPLE mode 196696 135528 264003
          BATCH mode 58012 128714 94147

          It looks like MySQL does not distinguish batch and not batch operations, but SQL Server and Oracle do.

          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited Performance test consisted of the following: 1. FULL history level 2. Process looks like this: BpmnModelInstance process = Bpmn.createExecutableProcess( "process" ) .startEvent() .serviceTask() .camundaClass(NoopDelegate.class.getName()) .camundaAsyncAfter() .serviceTask() .camundaClass(UpdateVarDelegate.class.getName()) .camundaAsyncAfter() .serviceTask() .camundaClass(DeleteVarDelegate.class.getName()) .endEvent() .done(); 3. Process is started with 500 process variables. 1st Service task does nothing, but the transaction is committed after it is completed (asyncAfter). 2nd service task update all 500 process variables with new values and the transaction is committed afterwards. 3rd service task removes all the variables and process finishes. The testing was done with the help of QA Performance test suite. It was running the test in 2 threads with 50 repeations. The result are the following: - Oracle 12 Mysql 5.7.17 SQL Server 2014 SIMPLE mode 196696 135528 264003 BATCH mode 58012 128714 94147 It looks like MySQL does not distinguish batch and not batch operations, but SQL Server and Oracle do.
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Original Estimate 0 minutes [ 0 ]
          Remaining Estimate 0 minutes [ 0 ]
          Assignee Svetlana Dorokhova [ svetlana.dorokhova ] Smirnov Roman [ roman.smirnov ]
          Resolution Fixed [ 1 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Assignee Smirnov Roman [ roman.smirnov ] Svetlana Dorokhova [ svetlana.dorokhova ]
          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited

          Won't work for Oracle < 12.

          http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28754

          For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

          https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28773

          For a prepared statement batch, the array contains the actual update counts indicating the number of rows affected by each operation.

          This means that we won't be able to use optimistic locking with Batch processing switched on on Oracle earlier than v. 12.

          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited Won't work for Oracle < 12. http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28754 For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown. https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28773 For a prepared statement batch, the array contains the actual update counts indicating the number of rows affected by each operation. This means that we won't be able to use optimistic locking with Batch processing switched on on Oracle earlier than v. 12.
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Link This issue is related to CAM-8351 [ CAM-8351 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Status Reopened [ 4 ] In Progress [ 3 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Assignee Svetlana Dorokhova [ svetlana.dorokhova ] Smirnov Roman [ roman.smirnov ]
          Resolution Fixed [ 1 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Assignee Smirnov Roman [ roman.smirnov ] Yana Vasileva [ yana.vasileva ]
          yana.vasileva Yana Vasileva made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          yana.vasileva Yana Vasileva made changes -
          Fix Version/s 7.8.0-alpha6 [ 15101 ]
          thorben.lindhauer Thorben Lindhauer made changes -
          Workflow camunda BPM [ 44270 ] Backup_camunda BPM [ 63252 ]

            People

            • Assignee:
              yana.vasileva Yana Vasileva
              Reporter:
              svetlana.dorokhova Svetlana Dorokhova
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development