Summary

Database locking in two tables AO_319474_QUEUE and AO_319474_MESSAGE that occur multiple times in a day.  

Environment

• JSM DC 8.20.14
• MySQL 5.x

Diagnosis

Slow symptoms:

• High response time on peak hours.
• Jira data center and database server resources are not receiving any high CPU or Memory usage. 

• Run the next query to validate if there's any deadlock transaction in the tables AO_319474_QUEUE and AO_319474_MESSAGE 

  select Q."NAME", Q."MESSAGE_COUNT", count(M."ID") as real_message_count from "AO_319474_QUEUE" as Q left join "AO_319474_MESSAGE" as  M  on M."QUEUE_ID" = q."ID"  group by Q."NAME", Q."MESSAGE_COUNT" having count(M."ID") = 0 AND Q."MESSAGE_COUNT" != 0;

• In the MySQL database, please run the following SQL query to validate if there are any locks entries showing up in the innodb_lock_waits table when Jira is running slow: 

  SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

  Results (returned 100+ records): 

  SELECT   waiting_trx_id,   waiting_pid,   waiting_query,   blocking_trx_id,   blocking_pid,   blocking_query FROM sys.innodb_lock_waits;
  +----------------+-------------+-------------------------------------------------------------------+-----------------+--------------+-------------------------------------------------------------------+
  | waiting_trx_id | waiting_pid | waiting_query                                                     | blocking_trx_id | blocking_pid | blocking_query                                                    |
  +----------------+-------------+-------------------------------------------------------------------+-----------------+--------------+-------------------------------------------------------------------+
  | 182664579176   |      180926 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355597) | 182664579167    |       179776 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355144) |
  | 182664579176   |      180926 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355597) | 182664579166    |       180406 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355143) |
  | 182664579176   |      180926 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355597) | 182664579153    |       181066 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713354687) |
  | 182664579176   |      180926 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355597) | 182664579151    |       180500 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713354686) |
  | 182664579176   |      180926 | update `AO_319474_QUEUE` set ` ... AIMANT_TIME` >= 1669713355597) | 182664579126    |       180852 | select `AO_319474_MESSAGE`.`CL ... 9474_MESSAGE`.`ID` asc limit 1 |

Cause

Cause 1: A known deadlock bug from JSDSERVER-11745 - Getting issue details... STATUS

Cause 2: On the MySQL server InnoDB thread concurrency (innodb_thread_concurrency) is not set to indefinite value via my.cnf file.

Solution

Solution 1: For the deadlock bug from JSDSERVER-11745 - Getting issue details... STATUS , please fix it with the workaround: 

update "AO_319474_QUEUE" set "MESSAGE_COUNT" = 0 where "NAME" in (select Q."NAME" from "AO_319474_QUEUE" as Q left join "AO_319474_MESSAGE" as  M  on M."QUEUE_ID" = q."ID"  group by Q."NAME", Q."MESSAGE_COUNT" having count(M."ID") = 0 AND Q."MESSAGE_COUNT" != 0);

Solution 2: Set innodb_thread_concurrency=0 via my.cnf file.

There isn't a recommended prescribed value per instance. Based on our understanding, set thread concurrency to 0 would use indefinite concurrency without limitation from the DB server. Please check with your DBA if it's needed to set equal to the number of CPUs available or zero.