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

Missing index for MySQL 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-alpha5
    • Component/s: engine
    • Labels:
      None

      Description

      The following DELETE statement uses no index on MySQL which leads to performance issues:

      -- deleteHistoricDetailByteArraysByIds_mysql
      delete B from ACT_GE_BYTEARRAY B inner join ACT_HI_DETAIL D on B.ID_ = D.BYTEARRAY_ID_ inner join ACT_HI_TASKINST T on TASK_ID_ = T.ID_ and ( T.PROC_INST_ID_ IN ( ? ) ) WHERE D.BYTEARRAY_ID_ is not NULL;
      

      The problem can be solved by adding the following index:

      CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
      

      Query plan before the index has been set

      id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	D	(null)	ALL	ACT_IDX_HI_DETAIL_TASK_ID,ACT_IDX_HI_DETAIL_BYTEAR	(null)	(null)	(null)	437950	50.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
      1	DELETE	B	(null)	eq_ref	PRIMARY	PRIMARY	194	magma.D.BYTEARRAY_ID_	1	100.0	(null)
      

      Query plan after the index has been set

      id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	D	(null)	range	ACT_IDX_HI_DETAIL_TASK_ID,ACT_IDX_HI_DETAIL_BYTEAR,ACT_IDX_HI_DETAIL_TASK_AND_BYAR	ACT_IDX_HI_DETAIL_TASK_AND_BYAR	195	(null)	218975	100.0	Using where; Using index
      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
      1	DELETE	B	(null)	eq_ref	PRIMARY	PRIMARY	194	magma.D.BYTEARRAY_ID_	1	100.0	(null)
      

      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 created 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: