Performance Improvements for big amount of workflow instances

[m.radli...@gmail.com]

Hi,

We encountered some performance issues when running > 15.000 process instances (Oracle 11g, MySQL 5.1).

Settings:
History Level: activity
Camunda BPM version: 7.0.0-alpha1
Processing: max. 25 process instance in parallel

After doing some DB tracing on Oracle11g we found out, that the issues are related to exactly one SQL statement:

select * from ( select a.*, ROWNUM rnum from (
select RES.*
from ACT_HI_ACTINST RES
WHERE RES.EXECUTION_ID_ = :?
and RES.ACT_ID_ = :?
and RES.END_TIME_ is null
order by RES.ID_ asc
) a where ROWNUM = :? ) where rnum >= :?

We created an additional index (quite statement-specific) and with the new index the performance is back to a normal/very good value (statement execution time decreased from >500ms to <15ms, number of rows in ACT_HI_ACTINST: 680K, statement costs remain the same)

Index:
CREATE INDEX ACT_IDX_HI_ACT_INST_IMPR ON ACT_HI_ACTINST (EXECUTION_ID_, ACT_ID_, END_TIME_, ID_);

Is there a way that the additional index is added to the DB-setup scripts, or can we somehow intercept the DB schema updates on engine startup?

Thanks & Cheers
Michael

[Daniel Meyer]

Hi Michael,

Intersting findings, thanks for the pointer! We sould definately consider adding the index you propose to the create scripts. 

The general strategy is this: we try providing a good default index configuration out of the box. If users need application specific indexes (ie. for making their own queries faster) they should add them themselves (otherwise we end up with adding indexes on everything). But if we need an index for process engine runtime performace we sould add it to the default configuration.

So the question would be wether the statement you mention above is issued by the engine itself (when executing processes) or whether it is a HistoryService.createhistoricXXQuery() you perform yourselves?

Regards,

Daniel

[m.radli...@gmail.com]

Hi Daniel,

Thanks for the fast reply!
Would be really helpful if you could add it to the default config.

I would assume that the statement is executed by the engine itself, as I do not invoke something like 'HistoryService.createhistoricXXQuery()'.

Thanks,
Michael

[gimb...@gmail.com]

Hi Michael,

I have added an issue to our backlog for 7.0.0 Final Version:

https://app.camunda.com/jira/browse/CAM-683

You can register for our Jira if you want to add comments.

Thank you for your feedback!
Robert

[robert...@camunda.com]

Sorry, got the Issue Number wrong: https://app.camunda.com/jira/browse/CAM-684

[Bernd Rücker (camunda)]

Hi Guys.

Nice to see you on this forum :-) I quickly checked the SQL and it must be
issued by some history query you make. So it should be something like

historyService. createHistoricActivityInstanceQuery()....

And then it looks like you use executionId, activity id and end time.
I quickly did a search within the project and we do not use this query
ourselves. Could you maybe double check?

Because if this is a specific query from your use case it would be more a
task for us to improve documentation to tell you that creating own indices
based on your queries is pretty normal and encouraged. But it is hard for
us to merge it into the product (as other people might have completely
different queries).

What do you guys think?

Cheers
Bernd

-----Urspr�ngliche Nachricht-----
Von: camunda-...@googlegroups.com
[mailto:camunda-...@googlegroups.com] Im Auftrag von
robert...@camunda.com
Gesendet: Montag, 29. April 2013 16:13
An: camunda-...@googlegroups.com
Cc: Thomas.Andr...@sonydadc.com; peter.h...@sonydadc.com;
m.radli...@gmail.com
Betreff: Re: Performance Improvements for big amount of workflow instances

Sorry, got the Issue Number wrong:
https://app.camunda.com/jira/browse/CAM-684

--
You received this message because you are subscribed to the Google Groups
"camunda BPM users & process application developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to camunda-bpm-us...@googlegroups.com.
To post to this group, send email to camunda-...@googlegroups.com.

[Robert G]

Hi,

can you please provide feedback to Bernds last remark.

If this is more of a custom query, we could skip this issue in our backlog and focus on other items instead.

There is always lot's of improvements to be done ;)

Thank You

Robert

[Thomas Andreas Winkler]

Hi,

we checked our code and we are not using a query like mentioned from Bernd. What we are doing in our tests is running a high number of receive tasks and "waking" them up like this:

this.getRuntimeService().signal(this.executionId);

Could it be the case that the query is used when the engine is getting the persisted workflow out of the DB again?

select * from ( select a.*, ROWNUM rnum from (
   select RES.*
   from ACT_HI_ACTINST RES
    WHERE  RES.EXECUTION_ID_ = :?
       and RES.ACT_ID_ = :?
       and RES.END_TIME_ is null
    order by RES.ID_ asc
     ) a where ROWNUM = :? ) where rnum  >= :?

Cheers,

Thomas

[Bernd Rücker (camunda)]

Hi Thomas.

 

If you do the signal in the background the following SQL is executed – which is pretty different to what you write:

  <select id="selectExecution" parameterType="string" resultMap="executionResultMap">

    select * from ${prefix}ACT_RU_EXECUTION where ID_ = #{id}

  </select>

 

So that cannot cause this query.

 

How do you know which executionId to trigger?

 

Do you have a limited test case where the query is issued? Then we could have a deeper look why we get this SQL?

What puzzles me is that when I did a quick search through the code I only found the SQL snippet you mention when issuing queries via the Query API. Strange!

 

Cheers

Bernd

[Thomas Andreas Winkler]

Hi Bernd,

I will take a look again today, unfort. our developer who was running the tests is on a external training this week so I need to check if I can find out the details myself. I will let you know...

Cheers,

Thomas

[Christian Lipphardt]

I did some quick digging into the engine. The query described is issued in the ActivityInstanceEndHandler in method findActivityInstance(ExecutionEntity execution) => core part of the engine. Imo we should add it as default.

[Bernd Rücker (camunda)]

Thanks Christian for finding this usage - somehow slipped through my quick search. Then this is indeed an internal issue and we will add that index.

 

Thanks Thomas for reporting this!

 

Cheers

Bernd

Evangelist & Consultant

www.camunda.org/community/team.html

 

 

Von: camunda-...@googlegroups.com [mailto:camunda-...@googlegroups.com] Im Auftrag von Christian Lipphardt

Gesendet: Dienstag, 7. Mai 2013 13:36
An: camunda-...@googlegroups.com
Cc: Thomas.Andr...@sonydadc.com; peter.h...@sonydadc.com; m.radli...@gmail.com

Betreff: Re: Performance Improvements for big amount of workflow instances

 

I did some quick digging into the engine. The query described is issued in the ActivityInstanceEndHandler in method findActivityInstance(ExecutionEntity execution) => core part of the engine. Imo we should add it as default.

--

[Daniel Meyer]

Fixed in Master:

https://github.com/camunda/camunda-bpm-platform/commit/ddd75e4eb1fecd4d2b1d310f506cea16308ab6db