Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Fixed
Priority: Low
Fix Version/s: 7.13.1, 8.0.0
Affects Version/s: 7.5.3, 7.6.2
Component/s: Indexing
Labels:
- affects-zdu
- l1l2
- pse-request
- triaged

Fixed in Long Term Support Release/s:

Download 7.13
Introduced in Version:
7.05
Support reference count:
24
Symptom Severity:
Severity 2 - Major
UIS:
49
Bug Fix Policy:
View Atlassian Server bug fix policy
Current Status:

Hide

Atlassian Update – 11-12-2018

Hi everyone,

We have recently reviewed this issue and the overall interest in the problem. We acknowledge that the issue has a high impact on the software teams and agree that it has to be prioritised in the Short Term which corresponds the current status of the bug.

We would aim to address the issue within the next 3 months or so, yet please do not consider this a deadline to fix the bug, but rather as an estimated timeline. Please refer to Atlassian Bugfix Policy for the details on how we prioritise and deliver fixes in our product.

Atlassian will continue to watch this issue for further updates, so please feel free to share your thoughts in the comments.

Kind regards,

Jira Server Product Management

Show
Atlassian Update – 11-12-2018 Hi everyone, We have recently reviewed this issue and the overall interest in the problem. We acknowledge that the issue has a high impact on the software teams and agree that it has to be prioritised in the Short Term which corresponds the current status of the bug. We would aim to address the issue within the next 3 months or so, yet please do not consider this a deadline to fix the bug, but rather as an estimated timeline. Please refer to Atlassian Bugfix Policy for the details on how we prioritise and deliver fixes in our product. Atlassian will continue to watch this issue for further updates, so please feel free to share your thoughts in the comments. Kind regards, Jira Server Product Management

Description

Summary

JIRA performance is impacted by slow queries pulling data from the customfieldvalue table due to lack of indexing on the newly introduced UPDATED column.
This issue impacts JIRA 7.5 and above.
The issue is specifically amplified if JIRA has Service Desk installed with many SLAs defined.
This problem also impacts JIRA reindexing speed which can be already slow for large instances.

Environment

JIRA 7.5 and above
JIRA Service Desk
Big instances with 500k+ issues, and 500+ Custom fields.

Steps to Reproduce

Install JIRA 7.50 or any version above.
Create some test data containing a lot of custom fields.
Try to comment on an issue or create a new issue, or run reindexing.

Expected Results

Selects Queries on customfieldvalue should execute quickly, coming back in less than 2ms.

Actual Results

The queries are not fast and in big instances can cause performance issues.

 $ egrep "ISSUE=" atlassian-jira-sql.log*|grep "JiraTaskExectionThread-4 user1 820x222030x1"|grep customfieldvalue |head -500|tail -2
atlassian-jira-sql.log:2018-03-01 13:43:58,550 JiraTaskExectionThread-4 user1 820x222030x1 1nlzej4 /secure/admin/IndexReIndex.jspa 2ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='1310006' ORDER BY UPDATED ASC, ID ASC"
atlassian-jira-sql.log:2018-03-01 13:43:58,552 JiraTaskExectionThread-4 user1 820x222030x1 1nlzej4 /secure/admin/IndexReIndex.jspa 2ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='1310006' ORDER BY UPDATED ASC, ID ASC"
$ egrep "ISSUE=" atlassian-jira-sql.log*|grep "JiraTaskExectionThread-4 user1 820x222030x1"|grep customfieldvalue |head -1000|tail -2
atlassian-jira-sql.log:2018-03-01 13:43:59,644 JiraTaskExectionThread-4 user1 820x222030x1 1nlzej4 /secure/admin/IndexReIndex.jspa 2ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='1310004' ORDER BY UPDATED ASC, ID ASC"
atlassian-jira-sql.log:2018-03-01 13:43:59,646 JiraTaskExectionThread-4 user1 820x222030x1 1nlzej4 /secure/admin/IndexReIndex.jspa 2ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='1310004' ORDER BY UPDATED ASC, ID ASC"
 $ egrep "ISSUE=" atlassian-jira-sql.log*|grep "JiraTaskExectionThread-4 user1 820x222030x1"|grep customfieldvalue |head -3000|tail -2
atlassian-jira-sql.log:2018-03-01 13:44:05,913 JiraTaskExectionThread-4 user1 820x222030x1 1nlzej4 /secure/admin/IndexReIndex.jspa 2ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='1310009' ORDER BY UPDATED ASC, ID ASC"
atlassian-jira-sql.log:2018-03-01 13:44:05,922 JiraTaskExectionThread-4 user1 820x222030x1 1nlzej4 /secure/admin/IndexReIndex.jspa 2ms "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='1310009' ORDER BY UPDATED ASC, ID ASC"
 $

The issue gets much worse if we have many custom fields and if Service Desk is installed.
The above data is coming for a quite fast database, even though the queries are taking 2ms to complete.
The more custom fields we have the more it will take to get the data and time add up.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List

race.png
24 kB
16/Mar/2018 10:11 AM

Issue Links

is caused by

JSDSERVER-5299 SLA custom field should use last updated value if the event of a race condition

Closed

relates to

JRASERVER-67011 Duplicate value in "Time in Status" Field persists despite fix code in place

Closed

JSDSERVER-6043 Issue update/create is slow due to SLA indexing takes a long time

Closed

JRASERVER-71233 Custom fields with stale values break reindexing

Closed

JSDSERVER-5299 SLA custom field should use last updated value if the event of a race condition

Closed

JSDSERVER-5681 Non-optimal computation of SLA values in addDocumentFields() method

Closed

JSDSERVER-5685 While loading values for SLA CustomField getValueFromIssue method flushes EagerLoadingOfBizCustomFieldPersister cache

Closed

JRASERVER-67823 JIRA may become unresponsive when creating too many concurrent create requests due to transaction in OfBizCustomFieldValuePersister

Gathering Impact

is blocked by: DELTA-634 Loading...; PSR-136 Loading...

Mentioned in

FY19 Jira DC L1L2 Bugs - Recommend to fix in 7.13 --> 7.6 where technically possible: (5) Slow queries CF table [JRA-66890] **ER Blocker**

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(3 relates to, 2 is blocked by, 1 Mentioned in, 17 mentioned in)

Activity

People

Assignee:: Karol Lopacinski

Reporter:: Sherif Abdelfattah (Inactive)

Votes:: 15 Vote for this issue

Watchers:: 35 Start watching this issue

Dates

Created:: 05/Mar/2018 5:29 PM

Updated:: 26/Feb/2024 6:06 PM

Resolved:: 29/Jan/2019 2:17 PM