High database CPU load while DVCS task is running in Jira

Summary

A massive spike on the database CPU load based on queries against the moved_issue_key table is observed periodically:

Environment

Jira 8.4 and above, until 8.11.0

Fixed in 8.5.6, 8.9.2 and 8.10.1

Diagnosis

• SQL trace enabled showed that most of the requests to moved_issue_key table were done by the Scheduler task Caesium;

• Thread dumps showed Caesium working at com.atlassian.jira.plugin.devstatus.provider.IssueDataRequest.createRequest

  1 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 "Caesium-1-4" #442 daemon prio=5 os_prio=0 tid=0x00007fef0815d800 nid=0x1b93 runnable [0x00007fef0ccf3000] java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) at java.net.SocketInputStream.read(SocketInputStream.java:171) at java.net.SocketInputStream.read(SocketInputStream.java:141) ... at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109) at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67) at org.postgresql.core.PGStream.receiveChar(PGStream.java:321) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1978) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309) ... at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:527) at org.ofbiz.core.entity.GenericDAO.createEntityListIterator(GenericDAO.java:877) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:857) at org.ofbiz.core.entity.GenericDAO.selectByCondition(GenericDAO.java:795) at org.ofbiz.core.entity.GenericDAO.selectByCondition(GenericDAO.java:775) at org.ofbiz.core.entity.GenericHelperDAO.findByCondition(GenericHelperDAO.java:198) at org.ofbiz.core.entity.GenericDelegator.findByCondition(GenericDelegator.java:1091) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByCondition(DefaultOfBizDelegator.java:154) at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findByCondition(WrappingOfBizDelegator.java:240) at com.atlassian.jira.issue.managers.DefaultIssueManager.getPreviousIssueKeysForMovedIssues(DefaultIssueManager.java:453) at com.atlassian.jira.issue.managers.DefaultIssueManager.getAllIssueKeys(DefaultIssueManager.java:439) at com.atlassian.jira.issue.managers.RequestCachingIssueManager.getAllIssueKeys(RequestCachingIssueManager.java:175) ... at com.atlassian.jira.plugin.devstatus.provider.IssueDataRequest.createRequest(IssueDataRequest.java:40) at com.atlassian.jira.plugin.devstatus.provider.DefaultCachingProviderHelper.refresh(DefaultCachingProviderHelper.java:104) at com.atlassian.jira.plugin.devstatus.provider.DefaultCachingProviderHelper.refreshProvider(DefaultCachingProviderHelper.java:68) at com.atlassian.jira.plugin.devstatus.provider.DefaultDevSummaryPollService.handlePollingSuccess(DefaultDevSummaryPollService.java:71) at com.atlassian.jira.plugin.devstatus.provider.DefaultDevSummaryPollService.lambda$performPull$1(DefaultDevSummaryPollService.java:52) at com.atlassian.jira.plugin.devstatus.provider.DefaultDevSummaryPollService$$Lambda$11148/932905286.apply(Unknown Source) at com.atlassian.jira.plugin.devstatus.provider.source.applink.PollResult$PollResultSuccess.fold(PollResult.java:51) at com.atlassian.jira.plugin.devstatus.provider.DefaultDevSummaryPollService.performPull(DefaultDevSummaryPollService.java:50) at com.atlassian.jira.plugin.devstatus.provider.DevSummaryUpdateJob$$Lambda$11014/704142687.accept(Unknown Source) ... at com.atlassian.jira.plugin.devstatus.provider.DevSummaryUpdateJob.runJob(DevSummaryUpdateJob.java:85) at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134) at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106) at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:435) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:430) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:454) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:382) at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$$Lambda$2517/1725784422.accept(Unknown Source) at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66) at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60) at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35) at java.lang.Thread.run(Thread.java:748)

Cause

DVCS Scheduler task is running the task com.atlassian.jira.plugin.devstatus.provider.DevSummaryUpdateJob called "Guaranteed Delivery". Every 5 minutes it goes to Bitbucket Server/Bamboo/FeCru and tries to poll updates, in case Jira missed any events.

Whenever Jira misses enough issue updates from dev.tools so they don't fit into single fetch that Guaranteed Deliver does, it'll get stuck looping over results over and over.

Solution

Upgrade to a version of Jira with the fix: 8.5.6, 8.9.2, 8.10.1 or above. JSWSERVER-20612 - Dev.Status can get stuck paging over issue updates from Bitbucket Server, Bamboo or Fecru and cause excessive load on database

If unable to upgrade, apply the workarounds below. They won't avoid the select loop but will lessen it's impact on the database CPU:

Adding Index

Add an index to the moved_issue_table:

1. Stop Jira

2. Apply the index (Postgres example below):

   1 CREATE INDEX idx_issue_id ON moved_issue_key (issue_id);

3.  Start Jira

A request to bundle this index natively in Jira is tracked in JRASERVER-70678 - As a Jira Admin, I would like to add an index by ISSUE_ID column on the table moved_issue_key.

Add JVM parameter

It's also possible to add the below parameter to the JVM with a value of 500 or higher:

To add such parameters, you can follow this guide on Setting properties and options on startup.

This workaround is also documented on JSWSERVER-20612 - Dev.Status can get stuck paging over issue updates from Bitbucket Server, Bamboo or Fecru and cause excessive load on database.