All Camunda distributions, Camunda version 7.15.x
Description (Required on creation; please attach any relevant screenshots, stacktraces, log files, etc. to the ticket):
The following update query performs poorly on MySQL with high cardinalities in the ACT_GE_BYTEARRAY table:
- Add 21 million records to the table ACT_GE_BYTEARRAY.
- Create a batch job that modifies some process instances.
- Wait until the batch job shows 100% progress in the cockpit.
- The batch job won't be finished for a long time (switch from runtime to history).
- Check the running statements in the database.
The statement runs for several minutes (~30 minutes for customers) up to more than one hour (see attached Support cases).
The statement finishes in a reasonable amount of time, ideally sub-second.
The EXPLAIN shows the whole table ACT_GE_BYTEARRAY is joined with the ACT_HI_JOB_LOG table for every row in the byte array table. The higher the cardinalities in the byte array table, the longer the statement will take to complete.
Use an INNER JOIN for the update statement instead of a subselect, which is known to perform poorly on MySQL. That way, the job log table will be queried first and the resulting rows will be used to join with the byte array table which is more efficient.
The original query shows a runtime of 1,780,000 milliseconds (~30 minutes) in no_join_query.json. The INNER JOIN equivalent shows a runtime of ~11 milliseconds in join_query.json. This is based on roughly ~23 million records in ACT_GE_BYTEARRAY and ~40 million records in ACT_HI_JOB_LOG.
- We use the INNER JOIN for MySQL already for updating the removal time of historic incidents in the following way: