Optimize the /rest/api/2/search endpoint

Problem Statement:

When using Jira's "search" API endpoint, api-clients are experiencing long delays while waiting on a response from Jira. As a result, the environment can experience a performance issue depending on the amount of API calls made and field selected to return in the JQL query.

Summary:

Administrators and developers are noticing that when using the search api endpoint in Jira, if there are too many custom fields being requested, or the filter is set to return all, then there could be a signification delay when trying to process the request and return results.

The worst of this has been approximately 10-second delays per API call. When this is multiplied by several external integrations then the environment suffers a performance hit as threads are left waiting on queries to complete and process between the app and database servers.

Tuning the JQL query used in the API Call, removing custom fields requested has been found to change the behavior drastically, but defeats the JQL requirements for the integration.

Relevant Details:

• Size the environment appears to be irrelevant and this issue occurs even on smaller systems

  ChangeItem: 12964 rows
  ChangeGroup: 11254 rows
  CustomFieldValue: 52592 rows
  

• When the delays start to occur, support finds errors like this in the catalina.out log:

   Thread [http-nio-8080-exec url: /rest/api/2/search; user: 
  <>] (id=[48452]) has been active for [126,294] milliseconds (since [4/3/24, 1:57 PM]) to serve the same request for [https://jira.base.url/rest/api/2/search?jql=filter=29808&startAt=1496&
  maxResults=187] and may be stuck (configured threshold for this StuckThreadDetectionValve is [120] seconds). There is/are [2] thread(s) in total that are monitored by this Valve and may be stuck
  

• This seems to be directly related to the query used by Jira while the api client refereces the search endpoint.
  Query pulled from atlassian-jira-sql.log

  "SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE='32339'"http-nio-8080-exec /rest/api/2/search

• Delays on average, based on customfields, around around 10-seconds. Determining the most-impacted custom field, then removing it from the query, can bring this down to about 3 seconds.

• Users do not see the same delay when using the same JQL query in Jira's Issue Search page. This only impacts the API endpoint.

Suggestion:

Please work to optimize the Search API endpoint, /rest/api/latest/search, so that results are returned in an appropriate timeframe and therefore the performance impact to Jira is minimized.