I am facing a problem with the amount of the data I have stored on camunda which is causing camunda ui to be un responsive and putting a high load on MYSQL
Camunda Version - 7.11
MySQL version - 5.7
Problem: On opening the camunda-ui’s cockpit the time required to view the data on the dashboard i.e running process instances, deployments tasks and other information timesout. I believe this is happening due to the huge amount of data we have stored on Camunda. We have close to 1.7k deployments and around 520000 running process instances. Also after trying to open the cockpit other parts of the camunda ui give way making the front-end completely un responsive.
Attempts at optimisation: We tried increasing the number of camunda pods on our EKS CLUSTER and changed the database isolation level from REPEATABLE READ to READ COMMITED. But unfortunately this did not work.
Observation: While checking MYSQL we found that this query
SELECT count(DISTINCT RES.ID_) FROM ACT_RU_TASK RES LEFT JOIN ACT_RE_PROCDEF PROCDEF ON RES.PROC_DEF_ID_ = PROCDEF.ID_ LEFT JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ IN ('breeze', '*') OR A.GROUP_ID_ IN ('camunda-admin') ) AND ( ( A.RESOURCE_TYPE_ = 7 AND A.PERMS_ & 2 = 2 OR A.RESOURCE_TYPE_ = 6 AND A.PERMS_ & 64 = 64 ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ IN (RES.ID_, PROCDEF.KEY_, '*') ) WHERE (1 = 1) AND ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) is taking close to 30 seconds while trying to open the cockpit.
- Is Camunda lagging because of the huge amount of running process instances?
- Can someone please suggest any optimisations which will be worthwhile exploring.