Querying process history is **SLOW**

[martin.m...@gmail.com]

Hello,

We are currently testing the enterprise version of the Camunda platform. After processing around 30k requests, the History feature because unusable: History View -> Process Instances,- just keep spinning. By looking at the postgres activity it looks like it is waiting for some **very** long queries to complete.

We have backend configured to PostgreSQL, indices added as advised by the User Guide (https://docs.camunda.org/manual/7.3/guides/user-guide/#process-engine-database-configuration-additional-database-schema-configuration).

Any advice for this situation?
Thanks

[Sebastian Menski]

Hi Martin,

can you specify which specific queries take long? Currently there is one ticket for the [1] query which is started by:

/camunda/api/engine/engine/default/history/process-definition/ID/statistics?canceled=true&completeScope=true

Do you have other slow queries? It would be enough to open the network tab in the browser developer console (F12)

and see which queries are not returning or taking longer than some seconds.

Cheers,

Sebastian

[1] https://app.camunda.com/jira/browse/CAM-5257

[martin.m...@gmail.com]

Hi Sebastian,

`select * from pg_stat_activity ;` shows a bunch of these queries:

select count(distinct RES.ID_)
from
ACT_HI_PROCINST RES
WHERE PROC_DEF_ID_ = $1
AND (
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = $2
AND
A.PERMS_ & $3 = $4
AND
A.RESOURCE_TYPE_ = $5
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM

[Sebastian Menski]

Hi Martin,

thanks for the query. It may be related to the authorizations. It would be great if you could try to disable the authorizations and test this again [1]. If you need help to disable

the authorizations please let me know. Also it would be interesting to know which Camunda version, Application Server and PostgreSQL version you are using.

Cheers,
Sebastian

[1] https://docs.camunda.org/manual/7.4/reference/deployment-descriptors/tags/process-engine/#configuration-properties

[martin.m...@gmail.com]

Hi Sebastian,

Sorry for the late reply. I have disabled authorization, by setting "authorizationEnabled" flag to false (`<property name="authorizationEnabled">false</property>`) inside project's `processes.xml`. That didn't help. I have extracted full queries which are taking really long time:

https://gist.github.com/anonymous/90ce4200a5849616413e

The wait time seems to be related to the count of process instance log records created in the database. We use Camunda 7.4.1 Enterprise Edition, PostgreSQL: 9.4.5. Please let me know if you need more information.

Regards,
Martin

[martin.m...@gmail.com]

OK, I have set the authorizationEnabled flag to false in the `server/apache-tomcat-8.0.24/conf/bpm-platform.xml` as well, and this time everything loads fast.

[Sebastian Menski]

Hi Martin,

thanks for conformation. I created a Ticket for this problem and we will look into it [1].

Cheers,

Sebastian

[1] https://app.camunda.com/jira/browse/CAM-5352