Uploaded image for project: 'Automation for Jira Server'
  1. Automation for Jira Server
  2. JIRAAUTOSERVER-828

Non-performant query on the AO_589059_RULE_STAT table causes high database cpu usage

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Low Low
    • None
    • 9.0.0
    • Rule execution (engine)
    • Severity 2 - Major
    • 4

      Issue Summary

      This is reproducible on Data Center: yes

      Automation for Jira has a certain type of query repeatedly run on the AO_589059_RULE_STAT as part of the scheduled RuleInsightsUpdateJob which populates the AO_589059_RULE_STAT_ROLLUP_* tables

      select sum(`AO_589059_RULE_STAT`.`QUEUED_ITEM_COUNT`)
from `AO_589059_RULE_STAT` `AO_589059_RULE_STAT`
where `AO_589059_RULE_STAT`.`CREATED` >= ?
limit ?

      This query can become non-performant over time and result in increased database CPU usage and hence reducing the database performance.

      Steps to Reproduce

      1. Generate a very high volume of rule executions on a given day.
      2. Verify that the rule executions are logged in the AO_589059_RULE_STAT table.
      3. Let the periodic RuleInsightsUpdateScheduler to run which causes the above query to be run repeatedly.

      Expected Results

      The query does not cause increased database cpu usage and performance of other queries are not hampered.

      Actual Results

      The above query is identified as one of the top CPU consuming queries.

      Workaround

      A index on the table such as follows is shown to relieve the inefficient query execution and reduce CPU usage

       CREATE NONCLUSTERED INDEX [IDX_SUPP_QUEUED_ITEM] ON [jiraschema].[AO_589059_RULE_STAT] ([CREATED]) INCLUDE ([QUEUED_ITEM_COUNT])

       

          Form Name

            [JIRAAUTOSERVER-828] Non-performant query on the AO_589059_RULE_STAT table causes high database cpu usage

            Gonchik Tsymzhitov added a comment -


            on 9.4.6

            Gonchik Tsymzhitov added a comment - on 9.4.6

              Unassigned Unassigned
              svenkatachari shrivatsaa (Inactive)
              Affected customers:
              5 This affects my team
              Watchers:
              7 Start watching this issue

                Created:
                Updated: