How to utilize deployment Cache in every instance execution to avoid db calls ?

Hi,
Using Camunda 7 community edition (Docker image camunda-bpm-platform:tomcat-7.20.0) with Postgresql as database.
In a setup where bpmn deployment is done only once and utilizing same model for every runs (process instance start’s), expecting deployment cache should be utilizing it fully to avoid SQL calls as much as this engine could.
When enabled engine.persistence debug level, noticed that below query getting logged every time in instance execution.
Select * from ACT_RE_PROCDEF where ID = $1
Does it mean deployment cache is not getting fully utilized in entire instance execution?
Above query is just one example… It might be applicable to other “select” queries in Camunda process instance run also.

More details… Here are the example logs:

  1. At deployment time, it shows :
13-May-2024 07:39:22.936 FINE [http-nio-9016-exec-19] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-03006 Cache state after flush: [
  PERSISTENT ResourceEntity[ea844fe9-10fb-11ef-a1b0-a65565181738]
  PERSISTENT ProcessDefinitionEntity[default_tasks1:1:ea8699da-10fb-11ef-a1b0-a65565181738]
  PERSISTENT DeploymentEntity[ea82c948-10fb-11ef-a1b0-a65565181738]
]
13-May-2024 07:39:22.937 FINE [http-nio-9016-exec-19] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-03008 Flush Summary: [
  INSERT DeploymentEntity[ea82c948-10fb-11ef-a1b0-a65565181738]
  INSERT ResourceEntity[ea844fe9-10fb-11ef-a1b0-a65565181738]
  INSERT ProcessDefinitionEntity[default_tasks1:1:ea8699da-10fb-11ef-a1b0-a65565181738]
]
  1. First run, cache state shows:
13-May-2024 07:41:19.957 FINE [http-nio-9016-exec-3] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==>  Preparing: select * from ACT_RE_PROCDEF where ID_ = ?
13-May-2024 07:41:19.957 FINE [http-nio-9016-exec-3] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Parameters: default_tasks1:1:ea8699da-10fb-11ef-a1b0-a65565181738(String)

13-May-2024 07:41:20.472 FINE [http-nio-9016-exec-3] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-03006 Cache state after flush: [
  PERSISTENT ProcessDefinitionEntity[default_tasks1:1:ea8699da-10fb-11ef-a1b0-a65565181738]
]
  1. Second run cache state shows:
13-May-2024 07:42:12.103 FINE [http-nio-9016-exec-8] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==>  Preparing: select * from ACT_RE_PROCDEF where ID_ = ?
13-May-2024 07:42:12.103 FINE [http-nio-9016-exec-8] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Parameters: default_tasks1:1:ea8699da-10fb-11ef-a1b0-a65565181738(String)

13-May-2024 07:42:12.115 FINE [http-nio-9016-exec-8] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-03006 Cache state after flush: [
  PERSISTENT ProcessDefinitionEntity[default_tasks1:1:ea8699da-10fb-11ef-a1b0-a65565181738]
]

Not sure if understanding is correct but if Cache is already having entry for ProcessDefinitionEntity, should not it be utilizing in second run and hence skip

select * from ACT_RE_PROCDEF

call for every instance execution ?

How does the deployment cache work and when will it get flushed ?
Is it the case that it can be flushed in every instance execution based on some condition?
Can the scope to retain cache be increased based on some configuration/usage ?
Want to leverage deployment cache fully to reduce calls to postgres db calls wherever possible.

Can someone help answering question please ?
Did check on Caching of process definitions in camunda , but it does not completely help. @aravindhrs / @felix-mueller
Thank you.

Hi,

The query is made to check that the definition still exists (e.g. the deployment could be deleted in the meantime). The cache avoids the loading and parsing of the XML, but not this one query.

Cheers,
Thorben

Thank you for the response @thorben . Appreciate it.
Ok. Actually, the question came up with understanding of a point in above document where it mentioned “Process definition” also. So thought it might be caching around "ACT_RE_PROCDEF " table data also.

Follow up question on it:
Is not there any code to flush all the cache related (including process definition) data, when there is change in deployment data ? If there is modification/deletion around that deployment data, it must be deleting all cache related operation ? or may be this particular query should be touched for cache ?
Or
Can there be any way via custom cache implementation which can also cache it and flush it when there is any modification/deletion ?

Little more background :
Had enabled db operations related debug level to find how many db calls are involved in process execution (our setup where Camunda 7.20 is pointing to AWS Aurora Postgres instance), if inbuilt cache utilizing all possible cached data ? In performance insights, it shows most of wait time is in LWLock:BufferContent when tried a load test…


And most queries involved in wait are 3 :

insert into ACT_RU_EXECUTION ( ID_, ROOT_PROC_INST_ID ......
select distinct RES.* from ACT_RU_EXECUTION RES inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_  ........
select * from ACT_RE_PROCDEF where ID_ = $1

So thought if “select * from ACT_RE_PROCDEF” can be avoided which can ultimately help avoiding WAIT time on db operation to improve overall performance ?