Optimizing ACT_RU_JOB Table

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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.

vasile_dirla

Hi,
I think that limiting the number of elements involved in join operation will improve the performance of the query.

Please run this query in your environment and post here the new execution time you got.

<code>
select RES.*
from (select * from ACT_RU_JOB t1
where (t1.TYPE_ = 'timer')
      and (t1.DUEDATE_ is not null)
      and (t1.DUEDATE_ < '2015-07-15 10:42:47.464')
      and (t1.LOCK_OWNER_ is null or t1.LOCK_EXP_TIME_ < '2015-07-15 10:42:47.464')
      and (t1.RETRIES_  > 0)) as RES

  LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
where (
        (RES.EXECUTION_ID_ is null)
        or
        (PI.SUSPENSION_STATE_ = 1)
      )
order by DUEDATE_;
</code>

and also will be nice to have a statistic about the execution time for this query:
<code>
select * from ACT_RU_JOB t1
where (t1.TYPE_ = 'timer')
      and (t1.DUEDATE_ is not null)
      and (t1.DUEDATE_ < '2015-07-15 10:42:47.464')
      and (t1.LOCK_OWNER_ is null or t1.LOCK_EXP_TIME_ < '2015-07-15 10:42:47.464')
      and (t1.RETRIES_  > 0)
</code>