1

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_;

Structure of Table ACT_RU_JOB

    ID_ varchar(64) NO  PRI NULL  
    REV_  int(11) YES   NULL  
    TYPE_ varchar(255)  NO  MUL NULL  
    LOCK_EXP_TIME_  timestamp(3)  YES   NULL  
    LOCK_OWNER_ varchar(255)  YES   NULL  
    EXCLUSIVE_  tinyint(1)  YES   NULL  
    EXECUTION_ID_ varchar(64) YES   NULL  
    PROCESS_INSTANCE_ID_  varchar(64) YES   NULL  
    PROC_DEF_ID_  varchar(64) YES   NULL  
    RETRIES_  int(11) YES MUL NULL  
    EXCEPTION_STACK_ID_ varchar(64) YES MUL NULL  
    EXCEPTION_MSG_  varchar(4000) YES   NULL  
    DUEDATE_  timestamp(3)  YES   NULL  
    REPEAT_ varchar(255)  YES   NULL  
    HANDLER_TYPE_ varchar(255)  YES   NULL  
    HANDLER_CFG_  varchar(4000) YES   NULL  
    TENANT_ID_  varchar(255)  YES   

Structure of ACT_RU_EXECUTION is as follows:

    ID_ varchar(64) NO  PRI NULL  
    REV_  int(11) YES   NULL  
    TYPE_ varchar(255)  NO  MUL NULL  
    LOCK_EXP_TIME_  timestamp(3)  YES   NULL  
    LOCK_OWNER_ varchar(255)  YES   NULL  
    EXCLUSIVE_  tinyint(1)  YES   NULL  
    EXECUTION_ID_ varchar(64) YES   NULL  
    PROCESS_INSTANCE_ID_  varchar(64) YES   NULL  
    PROC_DEF_ID_  varchar(64) YES   NULL  
    RETRIES_  int(11) YES MUL NULL  
    EXCEPTION_STACK_ID_ varchar(64) YES MUL NULL  
    EXCEPTION_MSG_  varchar(4000) YES   NULL  
    DUEDATE_  timestamp(3)  YES   NULL  
    REPEAT_ varchar(255)  YES   NULL  
    HANDLER_TYPE_ varchar(255)  YES   NULL  
    HANDLER_CFG_  varchar(4000) YES   NULL  
    TENANT_ID_  varchar(255)  YES     

Result of EXPLAIN Query

            1   SIMPLE  RES ref COMP_IDX_02 COMP_IDX_02 32  const   22559   Using index condition; Using where; Using filesort
            1   SIMPLE  PI  eq_ref  PRIMARY PRIMARY 194 sp_gruffi.RES.PROCESS_INSTANCE_ID_  1   Using where

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.