Progress of LexoRank Balancing Operation is extremely slow in Jira( running on MSSQL DB) and performance is severely impacted while Lexorank balance operation is in Progress
Symptoms
- The LexoRank balancer job runs very slow. The percentage completion can be tracked under Administration () > System > Lexorank management page. It may only progress by 1% in 4-5 hours depending upon the number of issues in the instance.
- CPU utilization of the DB server is very high and on checking the DB queries taking the maximum CPU in the DB, top queries belong to the DB table AO_60DB71_LEXORANK.
- Below entries can be found in atlassian-greenhopper.log file:
2021-12-09 17:34:29,059 lexorank-executor-thread-0 ERROR ServiceRunner [greenhopper.manager.lexorank.LexoRankDaoImpl] The query has timed out.
java.sql.SQLTimeoutException: The query has timed out.
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:226)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:471)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69)- DB pool connections usage increases up to threshold limit.
Environment
Applicable for Jira JIRA 7.x or higher running with MSSQL Database
.
Diagnosis
Run the below query in the Database and verify if Datatype of RANK and LOCK_HASH is varchar as can be seen below:
exec sp_columns 'AO_60DB71_LEXORANK' ;
Cause
An incorrect data type for LOCK_HASH and RANK column can cause SQL Server to use an inefficient execution plan which is likely to take more than 1500ms to complete, which in turn can trigger the hardcoded timeout in JIRA and the ranking operation will fail.
Solution
1. Stop JIRA.
2. Backup your database.
3. Delete all indexes from the table AO_60DB71_LEXORANK, except for the primary key.
To view Current Index on table:
exec sp_helpindex 'jiraschema.AO_60DB71_LEXORANK' ;
For dropping Index:
DROP INDEX jiraschema.AO_60DB71_LEXORANK.index_ao_60db71_lex1569533973;
DROP INDEX jiraschema.AO_60DB71_LEXORANK.index_ao_60db71_lex1632828616;
DROP INDEX jiraschema.AO_60DB71_LEXORANK.index_ao_60db71_lex1694305086;
DROP INDEX jiraschema.AO_60DB71_LEXORANK.index_ao_60db71_lex604083109;
Replace jiraschema with the DB schema name
4. Change the data type on the columns AO_60DB71_LEXORANK.RANK and AO_60DB71_LEXORANK.LOCK_HASH using these SQL queries:
ALTER TABLE dbo.AO_60DB71_LEXORANK ALTER COLUMN RANK NVARCHAR(255) NOT NULL;
ALTER TABLE dbo.AO_60DB71_LEXORANK ALTER COLUMN LOCK_HASH NVARCHAR(255) NULL;
5. Run the following SQL queries to flush caches and update statistics on the database:
DBCC FREEPROCCACHE;
SP_UPDATESTATS;
6. Start JIRA. The indexes for the table AO_60DB71_LEXORANK will be automatically recreated.
There is an open request JRASERVER-59816 - Getting issue details... STATUS to track this bug.