Uploaded image for project: 'JBoss BPMS Platform'
  1. JBoss BPMS Platform
  2. RHBPMS-4445

[GSS](6.4.z) Severe performance issue with "/query/runtime/process" endpoint

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 6.4.1
    • 6.3.3.GA
    • Business Central

    • databases mysql and postgres were used for test - but this should be reproducible with any db

    • CR3
    • Workaround Exists
    • Hide

      By default the endpoint tries to get the last value of the process variables. It results in a subquery inside the main query and it is basically the reasons to make it hang:

      select distinct variablein0_.id as id1_37_, variablein0_.log_date as log_date2_37_, variablein0_.externalId as external3_37_, variablein0_.oldValue as oldValue4_37_, variablein0_.processId as processI5_37_, variablein0_.processInstanceId as processI6_37_, variablein0_.value as value7_37_, variablein0_.variableId as variable8_37_, variablein0_.variableInstanceId as variable9_37_ from VariableInstanceLog variablein0_ where (variablein0_.id in (select max(variablein1_.id) from VariableInstanceLog variablein1_ group by variablein1_.variableId , variablein1_.processInstanceId)) and variablein0_.processInstanceId=1 order by variablein0_.processInstanceId asc

      The workaround is to get all the process variables values for the given endpoint using the query parameter "all":

      select distinct variablein0_.id as id1_37_, variablein0_.log_date as log_date2_37_, variablein0_.externalId as external3_37_, variablein0_.oldValue as oldValue4_37_, variablein0_.processId as processI5_37_, variablein0_.processInstanceId as processI6_37_, variablein0_.value as value7_37_, variablein0_.variableId as variable8_37_, variablein0_.variableInstanceId as variable9_37_ from VariableInstanceLog variablein0_ where variablein0_.processInstanceId=1 order by variablein0_.processInstanceId asc

      It results on a great performance improvement:

      [wsiqueir@wsiqueir kie-remote]$ time curl -u 'bpmsAdmin:redhat2014!' -X GET 'http://localhost:8080/business-central/rest/query/runtime/process?s=1'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><query-process-instance-result><processInstanceInfoList><process-instance><process-id>get_redhat_proj.main_proc</process-id><id>1</id><state>2</state><parentProcessInstanceId>-1</parentProcessInstanceId></process-instance><variables><name>processId</name><string>project1.main</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-10-31T18:59:01-02:00</lastModificationDate></variables></processInstanceInfoList></query-process-instance-result>
real	0m16.025s
user	0m0.002s
sys	0m0.004s
[wsiqueir@wsiqueir kie-remote]$ time curl -u 'bpmsAdmin:redhat2014!' -X GET 'http://localhost:8080/business-central/rest/query/runtime/process?s=1&all'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><query-process-instance-result><processInstanceInfoList><process-instance><process-id>get_redhat_proj.main_proc</process-id><id>1</id><state>2</state><parentProcessInstanceId>-1</parentProcessInstanceId></process-instance><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-06-13T21:05:17-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-08-24T16:41:24-03:00</lastModificationDate></variables><variables><name>processId</name><string>project1.main</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-10-31T18:59:01-02:00</lastModificationDate></variables></processInstanceInfoList></query-process-instance-result>
real	0m0.015s
user	0m0.002s
sys	0m0.004s

      An easy solution for this issue is only filter the variable on the application side, once you have the process list, go one by one and filter the list of variable values to get the last one instead doing it in the database query.

      Show
      By default the endpoint tries to get the last value of the process variables. It results in a subquery inside the main query and it is basically the reasons to make it hang: select distinct variablein0_.id as id1_37_, variablein0_.log_date as log_date2_37_, variablein0_.externalId as external3_37_, variablein0_.oldValue as oldValue4_37_, variablein0_.processId as processI5_37_, variablein0_.processInstanceId as processI6_37_, variablein0_.value as value7_37_, variablein0_.variableId as variable8_37_, variablein0_.variableInstanceId as variable9_37_ from VariableInstanceLog variablein0_ where (variablein0_.id in (select max(variablein1_.id) from VariableInstanceLog variablein1_ group by variablein1_.variableId , variablein1_.processInstanceId)) and variablein0_.processInstanceId=1 order by variablein0_.processInstanceId asc The workaround is to get all the process variables values for the given endpoint using the query parameter "all": select distinct variablein0_.id as id1_37_, variablein0_.log_date as log_date2_37_, variablein0_.externalId as external3_37_, variablein0_.oldValue as oldValue4_37_, variablein0_.processId as processI5_37_, variablein0_.processInstanceId as processI6_37_, variablein0_.value as value7_37_, variablein0_.variableId as variable8_37_, variablein0_.variableInstanceId as variable9_37_ from VariableInstanceLog variablein0_ where variablein0_.processInstanceId=1 order by variablein0_.processInstanceId asc It results on a great performance improvement: [wsiqueir@wsiqueir kie-remote]$ time curl -u 'bpmsAdmin:redhat2014!' -X GET 'http: //localhost:8080/business-central/ rest /query/runtime/process?s=1' <?xml version= "1.0" encoding= "UTF-8" standalone= "yes" ?><query-process-instance-result><processInstanceInfoList><process-instance><process-id>get_redhat_proj.main_proc</process-id><id>1</id><state>2</state><parentProcessInstanceId>-1</parentProcessInstanceId></process-instance><variables><name>processId</name><string>project1.main</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-10-31T18:59:01-02:00</lastModificationDate></variables></processInstanceInfoList></query-process-instance-result> real 0m16.025s user 0m0.002s sys 0m0.004s [wsiqueir@wsiqueir kie-remote]$ time curl -u 'bpmsAdmin:redhat2014!' -X GET 'http: //localhost:8080/business-central/ rest /query/runtime/process?s=1&all' <?xml version= "1.0" encoding= "UTF-8" standalone= "yes" ?><query-process-instance-result><processInstanceInfoList><process-instance><process-id>get_redhat_proj.main_proc</process-id><id>1</id><state>2</state><parentProcessInstanceId>-1</parentProcessInstanceId></process-instance><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-06-13T21:05:17-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-08-24T16:41:24-03:00</lastModificationDate></variables><variables><name>processId</name><string>project1.main</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-10-31T18:59:01-02:00</lastModificationDate></variables></processInstanceInfoList></query-process-instance-result> real 0m0.015s user 0m0.002s sys 0m0.004s An easy solution for this issue is only filter the variable on the application side, once you have the process list, go one by one and filter the list of variable values to get the last one instead doing it in the database query.
      • Generate more than 100k records on ProcessInstanceLog and on VariableInstanceLog tables;
      • Make a request to /query/runtime/process

      When we have about 100k+ records on ProcessInstanceLog and VariableInstanceLog tables the calls to /query/runtime/process starts to hang, leading to transaction timeout on server side and socket timeout on client side.

      Indexes where applied to these tables, however, it didn't result in performance improvements

              rhn-support-tsurdilo Tihomir Surdilovic (Inactive)
              rhn-support-wsiqueir William Siqueira
              Jiri Petrlik Jiri Petrlik
              Jiri Petrlik Jiri Petrlik
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: