.Performance issues with Audit Log
Problem
Getting some performance issues while browsing the Audit Log page, either slowness or even unresponsiveness of the browser.
Symptoms
Slowness of Audit Log or unresponsiveness of the browser while checking the Summary filter.
Having similar WARN messages in the Atlassian log file:
- [c.a.a.ao.dao.AoCachedActionDao] Detected 623099 audit summaries (AKA actions) which is greater than the limit of 1000 this may lead to general performance degradation as audit events are saved and/or while loading the UI.
Cause
The implementation of the adding the data in the Audit Log for the app versions older than 2.6.0 was done in a way that writes the details under 'Summary', which was not optimum. Below is what a record in the Audit Log looked like:
This has led to the behaviour explained above.
Starting from version 2.6.0, we did a refactor to the Audit Log service (see the release notes here), such that the details are added to the correct place where they should be, like the following example:
Solution
Even if you have an older version than 2.6.0, then upgraded to the latest version, the old summaries would still be in the database.
To fix the issue, you need to delete the old summaries from the database table after upgrading the plugin.
Since the fix requires database operations, to be safe, we recommend doing that in a downtime window, although it’s highly unlikely that deleting these records in a running system causes any issues.
The following query reveals all records that our app has created in the database table and only the ones that the old version of the app created:
- SELECT count(*) FROM "AO_C77861_AUDIT_ACTION_CACHE" WHERE "ACTION" LIKE 'Found a matching token%' OR "ACTION" LIKE 'A user provided%' OR "ACTION" LIKE 'Token verification for user%' OR "ACTION" LIKE 'Created token with%' OR "ACTION" LIKE 'Deleted token with%' OR "ACTION" LIKE '%is authenticating with an invalid token%' OR "ACTION" LIKE 'An unknown user provided an invalid%';
The value of the count(*) is expected to be very high.
Using LIKE in a table with many records can impact database performance in the time the query runs, that’s why it’s also preferred to do that outside business hours.
Then you could finally use the same WHERE clause to delete these old records:
- DELETE FROM "AO_C77861_AUDIT_ACTION_CACHE" WHERE "ACTION" LIKE 'Found a matching token%' OR "ACTION" LIKE 'A user provided%' OR "ACTION" LIKE 'Token verification for user%' OR "ACTION" LIKE 'Created token with%' OR "ACTION" LIKE 'Deleted token with%' OR "ACTION" LIKE '%is authenticating with an invalid token%' OR "ACTION" LIKE 'An unknown user provided an invalid%';