topic Re: Optimizing ACT_RU_JOB Table in Alfresco Archive

Optimizing ACT_RU_JOB Table

anandagarwaal — Sat, 18 Jul 2015 05:15:56 GMT

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: Optimizing ACT_RU_JOB Table

vasile_dirla — Sun, 19 Jul 2015 06:35:10 GMT

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>

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Sun, 19 Jul 2015 07:07:03 GMT

Hi Vasile,
Thanks for the response. Please find below response times, but is it possible to change these queries in Activiti (5.15.1) ? I thought that only way to optimize these queries is to add indices.
The below query took 1.92s
<code>
select sql_no_cache 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 this query took 1.58s

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

Re: Optimizing ACT_RU_JOB Table

vasile_dirla — Sun, 19 Jul 2015 07:42:45 GMT

since it's open source you could modify it as you wish.
have a look into Job.xml (selectNextTimerJobsToExecute)
you could change this query if you think you could construct a more performant one (but you know.. it needs some testing to be sure will give you the correct results)

if you run the original query how log time will take (is there any improvement if the where clause is done before JOIN?)
it seems that the most time is spent during the job table query.
how many records you have in the job table ?

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Sun, 19 Jul 2015 07:43:14 GMT

The original query execution time ranges from 200ms to 1.95s during multiple runs. Currently we've 49,539 rows in ACT_RU_JOB.

Is it something to do with locks on table etc?

Re: Optimizing ACT_RU_JOB Table

vasile_dirla — Sun, 19 Jul 2015 08:08:16 GMT

What happens if you remove the second index you added on Job table and just disable caching ?
<code>query_cache_size = 0</code>
If this table is updated too often then the cache is invalidated for each insert. (you could try to disable it and check the results)
have a look also here:
http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-queries.html

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Sun, 19 Jul 2015 08:13:11 GMT

Everyday we've around 2000-3000 inserts in this table. We've to ask DBA to disable cache on the table and we will able to do it on Monday. Thanks for the help Vasile.

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Mon, 20 Jul 2015 04:32:19 GMT

We tried disabling the cache and dropping the second index (RETRIES_,EXECUTION_ID_, LOCK_EXP_TIME_,DUEDATE_) but it doesn't seem to have any effect.

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Thu, 23 Jul 2015 18:22:09 GMT

Any more ideas/tips on how to resolve this?

Re: Optimizing ACT_RU_JOB Table

jbarrez — Tue, 28 Jul 2015 11:48:37 GMT

Not really. It seems it's doing a table scan … which probably could be solved by more indices .. but this is trial and error and database specific.

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Wed, 29 Jul 2015 03:16:23 GMT

Has any optimizations been made to ACT_RU_JOB table in newer version of activiti? We're using 5.15.1 version.

Re: Optimizing ACT_RU_JOB Table

trademak — Wed, 29 Jul 2015 17:42:33 GMT

There's a big change in Activiti 5.17 with the new async executor we introduced. This job executor uses less database queries for job execution, so could be interesting for your use case. We provide a schema with default indexes and it's always good to see if for your use case some more indexes would improve performance.

Best regards,

Re: Optimizing ACT_RU_JOB Table

anandagarwaal — Thu, 30 Jul 2015 03:42:51 GMT

This new async job executor works for timers as well?