Activiti is slowing down -- Complete task is taking time

[anilknayak]

Hi,

we have been using activiti  with history enabled and we are using Oracle DB for the activiti, but recently got some performance issues.
following are the observations, what I got from the performance stand point while performance test of our application

we have seen that the records are growing in activiti history table, which is causing performance issues.
following are the statistics of activiti tables when we got the performance issues. Each execution is taking 4-6 minutes and sometime it crosses 30 min.

Records in Activiti Tables are as follows
ACT_HI_IDENTITYLINK   4607105
ACT_HI_VARINST          33178192
ACT_HI_TASKINST         10192002
ACT_HI_ACTINST        55526361
ACT_HI_PROCINST         2264137
ACT_RU_VARIABLE         3956114
ACT_RU_IDENTITYLINK   564997
ACT_RU_TASK                  282432
ACT_RU_EXECUTION           564947

with the above number of record in table, our process execution is very slow.
but with the below statistics our application running exceptionally faster like each processes is executing in some millisecond .
after we have deleted records from the history table and below is the row count after deletion

ACT_HI_IDENTITYLINK   199044
ACT_HI_PROCINST   87648
ACT_HI_ACTINST   2134173
ACT_HI_TASKINST   445314
ACT_HI_VARINST   1141642

could you please give us some suggestion for this slowness. we are having a very critical application. which need to be fixed.
Going forward our data can grow 20-30 time as we have today.

[daisuke-yoshimo]

You should identify the slow SQL.

If you use Oracle DB, you can identify the slow SQL from Oracle Statspack report or AWR report.

[anilknayak]

Yes we have found out the queries and those queries are having the tables mentioned above.

[daisuke-yoshimo]

Can you tell not only table name but the slow query and it's execution plan ?

[daisuke-yoshimo]

Please tell me not only table name but the slow query and it's execution plan.

[martin_grofcik]

Hi Anil,

I had similar issue with another BPM platform. And (after the long time of strugling with DB +++….) the conclusion was to periodically archive history. (In that case clean up the history tables and move content to the archive - does not matter how it is implemented).

Short-distance runner cannot carry whole house as a snail.

Regards
Martin