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

Missing not null filter for MySQL DELETE statement slows down History Cleanup

    XMLWordPrintable

    Details

    • Type: Bug Report
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: 7.10.0, 7.9.5, 7.8.11
    • Fix Version/s: 7.10.0, 7.9.6, 7.8.12, 7.10.0-alpha6
    • Component/s: engine
    • Labels:
      None

      Description

      The following DELETE statement for MySQL leads to performance issues:

      -- deleteHistoricDetailsByIds_mysql 
      delete D from ACT_HI_DETAIL D inner join ACT_HI_TASKINST T on TASK_ID_ = T.ID_ and ( T.PROC_INST_ID_ IN ( ? ) );
      

      The DELETE statement can be improved by adding a not null filter as follows:

      delete D from ACT_HI_DETAIL D inner join ACT_HI_TASKINST T on TASK_ID_ = T.ID_ and ( T.PROC_INST_ID_ IN ( ? ) ) and D.TASK_ID_ is not null;
      

      Query plan before the DELETE statement has been improved

      id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	DELETE	D	(null)	ALL	ACT_IDX_HI_DETAIL_TASK_ID	(null)	(null)	(null)	437952	100.0	Using where
      1	SIMPLE	T	(null)	eq_ref	PRIMARY,ACT_IDX_HI_TASKINST_PROCINST,ACT_IDX_HI_TASKINSTID_PROCINST	PRIMARY	194	magma.D.TASK_ID_	1	5.0	Using where
      

      Query plan after the DELETE statement has been improved

      id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	DELETE	D	(null)	range	ACT_IDX_HI_DETAIL_TASK_ID	ACT_IDX_HI_DETAIL_TASK_ID	195	(null)	1	100.0	Using where
      1	SIMPLE	T	(null)	eq_ref	PRIMARY,ACT_IDX_HI_TASKINST_PROCINST,ACT_IDX_HI_TASKINSTID_PROCINST	PRIMARY	194	magma.D.TASK_ID_	1	5.0	Using where
      

      Setting the index results in:

      • no full table scan is performed as the column type of the first row shows range instead of ALL
      • the index is used as the column key of the first row is not (null) anymore but shows the used index

        Attachments

          Activity

            People

            Assignee:
            nikola.koevski Nikola Koevski
            Reporter:
            tassilo.weidner Tassilo Weidner
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: