Uploaded image for project: 'jBPM'
  1. jBPM
  2. JBPM-9846

Critical Performance issue while connecting with SQL SERVER!!

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Major Major
    • None
    • None
    • Persistence
    • None
    • False
    • False
    • NEW
    • NEW
    • Undefined
    • Hide

      Update datasource to use SQL Server

       

      <datasources>
      <xa-datasource jndi-name="java:jboss/datasources/jBPMDS" pool-name="jBPMXADS">
      <xa-datasource-property name="ServerName">
      localhost
      </xa-datasource-property>
      <xa-datasource-property name="DatabaseName">
      NEW_JBPM
      </xa-datasource-property>
      <xa-datasource-property name="SelectMethod">
      cursor
      </xa-datasource-property>
      <driver>mssql</driver>
      <xa-pool>
      <is-same-rm-override>false</is-same-rm-override>
      </xa-pool>
      <security>
      <user-name>sa</user-name>
      <password>@XXXXX</password>
      </security>
      <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
      <background-validation>true</background-validation>
      </validation>
      </xa-datasource>
      <drivers>
      <driver name="mssql" module="org.mssql">
      <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
      </driver>
      </drivers>
      </datasources> 

      Show
      Update datasource to use SQL Server   <datasources> <xa-datasource jndi-name="java:jboss/datasources/jBPMDS" pool-name="jBPMXADS"> <xa-datasource-property name="ServerName"> localhost </xa-datasource-property> <xa-datasource-property name="DatabaseName"> NEW_JBPM </xa-datasource-property> <xa-datasource-property name="SelectMethod"> cursor </xa-datasource-property> <driver>mssql</driver> <xa-pool> <is-same-rm-override>false</is-same-rm-override> </xa-pool> <security> <user-name>sa</user-name> <password>@XXXXX</password> </security> <validation> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/> <background-validation>true</background-validation> </validation> </xa-datasource> <drivers> <driver name="mssql" module="org.mssql"> <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class> </driver> </drivers> </datasources> 

      We are facing serious performance issue with SQL Server , even with dummy script task. we are not able to achieve throughput more than 110 TPS.
       
      We are far behind official performance baseline.https://www.jbpm.org/learn/jbpmPerformance.html

      jBPM performance baseline - jBPM - Open Source Business Automation Toolkit - jBPM Business Automation Toolkit
      × The aim of this article is to show a base information about performance of the jBPM to set a baseline and to answer basic question how good jBPM performs when it comes to execution. This is not to be seen as competitive information or show jBPM is faster or slower than other engines but more for setting a stage and open the door for more performance tests that can be performed in different ...
      www.jbpm.org

      Please find attached document related to comparative performance analysis with other databases, installed specially to gauge if the performance lag is on JBPM side or specific DB engine.
       
      The suspected culprit is autogenerated schema by hibernate on MS SQL server, we managed to boost performance with some new indexes, created directly on DB (MS SQL), but still metrices are not up to par with official baseline!!
       
      CREATE UNIQUE INDEX IDX_EventTypes_compound ON dbo.EventTypes(InstanceId, element);CREATE INDEX IDX_CorrelationKeyInfo_pInstId ON dbo.CorrelationKeyInfo(processInstanceId);CREATE UNIQUE INDEX IDX_ContextMappingInfo_compound ON dbo.ContextMappingInfo(CONTEXT_ID, OWNER_ID); 
       
      Hoping for your prompt suggestions!

        1. JBPM DB Comparative Analysis.docx
          903 kB
          Ovais Khan

              kverlaen@redhat.com Kris Verlaenen
              ovais.khan Ovais Khan (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: