-
Bug
-
Resolution: Fixed
-
Highest
-
8.12.0, 7.21.15, 8.9.4, 8.11.3, 8.9.5, 8.9.6, 8.9.17
-
14
-
Severity 2 - Major
-
125
-
Issue Summary
High number of pull requests associated to a Jira issue key can cause high resource utilisation on the DB.
The query identified as causing high CPU usage on the DB is:
select internalpu0_.id as col_0_0_, internalpu0_.scoped_id as col_1_0_, internalpu0_.from_branch_name as col_2_0_, internalpu0_.from_hash as col_2_1_, internalpu0_.from_branch_fqn as col_2_2_, internalpu0_.from_repository_id as col_2_3_, internalpu0_.to_branch_name as col_3_0_, internalpu0_.to_hash as col_3_1_, internalpu0_.to_branch_fqn as col_3_2_, internalpu0_.to_repository_id as col_3_3_, internalpu0_.pr_state as col_4_0_, internalpu0_.updated_timestamp as col_5_0_ from sta_pull_request internalpu0_ where internalpu0_.to_repository_id=$1 and (internalpu0_.scoped_id in ($2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 , $10 , $11 , $12 , $13 , $14 , $15 , $16 , $17 , $18 , $19 , $20 , $21 , $22 , $23 , $24 , $25 , $26 , $27 , $28 , $29 , $30 , $31 , $32 , $33 , $34 , $35 , $36 , $37 , $38 , $39 , $40 , $41 , $42 , $43 , $44 , $45 , $46 , $47 , $48 , $49 , $50 , $51 , $52 , $53 , $54 , $55 , $56 , $57 , $58 , $59 , $60 , $61 , $62 , $63 , $64 , $65 , $66 , $67 , $68 , $69 , $70 , $71 , $72 , $73 , $74 , $75 , $76 , $77 , $78 , $79 , $80 , $81 , $82 , $83 , $84 , $85 , $86 , $87 , $88 , $89 , $90 , $91 , $92 , $93 , $94 , $95 , $96 , $97 , $98 , $99 , $100 , $101)) order by internalpu0_.scoped_id asc limit $102
This query is called by the following endpoints:
- /rest/jira-dev/latest/detail/pullrequest - called by Jira when the "N pull requests" link in the Jira Development panel in clicked
- /rest/remote-link-aggregation/latest/aggregation - called by Jira when these actions are performed:
- a branch with a Jira issue key in its name is pushed
- a Jira issue key is added to the pull request title
- a commit containing a Jira issue key in the message is pushed
This is reproducible on Data Center: yes
Steps to Reproduce
- Connect Jira to Bitbucket by applink
- Create a Jira issue and make several thousand pull requests associated with the Jira issue key by including the Jira issue key in the pull request title
- Perform any of these actions:
- in Jira, open the issue key and click the "N pull requests" link in the Development panel
- Push a new commit with the Jira issue key in the commit message
- Create a new PR with the Jira issue key in the PR title
- Create a new branch with Jira issue key in the branch name
Expected Results
The query does not cause high resource utilisation.
Actual Results
The query causes high utilisation on the DB, causing performance issues in Bitbucket
Thread dumps show HTTP threads with stack traces similar to the following (in the sample below, the DB used is a PostgreSQL DB):
"http-nio-7990-exec-89 url: /rest/remote-link-aggregation/latest/aggregation" daemon prio=5 tid=0x0000000000001bbe nid=0 runnable java.lang.Thread.State: RUNNABLE at java.base@11.0.20/java.net.SocketInputStream.socketRead0(Native Method) at java.base@11.0.20/java.net.SocketInputStream.socketRead(SocketInputStream.java:115) at java.base@11.0.20/java.net.SocketInputStream.read(SocketInputStream.java:168) at java.base@11.0.20/java.net.SocketInputStream.read(SocketInputStream.java:140) at java.base@11.0.20/sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:484) at java.base@11.0.20/sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:478) at java.base@11.0.20/sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:70) at java.base@11.0.20/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1459) at java.base@11.0.20/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1070) at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113) at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:223) at org.postgresql.core.PGStream.receive(PGStream.java:658) at org.postgresql.core.PGStream.receiveTupleV3(PGStream.java:618) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2368) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) at org.hibernate.loader.Loader.doQuery(Loader.java:956) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357) at org.hibernate.loader.Loader.doList(Loader.java:2868) at org.hibernate.loader.Loader.doList(Loader.java:2850) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682) at org.hibernate.loader.Loader.list(Loader.java:2677) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1459) ... <trimmed> ... org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241) at com.sun.proxy.$Proxy2000.getSummaries(Unknown Source) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor.lambda$null$2(JiraIndexResultsProcessor.java:146) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor$$Lambda$4679/0x0000000842f36440.get(Unknown Source) at com.atlassian.bitbucket.util.PagedIterable.iterator(PagedIterable.java:27) at java.base@11.0.20/java.lang.Iterable.spliterator(Iterable.java:101) at com.atlassian.bitbucket.util.MoreStreams.streamIterable(MoreStreams.java:86) at com.atlassian.bitbucket.util.PageUtils.toStream(PageUtils.java:206) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor.lambda$getPullRequestsById$3(JiraIndexResultsProcessor.java:141) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor$$Lambda$4677/0x0000000842f6fc40.apply(Unknown Source) at java.base@11.0.20/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:271) at java.base@11.0.20/java.util.Iterator.forEachRemaining(Iterator.java:133) at java.base@11.0.20/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) at java.base@11.0.20/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) at java.base@11.0.20/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) at java.base@11.0.20/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) at java.base@11.0.20/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) at java.base@11.0.20/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor.getPullRequestsById(JiraIndexResultsProcessor.java:148) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor.lambda$onEnd$1(JiraIndexResultsProcessor.java:62) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor$$Lambda$4675/0x0000000842f6dc40.accept(Unknown Source) at java.base@11.0.20/java.util.Map.forEach(Map.java:661) at com.atlassian.stash.internal.jira.index.JiraIndexResultsProcessor.onEnd(JiraIndexResultsProcessor.java:60) at com.atlassian.stash.internal.jira.index.DefaultJiraIndexService.find(DefaultJiraIndexService.java:127) at com.atlassian.stash.internal.jira.summary.DefaultSummaryService.getSummaries(DefaultSummaryService.java:67) at com.atlassian.stash.internal.jira.rest.SummaryLinkAggregator.getSummaries(SummaryLinkAggregator.java:57) at com.atlassian.stash.internal.jira.rest.SummaryLinkAggregator.lambda$aggregateForGlobalIds$0(SummaryLinkAggregator.java:48) at com.atlassian.stash.internal.jira.rest.SummaryLinkAggregator$$Lambda$4674/0x000000084308c040.perform(Unknown Source) at com.atlassian.stash.internal.user.DefaultEscalatedSecurityContext.call(DefaultEscalatedSecurityContext.java:59) at com.atlassian.stash.internal.jira.rest.SummaryLinkAggregator.aggregateForGlobalIds(SummaryLinkAggregator.java:48) at com.atlassian.linkaggregation.impl.RemoteLinkAggregatorService.aggregateLinksByGlobalId(RemoteLinkAggregatorService.java:115) at com.atlassian.linkaggregation.impl.RemoteLinkAggregatorService.access$000(RemoteLinkAggregatorService.java:35) at com.atlassian.linkaggregation.impl.RemoteLinkAggregatorService$2.apply(RemoteLinkAggregatorService.java:96) at com.atlassian.linkaggregation.impl.RemoteLinkAggregatorService$2.apply(RemoteLinkAggregatorService.java:92) at com.atlassian.linkaggregation.impl.Iterables2$1$1.get(Iterables2.java:39) at com.atlassian.linkaggregation.impl.Iterables2$1$1.get(Iterables2.java:35) at com.atlassian.linkaggregation.impl.Iterables2$2.iterator(Iterables2.java:59) ...
Workaround
- Identify Jira issues with more than 1500 associated pull requests, using the SQL query below:
select "ISSUE", count(1) as ROW_COUNT from "AO_777666_JIRA_INDEX" where "PR_ID" is not null group by "ISSUE" having count(1) >= 1500 order by ROW_COUNT;
- Delete the Jira issues returned by the query
- mentioned in
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-