[jbpm-users] [JBoss jBPM] - Solution: Large numbers of jobs in JOB table causes major sl

[mr_magoo]

Environment: 
Oracle with jbpm3, db message queue and async processing.

Problem: 
During high volume processing (such as our overnight soak test) the job queue can fill up. 
The job executor synchronises the code in the method "acquireJobs" which in turn executes the query: 

  | select job from org.jbpm.job.Job as job
  | where ( (job.lockOwner is null) or (job.lockOwner = :lockOwner) )
  |       and job.retries > 0
  |       and job.dueDate <= :now
  |       and job.processInstance = :processInstance
  |       and job.isExclusive = true
  |       and job.isSuspended != true
  |       order by job.dueDate asc
  | 

Unfortunately duedate is not indexed and job.dueDate <= :now appears to insure that oracle will fail to optimise the query as ":now" constantly changes. (obviously query plan culls jobs first, then sorts)

Because this is all synchronised it slows the executor pool down and thus more jobs pool and the whole thing begins to crawl. Someone here decribed it as "degrade disgracefully" which is now my personal life long goal as well. 

Solution: 
Index duedate: 
create index IDX_JOB_DUEDATE on JBPM_JOB (DUEDATE_);

Otherwise your DB will be constantly resorting that table.


View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4250501#4250501

Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4250501