Hide Forgot
Date of First Response: 2009-11-05 02:39:48 project_key: SOA While profiling the users system found that the methods org.jbpm.job.executor.JobExecutorThread.getWaitPeriod(int) and org.jbpm.job.executor.JobExecutorThread.getNextDueDate() are taking 2.58 seconds to run. This is true even though the client is not using JBPM and has nothing added to the database. It appears to be do to a lack of an index on the query. Since they don't use JBPM in this implementation of ESB we are just taking it out.
"JBoss 4.3 running ESB 4.6" is not a supported platform. A way to reproduce this in the SOA platform would be more than useful.
It looks like this was an issue for jBPM 3.2.6: the code that is referenced by this issue has been rewritten more than once since then. Because of the fact that code is now different, I'm resolving this issue as "Won't fix". Should the issue come up again, please do make a new issue (and of course, do include as much information as possible about the setup/server/environment in which the issue occurred.)
Ehh.. closed too soon: realized that affected code _does_ still exist and that there _is_ a practical solution for this. Currently testing the solution!
Link: Added: This issue relates to JBPM-3235
Query plan for - JobSession.getFirstDueJobExcludingMonitoredJobs query -- with 2500 monitored Job ids - on 40000 row ^1^ Job table - in a on Postgresql 8.4 database: ^1^: see JBPM3235Test for content Before job.duedate index: {code} Limit (cost=173657.08..173657.09 rows=1 width=2177) -> Sort (cost=173657.08..173755.63 rows=39417 width=2177) Sort Key: duedate_ -> Seq Scan on jbpm_job job0_ (cost=0.00..173460.00 rows=39417 width=2177) Filter: ((NOT issuspended_) AND ((lockowner_ IS NULL) OR ((lockowner_)::text = 'JBPM3235'::text)) AND (retries_ > 0) AND (id_ <> ALL ('{50032,...,52531}'::bigint[]))) {code} After job.duedate index: {code} Limit (cost=0.00..4.43 rows=1 width=2177) -> Index Scan using idx_job_duedate on jbpm_job job0_ (cost=0.00..174474.14 rows=39417 width=2177) Filter: ((NOT issuspended_) AND ((lockowner_ IS NULL) OR ((lockowner_)::text = 'JBPM3235'::text)) AND (retries_ > 0) AND (id_ <> ALL ('{50032,...,52531}'::bigint[]))) {code} Of note is that the query plan with index no longer has to do an expensive sort (lines 2-3, first query plan). This query is the most intensive of the three queries that JobSession.getFirstDueJob(String, Collection) can use -- and the other two show similar query plans and improvements on the query plans. The conclusion is that an index on duedate is an effective solution.
Issue done, over to you!
This update requires an upgrade script for existing jBPM users, which would simply contain the following line of SQL: {code} create index IDX_JOB_DUEDATE on JBPM_JOB (DUEDATE_); {code} Release note draft: "This update is of interest only to jBPM installations who currently have problems with Job processing performance. In this update, an index has been added to the Job table so that the next Job to be executed can be retrieved quickly from the database."
Fixed in jBPM 3.2.11.
Link: Added: This issue Cloned to SOA-3214
Link: Added: This issue relates to SOA-3214