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

JIRA performance is impacted by slow queries pulling data from the customfieldvalue table

XMLWordPrintable

    • 7.05
    • 24
    • Severity 2 - Major
    • 49
    • 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

      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

      1. Install JIRA 7.50 or any version above.
      2. Create some test data containing a lot of custom fields.
      3. 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.

        1. race.png
          race.png
          24 kB

              klopacinski Karol Lopacinski
              sabdelfattah Sherif Abdelfattah (Inactive)
              Votes:
              15 Vote for this issue
              Watchers:
              35 Start watching this issue

                Created:
                Updated:
                Resolved: