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.
