Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Fixed
Priority: High
Fix Version/s: 8.17.0, 8.16.2, 7.21.20, 8.9.9, 8.12.6, 8.13.5
Affects Version/s: 7.21.0, 7.21.6
Component/s: Integration - JIRA
Labels:
- dob-pr

Support reference count:
12
Symptom Severity:
Severity 2 - Major
UIS:
45
Bug Fix Policy:
View Atlassian Server bug fix policy

Description

Issue Summary

Large number of commits associated to a Jira issue key can cause high resource utilisation on the Bitbucket database.

The /rest/remote-link-aggregation/latest/aggregation endpoint in Bitbucket, which is called by Jira performs the following query:

select internalin0_.id as col_0_0_, internalin0_.author_timestamp as col_1_0_, repository2_.repository_id as col_2_0_
from changeset internalin0_ inner join
cs_attribute attributes1_ on internalin0_.id=attributes1_.cs_id and (attributes1_.att_name=@P0 and attributes1_.att_value=@P1)
inner join cs_repo_membership repository2_ on internalin0_.id=repository2_.cs_id
order by internalin0_.author_timestamp DESC, internalin0_.id ASC, repository2_.repository_id ASC

Calling this query for Jira issue keys with thousands of commits can cause high CPU utilisation on the Bitbucket DB.

This is reproducible on Data Center: yes

Steps to Reproduce

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

Expected Results

The query does not cause high resource utilisation.

Actual Results

The query causes high resource utilisation on the DB, causing resource issues.

As a result, Bitbucket becomes unavailable to service requests because HTTP threads get stuck waiting on DB connections.

Thread dumps show HTTP threads waiting on the DB with stack traces similar to the following:

