We deployed a web application using JBPM 2.0 in a production environment and we're facing a great performance decay as database size increases.
We deployed on this environment:
- Windows 2000 server (2 CPUs, 4GB RAM)
- JBoss 3.2.7 w/ JRE 1.4.2_08
- PostgreSQL 8.0.2 on the same machine as JBoss
- No EJBs
- We have 7 process definitions deployed, each managing from 10 to 100 process variables.
When, for example, the "jbpm_exelog" table reaches about 300K rows, the performance decays dramatically: in particular, each time an endOfState() is called on an ExecutionService instance, it takes as long as 10 or more seconds to return.
We see that it's definitely a DB performance issue, since postgres daemon takes 50% CPU during method calls, while javaw stays idle.
After "cleaning" log tables performance becomes acceptable, where "cleaning" means disabling foreign keys, performing DELETEs, re-enabling foreign keys and VACUUMing database. This is not a definitive solution, though, as you can guess.
Is there a way to configure JBPM 2.0 so that logs are kept smaller than the default? We don't need to manage rollbacks, because process data are sent to an external repository (mainframe) each time the process changes state.
Thanks in advance
Unfortunately there is no solution provided by jBPM to keep the exelogs small. This is one of the areas in jBPM 3.0 where a lot of improvements have taken place.
What you could look at is looking at the queries that jBPM does when accessing the DB and optimize the indexes. Seeing the queries can be done by setting hibernate debug parameters (see the hibernate docs for that)