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

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:

• Issue ranking operation fails - JIRA Software cannot execute the rank operation at this time

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:

• https://bugs.mysql.com/bug.php?id=61631
  • fixed in 5.7
• Quote from https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html

  For the index merge access method, the optimizer could make a suboptimal choice of indexes to use. (Bug #14095506)

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"