Change audit query SQL to improve performance

Description

[Description]

When querying the audit tables using the alfresco API it can be seen that a query runs for a long time on the DB side which can max out DB resources.

[Steps to reproduce]

This has been reproduced at a customer's environment which has millions of entries in the audit tables, the customer is in the process of cleaning these out and they found this issue when trying to query audit records by date range

[Expected Behaviour]

When querying audit records by date range request do not result in http 500 errors 

[Observed Behaviour]

When querying audit records by date range requests can return http 500 errors due to waiting on DB to return.

[Analysis to date]

The DBA found the problematic query which belongs to the alfresco SQL template 

 

 

These queries take about 1-3 minutes to run, after tuning the query the new template looks like this:

 

The customer would like to have this embedded in the product.

 

Environment

rds postgres, tomcat, aws

Testcase ID

None

Activity

Show:
Marina Oliveira
February 12, 2021, 8:37 AM

Assigning back to support as triage and solution is proposed

Alan Davis
January 8, 2021, 5:15 PM

I don't think this issue is directly related to PRODMAN-341, which is about trying to reduce the overall size of audit data in the database.

It looks more closely related to and the MNT-21083. These two issues resulted in changing the way we cleaned up data in tables AFTER audit entries were deleted. The new approach was disabled by default in 5.2 (the version this customer is on) and only became the default in a later version, to avoid other customers suddenly having clean up jobs running if they took a 5.2 SP or HF with it in. Both customers had larger numbers of audit entries. The performance on the query side only improved after the old entries were removed and the new clean up script was run.

The query from the SQL template was never intended to scan millions of rows. However the investigation done by the customer may allow us to improve on what is there. Care would need to be taken to ensure that it would work for all our supported databases.

Steve Blair
January 6, 2021, 9:11 AM
Edited

This ticket is a Must-Have for ACS 7.0 and may be superseded by the work being done on

Won't Fix

Assignee

Steve Blair

Reporter

Jose Portillo

Labels

None

Escalated By

None

Security Issue

None

ACT Numbers

01017503

Premier Customer

Yes

Code Branch

None

Build Location

None

Regression Since

None

Work Funnel End

None

Patch Attached

None

Dependent Version/s

None

Cloud or Enterprise

None

Prioritization Score

None

Delivery Team

Team 5

Bug Priority

Category 2

Fix versions

Affects versions