Details
-
Bug
-
Resolution: Fixed
-
High
-
6.4.13, 6.4.14, 7.1.8, 7.3.2, 7.5.1, 7.6.10, 7.13.8, 7.13.11, 7.13.18
-
6.04
-
94
-
Severity 3 - Minor
-
158
-
-
Description
Issue Summary
Improve database indexes for changeitem and changegroup tables.
Steps to Reproduce
- 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:
- The performance impact of the change history db look up got worse after upgrading JIRA to JIRA 7.1.8, and is directly related to their Agile usage (i.e. it improves considerably as soon as they disable JIRA Agile).
- See attachment and reports from their DBA on the changes they have made with the indexes and the improvements seen Feedback DBA Securex.pdf
, v$sqlstats_After_Drop_Indexes.xlsx, v$sqlstats_Before_Drop_Indexes.xlsx.
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
Issue Links
- is related to
-
-
- Gathering Impact
-
-
JRASERVER-65168 Add Multi-Column Index to JIRA Tables
- Closed
- relates to
-
-
- Gathering Impact
-
-
RAID-906 Loading...
-
- is blocked by
-
- Mentioned in
- mentioned in
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-