Summary

Lock and Reindexing (Foreground Reindexing) on a Jira Data Center (consisting of more than one node) can run very slowly if there are issues with Rank field value missing. Typically this can occur in the following circumstances:

• after a major issue import 
• new rank field introduced due to project import 

Environment

Jira Datacenter consisting of at least two nodes.

Diagnosis

There are multiple entry points or symptoms that you might notice which should point towards there might be something wrong with the Rank field. Some common symptoms could be:

• Ranking issues on Agile board throws error on 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

• Lock and reindexing is progressing much slower than usual and thread dumps taken during lock and reindexing shows this repetitive pattern of rankInitially followed by backoff:

"NodeReindexServiceThread:thread-1" #325 prio=5 os_prio=0 tid=0x00007f3427e39000 nid=0x5e52 sleeping[0x00007f33468ae000]
   java.lang.Thread.State: TIMED_WAITING (sleeping)
	at java.lang.Thread.sleep(Native Method)
	at com.atlassian.greenhopper.service.lexorank.BackoffHandler$AggresiveSleepStrategy.backoff(BackoffHandler.java:106)
	at com.atlassian.greenhopper.service.lexorank.BackoffHandler.maybeWait(BackoffHandler.java:43)
	at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.rankInitially(LexoRankOperation.java:162)
...

• In the logs, we'd see node reindexer thread did not find the value for the Rank field and indexed the max rank value for issues:

[greenhopper.customfield.lexorank.LexoRankIndexer] Could not retrieve LexoRank value for issue[id=123456]. Indexing max LexoRank value instead.

• Lexorank DEBUG logging (in atlassian-greenhopper.log) shows that we're looking for a value in the AO_60DB71_LEXORANK table but for the Rank field (ID 10005) there was no value found for the issue (ID 1484962):

2021-02-09 12:04:12,637 http-nio-8080-exec-1915 url:/jira/rest/gree...er/1.0/sprint/rank username:ZZZZ DEBUG ZZZZ 724x9130276x5 1mj9c0u x.x.x.x /rest/greenhopper/1.0/sprint/rank [greenhopper.manager.lexorank.LexoRankDaoImpl]  LexoRank Query : SELECT "FIELD_ID","ID","ISSUE_ID","LOCK_HASH","LOCK_TIME","RANK","TYPE" FROM public."AO_60DB71_LEXORANK" WHERE "FIELD_ID" = ? AND "ISSUE_ID" = ? AND "TYPE" = ?; params[10005, 1484962, 1]

• To verify the same we checked directly in the database and found the field to be actually empty (note, there is a row for Rank CF (FIELD_ID) - 11718, but no entry for the 10005):

jira=# SELECT * FROM "AO_60DB71_LEXORANK" WHERE "ISSUE_ID" = '<ISSUE_ID>';
 BUCKET | FIELD_ID |   ID    | ISSUE_ID | LOCK_HASH | LOCK_TIME |   RANK    | TYPE
--------+----------+---------+----------+-----------+-----------+-----------+------
      1 |    11718 | 3062172 |  1484962 |           |           | 1|i009v3: |    1

• Check to find the number of issues that are missing rank field values 

SELECT COUNT(id) FROM jiraissue ji LEFT OUTER JOIN  (select * from "AO_60DB71_LEXORANK" where "FIELD_ID" = <rank_field_id> ) lr ON ji.id = lr."ISSUE_ID"  WHERE lr."ISSUE_ID" IS NULL  and ji.archived = 'N'

Cause:

For example, if a major issue/project import happened on the instance, all the imported issues will need to Reindexed. During the import if we introduced a new Rank field this means not only the imported issues but all Jira issues will now need to have a Rank field value for both rank fields. If you have 1M issues on the instance with 2 Rank fields then you'll now need to have 2M Rank field values populated in the AO_60DB71_LEXORANK table.

This can happen either through a manual reindexing, The background reindexing thread indexing each issue missing from the index or due to an issue update action triggering a during reindexing. During the reindexing proces, Jira finds there's no value for the Rank field(s) for the issue(s) and tries to create one (rankInitially). Due to specific environmental factors like DB latency or deadlock during reindexing, the initial ranking operation can timeout (backoff) and Jira will just enter the Max rank value in the index document for those issues to make sure the indexing is not lagging due to the missing rank field. However, this can significantly delay the Indexing operation speed and also cause ranking issues on board throw the error since there's no real rank field value for the affected issue(s) in the DB.

Solution

If there are only few issues returned:

The end goal is to get the issues returns in the query reindexed. You can do it it two different ways:

• Run a Lock and Reindex to create the Rank value from the issue ID

• You can use the REST API call to trigger an index just the issue ID

  POST https://<BASE-URL>/rest/api/2/reindex/issue?issueId=<ISSUE_ID>

• Check to confirm using the SQL query to ensure a Rank field value is populated: 

  SELECT * FROM "AO_60DB71_LEXORANK" WHERE "ISSUE_ID" = '<ISSUE_ID>';

• Once the Rank field is populated in the table, you should be able to re-rank the issue on the Agile board

If Reindexing is taking significantly long time and progressing very slowly

• • Determine if there are multiple Rank fields on your instance (check the Lexorank Management page on the UI) 
  • Check in atlassian-jira.log for "indexing-stats" and see if Rank field(s) is one of the costliest custom fields: Indexing Stats - cost of indexing custom fields
  • Follow this KB to determine if the second rank field is in use anywhere: Remove duplicate Rank fields in Jira server
  • Remove the second rank field if it's not being used any more 

Related Lexorank KB Articles

• Troubleshooting LexoRank System Issues
• Understand the LexoRank managment page in Jira server

Relevant Bugs & Feature Requests:

 In recent times, we've come to notice that having multiple Rank fields can be potential problem with indexing performance and the following bugs and feature requests are related to this: 

JSWSERVER-20910 - Getting issue details... STATUS

JSWSERVER-20845 - Getting issue details... STATUS

JSWSERVER-20911 - Getting issue details... STATUS

JSWSERVER-13098 - Getting issue details... STATUS