Jira server slow and high cpu usage issue with a mysql slow query

Question

We create a vm on Azure to running Jira 7.13.16 since 2020.

It was a machine with 8 vCore and 24GB memory.

But Jira server was slow and high cpu usage recently.

After check, I found a mysql query will be run everyday and make Jira slowly:

# Time: 2021-08-10T07:34:28.775759Z
# Query_time: 194.309504 Lock_time: 0.000000 Rows_sent: 9854035 Rows_examined: 9854035
use jirasw;
SET timestamp=1628580868;
SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue;

# Time: 2021-08-11T07:35:16.970223Z
# Query_time: 232.061057 Lock_time: 0.000000 Rows_sent: 9909408 Rows_examined: 9909408
use jirasw;
SET timestamp=1628667316;
SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue;

# Time: 2021-08-12T07:34:14.720236Z
# Query_time: 187.891826 Lock_time: 0.000000 Rows_sent: 9958486 Rows_examined: 9958486
use jirasw;
SET timestamp=1628753654;
SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue;

How did you solve the problem? Can you please share?

Nic Brough -Adaptavist- · Answer

You should never interfere with a running SQL query on a Jira database.  You shouldn't ever really be touching the database at all.

Your idea to look at subscriptions is a good thought, but I'm afraid it's the wrong place.  Subscriptions are not the problem.

Even if someone subscribes to a filter that is just "" (select every issue in Jira), when the subscription executes, first it reads the index (not the database) for a list of issues, then it reads the first 50 items returned and turns them into the email, one at a time.  Even if a subscription were to read the entire database of custom fields, it would not do it with that SQL, you'd see it executing SQL for the value of the custom field for the current issue, once for each issue.

Have you confirmed that the xml backups have been disabled?

Simon Xu_uq13pd · Answer

I try to kill mysql slow query but helpless.

A lot of threads in Timed_waiting when Jira hangs up as below.

I will try to send it to support to check again.

"SdOffThreadEventJobRunner:thread-2979" #192229 prio=5 os_prio=0 tid=0x00007fb19c3a0000 nid=0x136b waiting on condition [0x00007fafb93eb000]
java.lang.Thread.State: TIMED_WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
- parking to wait for <0x00000004419a16d0> (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)

Simon Xu_uq13pd · Answer

Thanks, I believe someone export data by a filter subscription by daily. But I can't find out this filter since we have 2000 users and 1400+ filters in Jira.

Is it possible to disable this query by mysql or Jira?