Uploaded image for project: 'Jira Software Server and Data Center'
  1. Jira Software Server and Data Center
  2. JSWSERVER-15874

Issue ranking operation fails to save new rank due to slow DB operation in MySQL and MS-SQL

    XMLWordPrintable

Details

    Description

      Summary

      While doing ranking operation Issue ranking request has two limits

      • Total time limit for retry for whole method: 4000 ms
      • More specific timeout for rank lock: 1500 ms
        In some cases (see below) issue ranking operation fails to save new rank due to rank lock expire. This is caused by doing number of SQL calls to AO_60DB71_LEXORANK table under lock and some of them are slow (see below).

      At this stage, we believe this is not a JIRA bug. That being said, we will keep ticket open for some time to monitor the problem.

      Environment

      • Large JIRA instance: 1M+ issue
        • Basically we need large AO_60DB71_LEXORANK table
      • MySQL 5.6.x
        • We identified problem for MySQL (5.6.x only), other DBs might be also affected, needs more checking
      • JIRA

      Steps to Reproduce

      1. Try to rank issue at the bottom of the RANK
        • Currently problem is easily reproducible for EPIC ranking, since they are mostly at the bottom

      Expected Results

      Rank operation is successful.

      Actual Results

      • EPIC ranking operation fails and that give error in UI:
        JIRA Software cannot execute the rank operation at this time. Other users may be ranking the issues that you are trying to rank. Please try again later
        • Rest call: /rest/agile/1.0/epic/<Epic_ID>/rank returns 500

      Notes

      • Detailed debug logging shows that EPIC rank fails to store new rank in DB due to lock timeout.
        • To enable logging set DEBUG for com.atlassian.greenhopper.service.lexorank and com.atlassian.greenhopper.manager.lexorank
        • Logs
          2017-05-15 03:51:37,226 https-8443-exec-12 DEBUG admin 231x1024011x9 2h8zm5 1.1.1.1 /rest/agile/1.0/epic/1582851/rank [greenhopper.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM AO_60DB71_LEXORANK WHERE FIELD_ID = ? AND RANK <= ? ORDER BY RANK DESC LIMIT 2; params[15180, 0|i18v13:]
          2017-05-15 03:51:42,614 https-8443-exec-12 DEBUG admin 231x1024011x9 2h8zm5 1.1.1.1 /rest/agile/1.0/epic/1582851/rank [greenhopper.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM AO_60DB71_LEXORANK WHERE FIELD_ID = ? AND RANK >= ? ORDER BY RANK ASC LIMIT 2; params[15180, 0|i18v1b:] 
          2017-05-15 03:51:42,617 https-8443-exec-12 DEBUG admin 231x1024011x9 2h8zm5 1.1.1.1 /rest/agile/1.0/epic/1582851/rank [greenhopper.service.lexorank.LexoRankOperation] Save of rank row [LexoRankRow{id=3101388, fieldId=15180, issueId=1582851, lockHash='8284c06d-a7bf-4da9-ae34-77fe6685e51a', lockTime=1494845497223, bucket=0, rank='0|i18v17:', type=ISSUE_RANK_ROW}] failed, retrying rank operation
          
      • slow SQL (3.47 sec)
        mysql>SELECT FIELD_ID,ID,ISSUE_ID,LOCK_HASH,LOCK_TIME,RANK,TYPE FROM AO_60DB71_LEXORANK WHERE FIELD_ID = 15180 AND RANK <= '0|i18v53:' ORDER BY RANK DESC LIMIT 2; 
        
      • Note that not all calls are slow, response time depends on actual RANK value (position in the table).

      To help investigate problem like this in the future, please see KB based on this bug:

      MySQL notes

      After some additional deep research from our side, we found a MySQL bug with very similar description which was fixed in 5.7:

      Workaround

      • In case of MySQL, please upgrade to MySQL 5.7. We have a confirmation from the client that fixes the problem.
      • In case of MSSQL, problem was caused by wrong column type: RANK was varchar, it should be nvarchar.

      Note on fix

      Since not all clients will be able quickly upgrade to MySQL 5.7, we decided to implement workaround for the problem in JIRA itself. In corresponding fixed version, code was changes to avoid SQL "RANK <= 'abc' ORDER BY RANK DESC"

      Attachments

        Issue Links

          Activity

            People

              izinoviev Ilya Zinoviev (Inactive)
              ayakovlev@atlassian.com Andriy Yakovlev [Atlassian]
              Votes:
              8 Vote for this issue
              Watchers:
              33 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: