Uploaded image for project: 'Jira Software Cloud'
  1. Jira Software Cloud
  2. JSWCLOUD-25753

JQL search for Epic issue type times out for large datasets

    XMLWordPrintable

Details

    Description

      Issue Summary

      When performing a JQL search that includes Epic issue types, the backend SQL query may time out for larger systems. This is problem is futher impacted by the following:

      • Loading an Agile board's backlog will automatically perform a search for Epic issue types.
      • Team-managed projects duplicate the issue types in Jira, as JSW instances grow with team-managed projects, there will be more and more Epic issue types.

      Steps to Reproduce

      1. Have a large Jira instance with many projects and a large number of issue types named "Epic" in the backend database.
        • Example is if a Jira instance has many team-managed projects, each having their own "Epic" issue type.
      2. Create a JQL that may have a broad scope that searches for Epic issue types
        • Example: 
          project = TEST OR labels IN ("test") AND issuetype = Epic

      Expected Results

      Jira performs a search and returns the results.

      Actual Results

      Jira fails with the front end error:

      An unknown error occurred while trying to perform a search.

      The below exception is thrown in Splunk:

      Caught PSQLException 
  ...
  ...

	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:508)
	at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:410)
	at com.querydsl.sql.AbstractSQLQuery.iterate(AbstractSQLQuery.java:342)
	at com.atlassian.jira.jql.query.QueryCallbackSupplier.lambda$getQueryCallback$2(QueryCallbackSupplier.java:124)
	at com.atlassian.jira.database.QueryCallback.lambda$map$0(QueryCallback.java:28)
	at com.atlassian.jira.database.ConnectionUtils.lambda$withTimeout$0(ConnectionUtils.java:74)
	at com.atlassian.jira.database.ConnectionTimeoutUtils.withStatementTimeout(ConnectionTimeoutUtils.java:60)
	at com.atlassian.jira.database.ConnectionUtils.lambda$withTimeout$1(ConnectionUtils.java:74)
	at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:72)
	at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:83)
	at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:71)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.lambda$getCloseableIteratorCallable$19(VDbSearchExecutor.java:704)
	at com.atlassian.jira.instrumentation.jdbc.DefaultSqlMetricCollector.withObserver(DefaultSqlMetricCollector.java:516)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.lambda$runQueryInternal$18(VDbSearchExecutor.java:663)
	at com.atlassian.workcontext.metrics.Timer.timeWithException(Timer.java:216)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.runQueryInternal(VDbSearchExecutor.java:650)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.runQuery(VDbSearchExecutor.java:643)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.runFullDBSearchInternal(VDbSearchExecutor.java:448)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.runFullDBSearch(VDbSearchExecutor.java:432)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.lambda$runCollection$10(VDbSearchExecutor.java:422)
	at com.atlassian.jira.instrumentation.jdbc.DefaultSqlMetricCollector.withObserverThrowingException(DefaultSqlMetricCollector.java:526)
	at com.atlassian.jira.instrumentation.jdbc.metric.DatabaseScaleAttributerImpl$InternalDatabaseScaleAttributer.attributeDatabaseTime(DatabaseScaleAttributerImpl.java:86)
	at com.atlassian.jira.instrumentation.jdbc.metric.DatabaseScaleAttributerImpl$InternalDatabaseScaleAttributer.forCodeThrowingException(DatabaseScaleAttributerImpl.java:70)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.runCollection(VDbSearchExecutor.java:422)
	at com.atlassian.jira.issue.search.vsearch.VDbSearchExecutor.collect(VDbSearchExecutor.java:183)
	at com.atlassian.jira.issue.search.vsearch.LoggingSearchProvider.lambda$collect$6(LoggingSearchProvider.java:168)
	at com.atlassian.jira.util.ThrowableFunctions.lambda$uncheckSupplier$6(ThrowableFunctions.java:144)
	at com.atlassian.jira.issue.search.vsearch.LoggingSearchProvider.runSearch(LoggingSearchProvider.java:191)
	at com.atlassian.jira.issue.search.vsearch.LoggingSearchProvider.collect(LoggingSearchProvider.java:170)
	at com.atlassian.jira.issue.search.vsearch.IssueCollectBuilderImpl$MappedIssueCollectImpl.execute(IssueCollectBuilderImpl.java:382)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.lambda$getEpicsForBoardImpl_old$8(EpicIssueServiceImpl.java:238)
  ...
	at com.atlassian.greenhopper.service.util.ServiceOutcomeHelper.toServiceOutcome(ServiceOutcomeHelper.java:53)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.getEpicsForBoardImpl_old(EpicIssueServiceImpl.java:225)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.lambda$getEpicsForBoard$6(EpicIssueServiceImpl.java:216)
	at com.atlassian.jira.instrumentation.jdbc.DefaultSqlMetricCollector.withObserver(DefaultSqlMetricCollector.java:516)
	at com.atlassian.jira.instrumentation.jdbc.metric.DatabaseScaleAttributerImpl$InternalDatabaseScaleAttributer.attributeDatabaseTime(DatabaseScaleAttributerImpl.java:76)
	at com.atlassian.jira.instrumentation.jdbc.metric.DatabaseScaleAttributerImpl$InternalDatabaseScaleAttributer.forCode(DatabaseScaleAttributerImpl.java:65)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.getEpicsForBoard(EpicIssueServiceImpl.java:213)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.lambda$getEpicList$31(EpicIssueServiceImpl.java:404)
	at com.atlassian.workcontext.metrics.Timer.time(Timer.java:260)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.lambda$getEpicList$32(EpicIssueServiceImpl.java:403)
	at com.atlassian.greenhopper.service.util.ServiceOutcomeHelper.toServiceOutcome(ServiceOutcomeHelper.java:53)
	at com.atlassian.greenhopper.service.issue.EpicIssueServiceImpl.getEpicList(EpicIssueServiceImpl.java:403)
	at com.atlassian.greenhopper.web.rapid.plan.PlanningModeHelper.loadEpicList(PlanningModeHelper.java:157)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeFacadeV2.lambda$baseLoadBacklogDataV2$4(PlanningModeFacadeV2.java:89)
	at com.atlassian.workcontext.metrics.Timer.timeWithException(Timer.java:246)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeFacadeV2.baseLoadBacklogDataV2(PlanningModeFacadeV2.java:88)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeFacadeV2.lambda$loadBacklogDataV2$0(PlanningModeFacadeV2.java:73)
	at com.atlassian.greenhopper.service.util.ServiceOutcomeHelper.toServiceOutcome(ServiceOutcomeHelper.java:53)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeFacadeV2.loadBacklogDataV2(PlanningModeFacadeV2.java:73)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeFacadeV2.getPlanningData(PlanningModeFacadeV2.java:140)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeResourceV2.getBacklog(PlanningModeResourceV2.java:131)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeResourceV2.lambda$getBacklogWithExecutionTimer$3(PlanningModeResourceV2.java:116)
  ...
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeResourceV2.getBacklogWithExecutionTimer(PlanningModeResourceV2.java:115)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeResourceV2.lambda$getBacklogDataV2$1(PlanningModeResourceV2.java:109)
	at com.atlassian.greenhopper.web.util.RestCall.response(RestCall.java:42)
	at com.atlassian.greenhopper.web.AbstractResource.createResponse(AbstractResource.java:172)
	at com.atlassian.greenhopper.web.AbstractResource.lambda$response$1(AbstractResource.java:133)
	at com.atlassian.workcontext.metrics.Timer.timeWithException(Timer.java:216)

	at com.atlassian.greenhopper.web.AbstractResource.response(AbstractResource.java:122)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeResourceV2.lambda$getBacklogDataV2$2(PlanningModeResourceV2.java:109)
	at com.atlassian.workcontext.metrics.Timer.time(Timer.java:260)
	at com.atlassian.greenhopper.web.rapid.plan.v2.PlanningModeResourceV2.getBacklogDataV2(PlanningModeResourceV2.java:108)
	at jdk.internal.reflect.GeneratedMethodAccessor3840.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
  ...
  
Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
  ...
      • Specifically notice EpicIssueServiceImpl.java being called and leading to db search.

      Workaround

      • Narrow down the scope of the filter, avoid using "OR"

       

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. JSWCLOUD-25955 Epic Panel fails to load when board JQL has a large number of issues

          • High - High priority issues
          • Closed
          mentioned in

          Page Loading...

          Activity

            People

              Unassigned Unassigned
              dchan David Chan
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: