Medium Issue Summary
When Jira is under high load from user actions (issue edit, comment, etc), rows are inserted into the AO_319474_MESSAGE table.
If the rate of message consumption is less than the rate of messages being added, this table grows to a very large size.
When queue's are accessed, JSM issues a count
query against the AO_319474_MESSAGE table that can be expensive depending on the database.
Steps to Reproduce
- Set dark feature sd.internal.base.db.backed.completion.events.enabled
- Place a high load into Jira, such that the MESSAGE table grows very large (for instance, 800K rows)
- Observe select * from pg_stat_activity and note the high load against the database from the statement below.
Expected Results
This bottleneck is not present.
Actual Results
The following query is highly active on the database:
select count(*) from "public"."AO_319474_MESSAGE" "AO_319474_MESSAGE" where "AO_319474_MESSAGE"."QUEUE_ID" = ?
and ("AO_319474_MESSAGE"."CLAIMANT" is null or "AO_319474_MESSAGE"."CLAIMANT" is not null
and ("AO_319474_MESSAGE"."CLAIMANT_TIME" is null or "AO_319474_MESSAGE"."CLAIMANT_TIME" < ?)
Resource contention can occur and affect the performance of Jira.
Workaround
Disable off-thread functionality by setting:
sd.internal.bounded.off.thread.on.completion.events.disabled sd.internal.base.off.thread.on.completion.events.disabled
