Querying a Jira issue with a large number of commits results in slow database performance

Issue Summary

When a Jira issue has thousands of associated commits, the /rest/remote-link-aggregation/latest/aggregation endpoint in Bitbucket Server runs thousands of queries.

The Jira integration queries Bitbucket for commits related to issue keys. The following queries are run:

select repository0_.cs_id as cs_id1_34_0_, repository0_.repository_id as reposito2_34_0_, repository0_.cs_id as cs_id1_34_1_, repository0_.repository_id as reposito2_34_1_ from cs_repo_membership repository0_ where repository0_.cs_id=? order by repository0_.repository_id

select internalin0_.id as id1_31_, internalin0_.author_timestamp as author_t2_31_ from changeset internalin0_ inner join cs_attribute attributes1_ on internalin0_.id=attributes1_.cs_id and (attributes1_.att_value=? and attributes1_.att_name=?) order by internalin0_.author_timestamp DESC limit ? offset ?

This results in the same number of queries to the cs_repo_membership table, and several hundred requests to changeset, resulting high database resource usage and the application eventually becoming unavailable.

Steps to Reproduce

1. Connect Jira to Bitbucket.
2. Create a Jira issue and make several thousand commits in Bitbucket associated with that issue key.
3. Monitor the requests made to /rest/remote-link-aggregation/latest/aggregation for that issue key and the associated SQL queries.

Expected Results

The SQL queries are all processed successfully.

Actual Results

These queries run for an extended period of time for tickets with a large number of commits, causing resource issues and Bitbucket to be unavailable.

Workaround

1. Identify any Jira issues with more than 1500 associated commits, using the SQL query below:

   select issue_key, commit_count from (select att_value as issue_key, count(1) as commit_count from cs_attribute where att_name = 'jira-key' group by att_name, att_value) as q where commit_count > 1500 order by commit_count desc;
   

2. Delete the Jira issues returned by the query.