Problem

Jira is slow because of high DB CPU consumption.

Diagnosis

• Thread dumps show a variety of threads on the database, which makes it difficult to pinpoint which is primarily causing DB performance issues.
• The database is being hammered by SQL queries on the issuelink table.
• From the database side, top queries with high DB CPU consumption are on the issuelink table that look like:

SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM <schema>.issuelink WHERE DESTINATION=<number>

• This matches many entries in the SQL dump log.

% grep "SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM public.issuelink WHERE DESTINATION" atlassian-jira-sql.log*
atlassian-jira-sql.log.1:2024-09-20 10:01:25,460+1000 pool-xxx-thread-x admin <session> /rest/migration/latest/check/<string> 13ms "SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM public.issuelink WHERE DESTINATION='<number'"
atlassian-jira-sql.log.1:2024-09-20 10:01:25,692+1000 pool-xxx-thread-x admin <session> /rest/migration/latest/check/<string> 1ms "SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM public.issuelink WHERE DESTINATION='<number'"
atlassian-jira-sql.log.1:2024-09-20 10:01:25,743+1000 pool-xxx-thread-x admin <session> /rest/migration/latest/check/<string> 1ms "SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM public.issuelink WHERE DESTINATION='<number'"
...

• JCMA assessment is running when the slowness occurs.

Cause

• JCMA assessments are queries and checks that are run in the early stages of migration as part of the Assess/Plan phase.
• JCMA Preflight checks are geared more towards just before the start of the migration with more specific contexts being checked.
• There is a dark feature called com.atlassian.jira.migration.enable.relations.analysis.during.instance.assessment which does further checks on issue relations which means it does repeated heavy queries on the issuelink table.
• If both assessments and Preflight checks are running while the dark feature flag com.atlassian.jira.migration.enable.relations.analysis.during.instance.assessment is enabled, this can cause clogging on the issuelink table.

Solution

• If the instance cannot handle the simultaneous existence of the three conditions mentioned in the cause, disable the dark feature flag com.atlassian.jira.migration.enable.relations.analysis.during.instance.assessment to reduce the load and avoid locks on the database issuelinks table.