Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-8064

Deleting deployments is slow on MySQL and MariaDB

    Details

      Description

      Deleting a deployment triggers deletion of job log exception stack traces via the statement

      delete
      from
      ACT_GE_BYTEARRAY
      WHERE ID_ in (
      select
      JOB_EXCEPTION_STACK_ID_
      from
      ACT_HI_JOB_LOG
      WHERE JOB_EXCEPTION_STACK_ID_ is not null
      and DEPLOYMENT_ID_ = <deployment_id>
      )
      

      On MySQL (and probably also MariaDB), this results in a query plan like

      +----+--------------------+------------------+------+---------------+------+---------+------+---------+-------------+
      | id | select_type        | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
      +----+--------------------+------------------+------+---------------+------+---------+------+---------+-------------+
      |  1 | PRIMARY            | ACT_GE_BYTEARRAY | ALL  | NULL          | NULL | NULL    | NULL |  125093 | Using where |
      |  2 | DEPENDENT SUBQUERY | ACT_HI_JOB_LOG   | ALL  | NULL          | NULL | NULL    | NULL | 3767036 | Using where |
      +----+--------------------+------------------+------+---------------+------+---------+------+---------+-------------+
      

      Note that the subselect is a DEPENDENT SUBQUERY. This means the sub query is correlated to the outer query and evaluted once per distinct value of the outer query (see https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_select_type). In this case, it is executed once for each row in ACT_GE_BYTEARRAY. Runtime depends linearly on the cardinality of ACT_GE_BYTEARRAY.

      A better query avoiding the sub query would be

      delete b from ACT_GE_BYTEARRAY b inner join ACT_HI_JOB_LOG j on b.ID_ = j.JOB_EXCEPTION_STACK_ID_ and j.DEPLOYMENT_ID_ = <id_of_deployment_to_delete>;
      

      Also check if other such queries are executed during deployment deletion.

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Unassigned
            Reporter:
            thorben.lindhauer Thorben Lindhauer
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development