Summary
The com.atlassian.servicedesk.internal.sla.audit.SlaAuditLogCleanupJobScheduler job was added as part of https://jira.atlassian.com/browse/JSDSERVER-5266 to clean up the data of tables AO_54307E_SLAAUDITLOGDATA and AO_54307E_SLAAUDITLOG as these tables can grow massive in size.
However, this job does a query like this :
prior to JSD 3.16.4:
select `AO_54307E_SLAAUDITLOG`.`ID`, `AO_54307E_SLAAUDITLOG`.`ISSUE_ID`, `AO_54307E_SLAAUDITLOG`.`SLA_ID` from `AO_54307E_SLAAUDITLOG` `AO_54307E_SLAAUDITLOG` where `AO_54307E_SLAAUDITLOG`.`ISSUE_ID` is null and `AO_54307E_SLAAUDITLOG`.`EVENT_TIME` < 1517364000008 or `AO_54307E_SLAAUDITLOG`.`ISSUE_ID` in (select `AO_54307E_SLAAUDITLOG`.`ISSUE_ID` from `AO_54307E_SLAAUDITLOG` `AO_54307E_SLAAUDITLOG` where `AO_54307E_SLAAUDITLOG`.`ISSUE_ID` is not null group by `AO_54307E_SLAAUDITLOG`.`ISSUE_ID` having max(`AO_54307E_SLAAUDITLOG`.`EVENT_TIME`) < 1517364000008);
or after JSD 3.16.4, 3.19.4, 4.3
select ID, ISSUE_ID, SLA_ID
from (
(select ID, ISSUE_ID, SLA_ID
from AO_54307E_SLAAUDITLOG
where ISSUE_ID in (
select ISSUE_ID, max(EVENT_TIME) as MAX_EVENT_TIME -- this is the DEPENDENT SUBQUERY
from AO_54307E_SLAAUDITLOG
group by ISSUE_ID
having max(EVENT_TIME) < now()))
UNION ALL
(select ID, ISSUE_ID, SLA_ID
from AO_54307E_SLAAUDITLOG
where ISSUE_ID is null
and EVENT_TIME < now())
) as t;
Which helped to improve performance of the Job for Postgres database based on SDSERVER-5719. However, neither of these queries perform well for MySQL. This queries runs for several hours in the database.
Environment
Service Desk instance with a few hundred thousand rows in both:
AO_54307E_SLAAUDITLOG
AO_54307E_SLAAUDITLOGDATA
Steps to Reproduce
Let the job com.atlassian.servicedesk.internal.sla.audit.SlaAuditLogCleanupJobScheduler run on the instance.
Expected Results
The job finishes in reasonable time.
Actual Results
The job can take several hours to finish on a MySQL database or may never complete at all leading to database errors and the Job failing.
Workaround
- We can try to make the tables lighter manually. That should help with the audit job finishing in reasonable time.
If you do not use the SLA Audit log feature, you may delete all rows from these tables AO_54307E_SLAAUDITLOGDATA and AO_54307E_SLAAUDITLOG.
- We can also make the SLA audit data get cleaned out sooner, preventing it from getting big enough to be problematic by applying the workaround in https://jira.atlassian.com/browse/JSDSERVER-5871
Bug
Highest
JSDSERVER-5871 As a Service Desk Admin I would like to be able to disable the SLA Audit Log so that I can improve performance and stability of Jira Service Desk Server
ITOPS-1714 Failed to load
JSMDC-2167 You do not have permission to view this issue
Page Loading...