Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-19701

Groups rest api call is very expensive on the DB CPU

    Details

    • Type: Hot Fix Request
    • Status: Closed
    • Resolution: Fixed
    • Affects Version/s: 5.2.3
    • Fix Version/s: 5.2.3.2, 5.2.5, 6.0.1
    • Component/s: ACS REST API
    • Labels:
    • Environment:
      AWS/Aurora(Mysql)

      Description

      HF MNT-19520 and MNT-19701 are for the same customer

      [Description]

      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

      http://localhost:8080/alfresco/api/-default-/public/alfresco/versions/1/people/sso4/groups

      [Expected Behaviour]

      The api call should not take long to return and the database CPU should not spike so high

      [Observed Behaviour]

      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

       

      select
      assoc.id as id,
      parentNode.id as parentNodeId,
      parentNode.version as parentNodeVersion,
      parentStore.protocol as parentNodeProtocol,
      parentStore.identifier as parentNodeIdentifier,
      parentNode.uuid as parentNodeUuid,
      childNode.id as childNodeId,
      childNode.version as childNodeVersion,
      childStore.protocol as childNodeProtocol,
      childStore.identifier as childNodeIdentifier,
      childNode.uuid as childNodeUuid,
      assoc.type_qname_id as type_qname_id,
      assoc.child_node_name_crc as child_node_name_crc,
      assoc.child_node_name as child_node_name,
      assoc.qname_ns_id as qname_ns_id,
      assoc.qname_localname as qname_localname,
      assoc.is_primary as is_primary,
      assoc.assoc_index as assoc_index
      
      from 
      (
      alf_child_assoc assoc 
      join alf_node parentNode on (parentNode.id = assoc.parent_node_id)
      join alf_store parentStore on (parentStore.id = parentNode.store_id)
      join alf_node childNode on (childNode.id = assoc.child_node_id)
      join alf_store childStore on (childStore.id = childNode.store_id)
      )
      LEFT OUTER JOIN 
      (
      alf_child_assoc a
      JOIN alf_child_assoc z2 ON (z2.parent_node_id = 487 AND z2.child_node_id = a.parent_node_id)
      )
      ON (a.child_node_id = childNode.id AND a.type_qname_id = 113)
      where
      parentNode.id = 487 and a.child_node_id IS NULL;

       

      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

       
      QA: db.r4.xlarge
       
      PROD: db.r4.8xlarge
       
       

      mysql> select count(*) from alf_child_assoc;
      ----------
      
      count(*)
      ----------
      
        1158623
      ----------
      1 row in set (0.21 sec)
       
      mysql> select count(*) from alf_node;
      ----------
      
      count(*)
      ----------
      
        1227026
      ----------
      1 row in set (0.80 sec)
       
      mysql> select count(*) from alf_store;
      ----------
      
      count(*)
      ----------
      
              6
      ----------
      1 row in set (0.01 sec)
       

      HF MNT-19520 and MNT-19701 are for the same customer

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  closedbugs Closed Bugs (Inactive)
                  Reporter:
                  jportillo Jose Portillo
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  8 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel