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

Historic activity statistics query takes too long for large datasets

    Details

      Description

      Steps to reproduce

      • there is a lot of entries in historic activity database table
      • request Historic activity statistics by calling {{GET /history/process-definition/ {id}

        /statistics}}

      Actual result

      • it takes too long the query to be completed, sometimes the timeout happens

      Expected result

      • statistics is returned in reasonable amount of time, no matter how large the database is

        Issue Links

          Activity

          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited

          First thoughts.

          Approach 1. Store intermediate results (once in a period in background)

          E.g. table ACTIVITY_STATISTICS (may be metrics table can be used) with fields:

          • datetime
          • proc_def_id
          • act_inst_id
          • finished
          • running
          • completedScope
          • cancelled

          Request statistics for dateX:
          1. Select from ACTIVITY_STATISTICS where datetime <= dateX
          2. Calculate statistics for the residuary period (datetime, dateX]
          3. Aggregate the results

          Problems:
          1. What if history cleanup has run?
          2. What if delete historic process instance was called?

          Possible solutions:
          1. Correct the data in statistics table before removal -> can be tricky as new data at the same time can be coming and we can't just move the datetime, we then need to take into account the whole delta: removed + added. This can slow down history cleanup.
          2. Clean all previously stored statistics and recalculate it period by period -> in case of constantly running history cleanup won't work.

          Approach 1a.
          Sacrifice the accuracy and not correct the data after it was added (the same as Optimize do).

          Approach 2.
          Only allow selecting statistics (and showing hashmap) for some period. + Make the period selectable and default period configurable on the front-end.

          To be discussed.

          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited First thoughts. Approach 1. Store intermediate results (once in a period in background) E.g. table ACTIVITY_STATISTICS (may be metrics table can be used) with fields: datetime proc_def_id act_inst_id finished running completedScope cancelled Request statistics for dateX: 1. Select from ACTIVITY_STATISTICS where datetime <= dateX 2. Calculate statistics for the residuary period (datetime, dateX] 3. Aggregate the results Problems: 1. What if history cleanup has run? 2. What if delete historic process instance was called? Possible solutions: 1. Correct the data in statistics table before removal -> can be tricky as new data at the same time can be coming and we can't just move the datetime, we then need to take into account the whole delta: removed + added. This can slow down history cleanup. 2. Clean all previously stored statistics and recalculate it period by period -> in case of constantly running history cleanup won't work. Approach 1a. Sacrifice the accuracy and not correct the data after it was added (the same as Optimize do). Approach 2. Only allow selecting statistics (and showing hashmap) for some period. + Make the period selectable and default period configurable on the front-end. To be discussed.
          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited

          Second approach was implemented. Now we can define restrictions on start date and end end of historic process instances, for with the statistics will be shown.

          Indices are tuned to correspond to the statistics SQL query, but it can still happen that in some specific situation more analysis of SQL execution plan will be needed and may be some further index adjustments will be required.

          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited Second approach was implemented. Now we can define restrictions on start date and end end of historic process instances, for with the statistics will be shown. Indices are tuned to correspond to the statistics SQL query, but it can still happen that in some specific situation more analysis of SQL execution plan will be needed and may be some further index adjustments will be required.

            People

            • Assignee:
              yana.vasileva Yana Vasileva
              Reporter:
              meyer Daniel Meyer
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development