"http-nio-127.0.0.1-11996-exec-10" #285 daemon prio=5 os_prio=0 cpu=178736.93ms elapsed=174569.45s tid=0x00002b86698f3800 nid=0x1d243 runnable  [0x00002b8692358000]
   java.lang.Thread.State: RUNNABLE
	at java.net.SocketInputStream.socketRead0(java.base@11.0.14.1/Native Method)
	at java.net.SocketInputStream.socketRead(java.base@11.0.14.1/SocketInputStream.java:115)
	at java.net.SocketInputStream.read(java.base@11.0.14.1/SocketInputStream.java:168)
	at java.net.SocketInputStream.read(java.base@11.0.14.1/SocketInputStream.java:140)
	at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1971)
	at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6369)
	- locked <0x000000072949dce8> (a com.microsoft.sqlserver.jdbc.TDSReader)
	at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7627)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:576)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
	- locked <0x000000072949de58> (a java.lang.Object)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:434)
	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:2297)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2050)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
	at org.hibernate.loader.Loader.scroll(Loader.java:2920)
	at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:576)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:452)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performScroll(HQLQueryPlan.java:353)
	at org.hibernate.internal.SessionImpl.scroll(SessionImpl.java:1556)
	at org.hibernate.query.internal.AbstractProducedQuery.doScroll(AbstractProducedQuery.java:1573)
	at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:1559)
	at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:115)
	at com.atlassian.stash.internal.hibernate.HibernatePageUtils.createScrollableFromQuery(HibernatePageUtils.java:55)
	at com.atlassian.stash.internal.content.HibernateIndexedCommitDao.scrollCommits(HibernateIndexedCommitDao.java:383)
	at com.atlassian.stash.internal.content.HibernateIndexedCommitDao.findByPropertyWithRepository(HibernateIndexedCommitDao.java:150)
	at jdk.internal.reflect.GeneratedMethodAccessor1573.invoke(Unknown Source)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.14.1/DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(java.base@11.0.14.1/Method.java:566)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
	at com.atlassian.stash.internal.aop.ProfilingAspect.profileMethod(ProfilingAspect.java:36)
	at jdk.internal.reflect.GeneratedMethodAccessor204.invoke(Unknown Source)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.14.1/DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(java.base@11.0.14.1/Method.java:566)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at com.sun.proxy.$Proxy345.findByPropertyWithRepository(Unknown Source)
	at com.atlassian.stash.internal.idx.DefaultCommitIndex.findByProperty(DefaultCommitIndex.java:125)
	at jdk.internal.reflect.GeneratedMethodAccessor1246.invoke(Unknown Source)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.14.1/DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(java.base@11.0.14.1/Method.java:566)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at com.sun.proxy.$Proxy346.findByProperty(Unknown Source)
	at jdk.internal.reflect.GeneratedMethodAccessor1246.invoke(Unknown Source)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.14.1/DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(java.base@11.0.14.1/Method.java:566)
	at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
	at com.sun.proxy.$Proxy532.findByProperty(Unknown Source)
	at jdk.internal.reflect.GeneratedMethodAccessor1246.invoke(Unknown Source)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.14.1/DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(java.base@11.0.14.1/Method.java:566)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
	at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
	at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at com.sun.proxy.$Proxy2013.findByProperty(Unknown Source)
	at com.atlassian.stash.internal.jira.summary.repo.RepositorySummaryService.getCommits(RepositorySummaryService.java:100)
	at com.atlassian.stash.internal.jira.summary.repo.RepositorySummaryService.lambda$mapCommitsByKey$6(RepositorySummaryService.java:107)
	at com.atlassian.stash.internal.jira.summary.repo.RepositorySummaryService$$Lambda$2829/0x0000000802825040.apply(Unknown Source)
	at java.util.stream.Collectors.lambda$uniqKeysMapAccumulator$1(java.base@11.0.14.1/Collectors.java:178)
	at java.util.stream.Collectors$$Lambda$671/0x000000080085f040.accept(java.base@11.0.14.1/Unknown Source)
	at java.util.stream.ReduceOps$3ReducingSink.accept(java.base@11.0.14.1/ReduceOps.java:169)
	at java.util.AbstractList$RandomAccessSpliterator.forEachRemaining(java.base@11.0.14.1/AbstractList.java:720)
	at java.util.stream.AbstractPipeline.copyInto(java.base@11.0.14.1/AbstractPipeline.java:484)
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(java.base@11.0.14.1/AbstractPipeline.java:474)
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(java.base@11.0.14.1/ReduceOps.java:913)
	at java.util.stream.AbstractPipeline.evaluate(java.base@11.0.14.1/AbstractPipeline.java:234)
	at java.util.stream.ReferencePipeline.collect(java.base@11.0.14.1/ReferencePipeline.java:578)
	at com.atlassian.stash.internal.jira.summary.repo.RepositorySummaryService.mapCommitsByKey(RepositorySummaryService.java:107)
	at com.atlassian.stash.internal.jira.summary.repo.RepositorySummaryService.getSummaries(RepositorySummaryService.java:80)
	at com.atlassian.stash.internal.jira.summary.DefaultSummaryService.getSummaries(DefaultSummaryService.java:66)
	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$2820/0x0000000802827840.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)
	at com.google.common.collect.Iterables$10.apply(Iterables.java:1026)
	at com.google.common.collect.Iterables$10.apply(Iterables.java:1023)
	at com.google.common.collect.Iterators$6.transform(Iterators.java:785)
	at com.google.common.collect.TransformedIterator.next(TransformedIterator.java:47)
	at com.google.common.collect.Iterators$ConcatenatedIterator.hasNext(Iterators.java:1332)
	at com.atlassian.linkaggregation.rest.RemoteLinkAggregatorResource.outputLinkAggregationToStream(RemoteLinkAggregatorResource.java:105)
	at com.atlassian.linkaggregation.rest.RemoteLinkAggregatorResource$1.write(RemoteLinkAggregatorResource.java:79)
	at com.atlassian.stash.internal.rest.filter.StreamingResponseFilter$ExceptionMappingStreamingOutput.lambda$write$0(StreamingResponseFilter.java:80)
	at com.atlassian.stash.internal.rest.filter.StreamingResponseFilter$ExceptionMappingStreamingOutput$$Lambda$2755/0x00000008027f9840.stream(Unknown Source)
	at com.atlassian.stash.internal.rest.filter.StreamingResponseFilter$AbstractStreamingExceptionMapper.stream(StreamingResponseFilter.java:50)
	at com.atlassian.stash.internal.rest.filter.StreamingResponseFilter$ExceptionMappingStreamingOutput.write(StreamingResponseFilter.java:80)
	at com.sun.jersey.core.impl.provider.entity.StreamingOutputProvider.writeTo(StreamingOutputProvider.java:71)
	at com.sun.jersey.core.impl.provider.entity.StreamingOutputProvider.writeTo(StreamingOutputProvider.java:57)
... <trimmed> ...
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1726)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	- locked <0x0000000737fedc78> (a org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(java.base@11.0.14.1/Thread.java:829)

Workaround

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;

Delete the Jira issues returned by the query.

Attachments

Issue Links

is related to

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

Closed

is caused by: BBSDEV-28149 Loading...

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(16 mentioned in)

Activity

People

Assignee:: Unassigned

Reporter:: JP Mariano

Votes:: 7 Vote for this issue

Watchers:: 19 Start watching this issue

Dates

Created:: 24/Mar/2023 3:01 AM

Updated:: 10/Apr/2024 11:27 PM

Resolved:: 18/Dec/2023 4:49 AM