-
Bug
-
Resolution: Fixed
-
Highest
-
3.8.3
-
26
-
Severity 2 - Major
-
162
-
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 :
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);
This query runs for several hours in the database.
Environment
In this example instance that I analysed the tables have the following number of rows :
AO_54307E_SLAAUDITLOG : 217.266
AO_54307E_SLAAUDITLOGDATA : 517.309
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 up to 10+ hours to finish.
# Query_time: 37781.640747 Lock_time: 0.000276 Rows_sent: 2501 Rows_examined: 45262655244 use jira; SET timestamp=1519993781; 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);
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
- causes
-
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
- Closed
-
ITOPS-1714 Failed to load
- was cloned as
-
-
- Closed
-
- is cloned by
-
JSMDC-2167 You do not have permission to view this issue
- mentioned in
-
Page Failed to load
-
Page Loading...
-
-
-
-
-
-
-
-
- relates to
-
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|