-
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 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
[JSDSERVER-5719] com.atlassian.servicedesk.internal.sla.audit.SlaAuditLogCleanupJobScheduler job running for several hours
I'm waiting for the 3.16.4 then!dastin.kuwalek1208141590 it will be released with Jira Service Desk 3.16.4 which corresponds to Jira 7.13.4.
Is this fix going to be released as part of 7.13.X? This is an Enterprise release which I have recommended and installed for one of my customers and this bug is bugging this customers Oracle DB admins.
Even worse on our instance.
31 hours:
Query_time: 112205.372680  Lock_time: 0.000112 Rows_sent: 8360  Rows_examined: 99012974631 SET timestamp=1525367569; 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` < 1522663363805 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`) < 1522663363805);
22 hours:
Query_time: 80174.489139 Lock_time: 0.000118 Rows_sent: 10187 Rows_examined: 72649805535 SET timestamp=1525508295; 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` < 1522836120550 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`) < 1522836120550);
The workaround will need Jira downtime as it is a direct database table cleanup and we cannot be scheduling downtime for this useless feature. Can you please bump the priority fix it asap?
Could anyone recommend where we can find the 7.13.4 release? I didn't find release notes for it and installation package.