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

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

XMLWordPrintable

    • 6.04
    • 94
    • Severity 3 - Minor
    • 158
    • 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.

      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.

        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

              psuwala ΞΔ (Inactive)
              astephen@atlassian.com Adrian Stephen
              Votes:
              60 Vote for this issue
              Watchers:
              102 Start watching this issue

                Created:
                Updated:
                Resolved: