As part of Lexorank rebalancing, each rank value needs to be updated in AO_60DB71_LEXORANK table. At the same time, individual ranking operation has 1500 ms timeout for rank lock.
Due to slow DB operation, issue ranking operation fails to save new rank since rank lock expires. That makes Lexorank rebalancing process stall, usually you will see no progress in UI. Please note that situation is different to
- Large JIRA instance: 1M+ issue
- more specific large AO_60DB71_LEXORANK table
- The issue was initially reported with Jira connected to MySQL databases, but there were other database engines affected as well, such as Oracle and MS SQL Server.
- Run Lexorank rebalancing
- Monitor progress
- Lexorank rebalancing runs continuously and finishes successful.
- SQL executions is fast:
- Lexorank rebalancing stalls
- Issue ranking operation fails to save new rank since rank lock expires
- SQL executions is slow
- To enable logging set DEBUG for com.atlassian.greenhopper.service.lexorank and com.atlassian.greenhopper.manager.lexorank
- Example of logging:
- explain SELECT
speed id select_type table type possible_keys key key_len ref slow 1 SIMPLE AO_60DB71_LEXORANK ref index_ao_60db71_lex1569533973,index_ao_60db71_lex1694305086 index_ao_60db71_lex1569533973 8 const fast 1 SIMPLE AO_60DB71_LEXORANK range index_ao_60db71_lex1694305086,index_ao_60db71_lex1569533973 index_ao_60db71_lex1569533973 775 NULL
- Note that in case of slow select key_len is 8.
- 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
- Resolving the issue involves making MySQL select the right index, to do that, please recreate index (there will be small downtime during that operation):
- If you can allow longer downtime, you can run at mysql: "ANALYZE TABLE AO_60DB71_LEXORANK" (During the analysis, the table is locked with a read lock for InnoDB and MyISAM. see analyze-table)