Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-63002

Create new composite indexes for changegroup and changeitem tables to improve performance

    XMLWordPrintable

    Details

    • Introduced in Version:
      6.04
    • Support reference count:
      94
    • Symptom Severity:
      Severity 3 - Minor
    • UIS:
      158
    • Current Status:
      Hide
      Atlassian Update – 01 February 2019

      Hello everyone,

      We introduced indexes to changegroup and changeitem tables.
      The new indexes are named chggroup_issue_id and chgitem_group_field. If you modified your indexes please adjust names/remove them. Otherwise they will get duplicated.

      As a friendly reminder - remember to update your database statistics frequently. Otherwise your queries will be vulnerable to parameter sniffing.

      Sincerely,
      Jira Bugfix Team.

      Show
      Atlassian Update – 01 February 2019 Hello everyone, We introduced indexes to changegroup and changeitem tables. The new indexes are named chggroup_issue_id and chgitem_group_field. If you modified your indexes please adjust names/remove them. Otherwise they will get duplicated. As a friendly reminder - remember to update your database statistics frequently. Otherwise your queries will be vulnerable to parameter sniffing. Sincerely, Jira Bugfix Team.

      Description

      Issue Summary

      Improve database indexes for changeitem and changegroup tables.

      Steps to Reproduce

      1. Have a Jira instance with a large number of issues, with heavy Agile usage.

      Expected Results

      Jira performance remains as usual.

      Actual Results

      Jira performance decreases greatly.

      Notes

      • The default indexes on both tables are as illustrated in the following screenshots:
        Table changeitem

        table changegroup
      • Based on users's DBA findings from their database monitoring, there's a high and troublesome CPU impact of the following query:
        SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE,
         CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
         FROM changegroup CG INNER JOIN
         changeitem CI ON CG.ID = CI.groupid
         WHERE CG.issueid=:1 AND CI.FIELD=:2
        ORDER BY CG.CREATED ASC, CI.ID ASC
        

        This is understandable as JIRA retrieves change history for rendering issue history tabs and JIRA Agile reports. However the DBA was able to significantly improve the performance by creating 2 new indexes:

        CREATE UNIQUE INDEX JIRADBUSER.CHANGEGROUP_SCX1
         ON JIRADBUSER.CHANGEGROUP
        (ISSUEID, ID)
        TABLESPACE JIRA_DATA;
        
        CREATE INDEX JIRADBUSER.CHANGEITEM_SCX1
         ON JIRADBUSER.CHANGEITEM
         (GROUPID, FIELD)
        TABLESPACE JIRA_DATA;
        

        which is believed to improve the performance considerably. We are assuming the logic behind this is the join on changegroup.id <==> changeitem.groupid Please consider the following arguments from the user:

      Workaround

      Note: Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

      Adding the indexes mentioned before seems to alleviate the problem.

        Attachments

        1. Feedback DBA Securex.pdf
          277 kB
        2. Selection_198.jpg
          Selection_198.jpg
          27 kB
        3. Selection_199.jpg
          Selection_199.jpg
          36 kB
        4. v$sqlstats_After_Drop_Indexes.xlsx
          19 kB
        5. v$sqlstats_Before_Drop_Indexes.xlsx
          14 kB

          Issue Links

            Activity

              People

              Assignee:
              psuwala ΞΔ
              Reporter:
              astephen@atlassian.com Adrian
              Votes:
              60 Vote for this issue
              Watchers:
              96 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: