Uploaded image for project: 'Bitbucket Data Center'
  1. Bitbucket Data Center
  2. BSERV-13725

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

XMLWordPrintable

      Update

      In versions 7.21.23, 8.9.11+, 8.14.6, 8.15.5, 8.16.4, 8.17.2, 8.18.1, 8.19.0+, the mssql.cs_attribute.index.clustered.force property defaults to false. This property is only relevant to MSSQL databases, and controls whether the clustered index (that was added to the cs_attribute table for MSSQL DBs) is used for a certain query in HibernateIndexedCommitDao

      A customer may use the suitable property for their Bitbucket version if:

      1. The customer is using MSSQL as their database, and
      2. There is some indication that this query is the cause of a problem, such as:
        • The profile logs are showing the remote link aggregation endpoint is sometimes very slow for specific Jira keys (/rest/remote-link-aggregation/latest/aggregation)
        • The profiling logs are showing calls to HibernateIndexedCommitDao are slow

      When to use the clustered index:

      The database is reporting this query as being long-running:

      select * from changeset cs inner join cs_attribute att on cs.id=att.cs_id and (att.att_value = ? and att.att_name = ? ) ORDER BY cs.author_timestamp DESC offset 0 rows fetch next ? rows only

      To opt in to using the clustered index, set the property to true:

      mssql.cs_attribute.index.clustered.force=true

      When not to use the clustered index:

      The database is reporting this query as being long-running:

      select * from changeset cs inner join cs_attribute att with(index(0)) on cs.id=att.cs_id and (att.att_value = ? and att.att_name = ? ) ORDER BY cs.author_timestamp DESC offset 0 rows fetch next ? rows only

       

      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

      1. Connect Jira to Bitbucket by applink
      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 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

      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;
      1. Delete the Jira issues returned by the query.

              Unassigned Unassigned
              jmariano@atlassian.com JP Mariano
              Votes:
              7 Vote for this issue
              Watchers:
              23 Start watching this issue

                Created:
                Updated:
                Resolved: