Jira performance degradation in boards, queues, or searches after upgrading to 9.11.x/5.11.x
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
After upgrading to Jira Software version 9.11.x or Jira Service Management version 5.11.x users are hit with Jira performance degradation. Areas of application that are utilizing Jira search are the ones being impacted. The most common examples reported by end users are:
- Jira Software Boards and backlogs are taking a very long time to load.
- Jira Service Management Queues are taking a very long time load.
- Jira Basic Search is very slow.
- Jira Dashboards are very slow.
At the same time Operations team might notice increased activity on Jira database in the form of database reads.
Environment
- Jira Software 9.11.x
- Jira Service Management 5.11.x
Diagnosis
This issue can be observed during working hours by examining the application thread dumps and database activity.
Thread Dumps
You can examine the threads by following the steps in this article: Generating a thread dump. These are the threads of interest:
- http-nio-8081-exec-xyz url: /jira/rest/greenhopper/1.0/xboard/work/allData.json; user: user
http-nio-8080-exec-xyz url: /rest/servicedesk/{id}/{PKEY}/webfragments/sections/sd-queues-nav,servicedesk.agent.queues,servicedesk.agent.queues.ungrouped; user:user
The stack trace looks like this, and there are frequent database calls to the database from DefaultWorkflowSchemeManager.
org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:548)
org.ofbiz.core.entity.GenericDAO.createEntityListIterator(GenericDAO.java:882)
org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:862)
org.ofbiz.core.entity.GenericDAO.selectByAnd(GenericDAO.java:734)
org.ofbiz.core.entity.GenericHelperDAO.findByAnd(GenericHelperDAO.java:166)
org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:913)
org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:891)
org.ofbiz.core.entity.GenericDelegator.getRelated(GenericDelegator.java:1509)
org.ofbiz.core.entity.GenericDelegator.getRelated(GenericDelegator.java:1439)
com.atlassian.jira.ofbiz.DefaultOfBizDelegator.getRelated(DefaultOfBizDelegator.java:351)
com.atlassian.jira.ofbiz.WrappingOfBizDelegator.getRelated(WrappingOfBizDelegator.java:208)
com.atlassian.jira.scheme.AbstractSchemeManager.getEntities(AbstractSchemeManager.java:247)
com.atlassian.jira.workflow.DefaultWorkflowSchemeManager.getNonDefaultEntities(DefaultWorkflowSchemeManager.java:397)
com.atlassian.jira.bc.config.DefaultConstantsService$IsStatusVisible$1.getWorkflowNames(DefaultConstantsService.java:188)
com.atlassian.jira.bc.config.DefaultConstantsService$IsStatusVisible.hasMoreWorkflows(DefaultConstantsService.java:240)
com.atlassian.jira.bc.config.DefaultConstantsService$IsStatusVisible.findMoreStatuses(DefaultConstantsService.java:221)
com.atlassian.jira.bc.config.DefaultConstantsService$IsStatusVisible.apply(DefaultConstantsService.java:213)
com.atlassian.jira.bc.config.DefaultConstantsService.isStatusVisible(DefaultConstantsService.java:117)
com.atlassian.jira.bc.config.DefaultConstantsService.getStatusByNameIgnoreCase(DefaultConstantsService.java:89)
com.atlassian.jira.jql.resolver.StatusResolver.getConstant(StatusResolver.java:34)
com.atlassian.jira.jql.resolver.ConstantsNameResolver.getIdsFromName(ConstantsNameResolver.java:25)
com.atlassian.jira.jql.resolver.IssueConstantInfoResolver.getIndexedValues(IssueConstantInfoResolver.java:32)
com.atlassian.jira.jql.query.AbstractOperatorQueryFactory.getIndexValues(AbstractOperatorQueryFactory.java:54)
com.atlassian.jira.jql.query.AbstractEqualityQueryFactory.createQueryForMultipleValues(AbstractEqualityQueryFactory.java:46)
com.atlassian.jira.jql.query.GenericClauseQueryFactory.getQuery(GenericClauseQueryFactory.java:57)
com.atlassian.jira.jql.query.StatusClauseQueryFactory.getQuery(StatusClauseQueryFactory.java:30)
com.atlassian.jira.jql.query.ContextAwareQueryVisitor.visit(ContextAwareQueryVisitor.java:98)
com.atlassian.jira.jql.query.ContextAwareQueryVisitor.visit(ContextAwareQueryVisitor.java:27)
com.atlassian.query.clause.TerminalClauseImpl.accept(TerminalClauseImpl.java:143)
com.atlassian.jira.jql.query.ContextAwareQueryVisitor.visit(ContextAwareQueryVisitor.java:57)
com.atlassian.jira.jql.query.ContextAwareQueryVisitor.visit(ContextAwareQueryVisitor.java:27)
com.atlassian.query.clause.AndClause.accept(AndClause.java:28)
com.atlassian.jira.jql.query.QueryVisitor.createQuery(QueryVisitor.java:51)
com.atlassian.jira.jql.query.DefaultLuceneQueryBuilder.createLuceneQuery(DefaultLuceneQueryBuilder.java:29)
com.atlassian.jira.issue.search.providers.LuceneSearchProvider.createLuceneQuery(LuceneSearchProvider.java:343)
com.atlassian.jira.issue.search.providers.LuceneSearchProvider.getHitCount(LuceneSearchProvider.java:184)
com.atlassian.jira.issue.search.providers.LuceneSearchProvider.getHitCount(LuceneSearchProvider.java:148)
com.atlassian.jira.bc.issue.search.DefaultSearchService.searchCount(DefaultSearchService.java:130)Database Activity
Jira ends up executing a few thousand SQL select statements within one second, and half a million within one minute. This exact number will vary depending on the number of workflow schemes, JQL used, and user activity. This is the query in question:
SELECT ID, SCHEME, WORKFLOW, issuetype FROM public.workflowschemeentity WHERE SCHEME=?Cause
During the development of Jira 9.11/5.11, the way workflow statuses are loaded was changed, and this introduced search performance regression that affected many areas of the application.
Workaround
Jira Software - As of today, there is no workaround for this issue in Jira Software version 9.11.x.
Jira Service Management - The issue can be mitigated to a certain extent on the JSM Queue side by caching the Queue counts, but queues and search will still be slower then before. For details please refer to the following article: How to resolve slow queues view with high CPU in Jira Service Management - Solution 3 - Enable queue count caching. The results will vary depending on the Jira instance.
Solution
To permanently resolve the performance issue please upgrade your Jira Software to version 9.12.x or Jira Service Management to version 5.12.x.