Consistent selects queries causing CPU spikes on Database side

Camunda 7 Topics Discussion & Questions
1

Hi,

We got into a production incident where we are seeing below query fire consistency and causing to our CPU, what process would fire these queries? we dint had issues over last 1 year of running camunda.

we are on camunda 7.10 and using postgres as backed database

SELECT DISTINCT RES.*
FROM act_ru_execution RES
INNER JOIN act_re_procdef P
ON RES.proc_def_id_ = P.id_
WHERE RES.parent_id_ IS NULL
AND P.key_ = $1
AND RES.suspension_state_ = $2
AND EXISTS (SELECT id_
FROM act_ru_variable
WHERE name_ = $3
AND RES.id_ = execution_id_
AND (( type_ IS NOT NULL
AND type_ = $4
AND long_ IS NOT NULL
AND long_ = $5 )))
ORDER BY RES.id_ ASC
LIMIT $6 offset $7

2

@harish_malavade Hi, just wondering if you had any success with this issue? Iā€™m facing the same issue currently in production, any help is appreciated, thanks

3

@EvertonM sorry about late reply, we identified a process that had two problems

  1. It had a gateway that was set to check for API response =200 and keep retrying the API until the response was 200, which caused the http connector task to be executed in loop
  2. We had a script task that was firing a process engine query to pull all child process instances for a parent in call activity scenario that was executed in loop

We had to suspend these bottleneck process.

we identified these queries based on our enhanced database logging, we had camunda deployed to AWS RDS

4

@harish_malavade Thanks for the response.

After further investigation, we were able to solve the issue by:

  1. creating an index to the act_ru_variable table.
  2. We identified that we were starting the camunda process too many times without the need to, so after revisiting the business logic we managed to reduce in more the half the number of times we started a process.

Also have Camunda (7.14) deployed to AWS RDS (Postgres).
Thanks

1 Like
5

Good to know you were able to solve this. we did add few additional indexes on act_ru_variable, act_ru_procinst and act_ru_ext_task tables on some of performance issues. We do have our database on RDS Postgress as well.
How are you deploying your app stack to AWS? are you using EC2 or Kubernetes?

6

@EvertonM For which columns?

7

We are deploying as a Docker container with ECS and Fargate

9

create index on ACT_RU_VARIABLE(TYPE_, LONG_) where (TYPE_ is not null and LONG_ is not null);

10