AnsweredAssumed Answered

Optimizing ACT_RU_JOB Table

Question asked by anandagarwaal on Jul 18, 2015
Latest reply on Jul 30, 2015 by anandagarwaal
Hi Team,

Kudos on making such a great workflow framework.

We're using Activiti version 5.15.1 with timers in our workflow which sends reminders to users who are in the workflow. Problem is that we're creating jobs in ACT_RU_JOB table and even though there are only 45k odd rows in ACT_RU_JOB table, we're constantly getting slow query alert from the queries running on ACT_RU_JOB table.

A sample slow query alert from database looks like this:


### 253 Queries
### Taking 1.001277 to 1.567178 seconds to complete

SET timestamp=XXX;
select RES.*
    from ACT_RU_JOB RES
    LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
    where (RES.TYPE_ = 'XXX')
      and (RES.DUEDATE_ is not null)
      and (RES.DUEDATE_ < 'XXX')
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < 'XXX')
      and (RES.RETRIES_  > XXX)
      and (
        (RES.EXECUTION_ID_ is null)
        or
        (PI.SUSPENSION_STATE_ = XXX)   
      )
    order by DUEDATE_;


### 225 Queries
### Taking 1.000308 to 1.255745 seconds to complete

SET timestamp=XXX;
select RES.* 
    from ACT_RU_JOB RES
      LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
    where (RETRIES_ > XXX)
      and (DUEDATE_ is null or DUEDATE_ <= 'XXX')
      and (LOCK_OWNER_ is null or LOCK_EXP_TIME_ <= 'XXX')
      and (
          (RES.EXECUTION_ID_ is null)
        or
        (PI.SUSPENSION_STATE_ = XXX)    
      ) 
    LIMIT XXX OFFSET XXX;

The actual query looks something like this:


select RES.*
    from ACT_RU_JOB RES
    LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
    where (RES.TYPE_ = 'timer')
      and (RES.DUEDATE_ is not null)
      and (RES.DUEDATE_ < '2015-07-15 10:42:47.464')
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < '2015-07-15 10:42:47.464')
      and (RES.RETRIES_  > 0)
      and (
        (RES.EXECUTION_ID_ is null)
        or
        (PI.SUSPENSION_STATE_ = 1)   
      )
    order by DUEDATE_;

select RES.*
from ACT_RU_JOB RES
  LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
where (RETRIES_ > 0)
  and (DUEDATE_ is null or DUEDATE_ <= '2015-04-17 14:59:09.982')
  and (LOCK_OWNER_ is null or LOCK_EXP_TIME_ <= '2015-04-17 14:59:09.982')
  and (
      (RES.EXECUTION_ID_ is null)
    or
    (PI.SUSPENSION_STATE_ = 1)
  )
LIMIT 1 OFFSET 0;

select RES.*
    from ACT_RU_JOB RES
    LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
    where (RES.TYPE_ = 'timer')
      and (RES.DUEDATE_ is not null)
      and (RES.DUEDATE_ < '2015-04-20 09:12:13.551')
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < '2015-04-20 09:12:13.551')
      and (RES.RETRIES_  > 0)
      and (
        (RES.EXECUTION_ID_ is null)
        or
        (PI.SUSPENSION_STATE_ = 1)
      )
    order by DUEDATE_;


We've added two composite indices on ACT_RU_JOB (TYPE_, EXECUTION_ID_, LOCK_OWNER_, RETRIES_, LOCK_EXP_TIME_, DUEDATE_) and (RETRIES_,EXECUTION_ID_, LOCK_EXP_TIME_,DUEDATE_) but that doesn't seem to help our cause. Any pointers on how to resolve this would be much appreciated.

Outcomes