Type: Hot Fix Request
Affects Version/s: 5.2.3
Component/s: ACS REST API
When using the public REST api call (https://api-explorer.alfresco.com/api-explorer/#!/groups/listGroupMembershipsForPerson) to get user's groups a premier customer has noticed that the call consumes excessive amount of CPU for a single call.
[Steps to reproduce]
This cannot be reproduced on a new system, it appears to have a relation with the amount of data in the repository. Currently when the customer executes the call
The api call should not take long to return and the database CPU should not spike so high
The call returns in about 2.5 mins and the database CPU spikes to 100%.
[Analysis to date]
The issue was discovered when testing the desktop sync client 1.1. The client makes use of this API call, however, we disabled desktop sync access and we have done a controlled test by making a single call to the groups API via a browser and we can see that a single call spikes the database CPU. We have also used p6spy to capture the query that gets sent to the db and it is something similar to the query below
This query is sent multiple times on a single groups API call which is leading the DB CPU to stay high for a long time. We have also tested executing this query directly bypassing alfresco and we can also see the single query spike the DB CPU to about 30% and the query runs in the QA environment for about 2.5 mins before it returns results. The DB instance used in AWS is