-
Bug
-
Resolution: Fixed
-
High (View bug fix roadmap)
-
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
-
-
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.
- is related to
-
-
- Gathering Impact
-
-
JRASERVER-65168 Add Multi-Column Index to JIRA Tables
- Closed
- relates to
-
-
- Gathering Impact
-
-
RAID-906 You do not have permission to view this issue
-
- is blocked by
-
PSR-38 Loading...
- Mentioned in
- mentioned in
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
[JRASERVER-63002] Create new composite indexes for changegroup and changeitem tables to improve performance
| Remote Link | Original: This issue links to "Page (Atlassian Documentation)" [ 408813 ] |
| Remote Link | Original: This issue links to "PSR-38 (Bulldog)" [ 383358 ] | New: This issue links to "PSR-38 (JIRA Server (Bulldog))" [ 383358 ] |
| Remote Link | Original: This issue links to "RAID-906 (JIRA Server)" [ 368695 ] | New: This issue links to "RAID-906 (JIRA Server (Bulldog))" [ 368695 ] |
| Remote Link | Original: This issue links to "RUM-2070 (Bulldog)" [ 380505 ] | New: This issue links to "RUM-2070 (JIRA Server (Bulldog))" [ 380505 ] |
| Affects Version/s | New: 7.13.18 [ 92692 ] |
| Labels | Original: affects-server affects-zdu l1l2 performance-scalability pse-request triage | New: affects-server affects-zdu database l1l2 performance-scalability pse-request triage |
| Remote Link | New: This issue links to "Page (Confluence)" [ 525360 ] |
| Remote Link | New: This issue links to "Page (Confluence)" [ 521320 ] |
| Description |
Original:
h3. Issue Summary
Improve database indexes for changeitem and changegroup tables. h3. Steps to Reproduce # Have a Jira instance with a large number of issues, with heavy Agile usage. h3. Expected Results Jira performance remains as usual. h3. Actual Results Jira performance decreases greatly. h3. Notes * The default indexes on both tables are as illustrated in the following screenshots: Table {{changeitem}} !Selection_198.jpg! table {{changegroup}} !Selection_199.jpg! * Based on users's DBA findings from their database monitoring, there's a high and troublesome CPU impact of the following query: {code} 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 {code} 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: {code} CREATE UNIQUE INDEX JIRADBUSER.CHANGEGROUP_SCX1 ON JIRADBUSER.CHANGEGROUP (ISSUEID, ID) TABLESPACE JIRA_DATA; {code} {code} CREATE INDEX JIRADBUSER.CHANGEITEM_SCX1 ON JIRADBUSER.CHANGEITEM (GROUPID, FIELD) TABLESPACE JIRA_DATA; {code} 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]. h3. Workaround {panel} (!) *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. {panel} Adding the indexes mentioned before seems to alleviate the problem. |
New:
h3. Issue Summary
Improve database indexes for changeitem and changegroup tables. h3. Steps to Reproduce # Have a Jira instance with a large number of issues, with heavy Agile usage. h3. Expected Results Jira performance remains as usual. h3. Actual Results Jira performance decreases greatly. h3. Notes * The default indexes on both tables are as illustrated in the following screenshots: Table {{changeitem}} !Selection_198.jpg! table {{changegroup}} !Selection_199.jpg! * Based on users's DBA findings from their database monitoring, there's a high and troublesome CPU impact of the following query: {code} 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 {code} 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: {code} CREATE UNIQUE INDEX JIRADBUSER.CHANGEGROUP_SCX1 ON JIRADBUSER.CHANGEGROUP (ISSUEID, ID) TABLESPACE JIRA_DATA; {code} {code} CREATE INDEX JIRADBUSER.CHANGEITEM_SCX1 ON JIRADBUSER.CHANGEITEM (GROUPID, FIELD) TABLESPACE JIRA_DATA; {code} 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]. h3. Workaround {panel} (!) *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. {panel} Adding the indexes mentioned before seems to alleviate the problem. |
| Remote Link | New: This issue links to "Page (Confluence)" [ 518604 ] |