Change audit query SQL to improve performance
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
When querying audit records by date range request do not result in http 500 errors
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.
rds postgres, tomcat, aws
Assigning back to support as triage and solution is proposed
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.
This ticket is a Must-Have for ACS 7.0 and may be superseded by the work being done on