Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-15623

DS Repo performance issues with large number of role members

    Details

    • Target Version/s:
    • Support Ticket IDs:

      Description

      We have a customer testing IDM 6.5.0.3 with DS as repo. They are testing roles with a large number of users (up to 40k users in a role - for multiple roles) - and although this approach is questionable from a solution perspective - the performance of the underlying DS as opposed to using PostgresSQL seems to be dramatically different.

      Testing with 4 roles each with 40k members - it seems to be at least 10x less performant at this level and would probably degrade further with more entries in the relationship OU (which in my test env has approximately 400k entries). The issue seems to be that once a role has greater than the index entry limit (4k) members, it will result in an unindexed search on the relationship table and depending on the number of entries in the relationship table - longer and longer response times.

       

      When using DS as the repo, the search filter when retrieving a role is the following: 

      (|(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceCollection eq "managed/role")
          (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceId eq "a9b9855d-c3cf-4996-b2cf-a9cb6de3975e")
          (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstPropertyName eq "members"))
        (&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceCollection eq "managed/role")
          (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceId eq "a9b9855d-c3cf-4996-b2cf-a9cb6de3975e")
          (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondPropertyName eq "members")))
      

       

      As the index has > 4k members this results in an unindexed search.

      Note that this will be the case for any relationship that has >4k relations - which may be quite common depending on IDM object model design. 

      This would seem to make DS as a repo unscalable in these situations.

       

      A backend stat of this environment can be found here:
      https://drive.google.com/file/d/1VgDwKjYSzZeYtNT7NWyEYDvjSa-_1puI/view?usp=sharing

      Testing

      Role with 40k members:

      IDM access log:

       

      {"roles":["internal/role/openidm-admin","internal/role/openidm-authorized"],"transactionId":"999d5420-e01a-444c-9609-565f1867a746-128175","client":\{"ip":"192.168.1.34","port":55716}

      ,"server":{"ip":"192.168.1.82","port":8443},"http":{"request":

      {"secure":true,"method":"GET","path":"[https://idm.example.com:8443/openidm/managed/role/a9b9855d-c3cf-4996-b2cf-a9cb6de3975e/members","queryParameters|https://idm.example.com:8443/openidm/managed/role/a9b9855d-c3cf-4996-b2cf-a9cb6de3975e/members]":\{"_queryFilter":["true"]}

      ,"headers":

      {"Accept":["*/*"],"Host":["[idm.example.com:8443|http://idm.example.com:8443/]"],"User-Agent":["python-requests/2.24.0"],"X-OpenIDM-Username":["openidm-admin"]}

      ,"cookies":{}}},"request":{"protocol":"CREST","operation":"QUERY"},"eventName":"access","userId":"openidm-admin","response":

      {"status":"SUCCESSFUL","statusCode":null,*"elapsedTime":19198,*"elapsedTimeUnits":"MILLISECONDS"}

      ,"timestamp":"2020-10-09T09:29:56.014Z","_id":"999d5420-e01a-444c-9609-565f1867a746-128211”}

       

      DS ldap access log:

       

      {"eventName":"DJ-LDAP","client":\{"ip":"127.0.0.1","port":60440}

      ,"server":{"ip":"127.0.0.1","port":31636},"request":{"protocol":"LDAPS","operation":"SEARCH","connId":0,"msgId":43,"dn":"ou=relationships,dc=openidm,dc=forgerock,dc=com","scope":"one","filter":"(|(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceId eq \"a9b9855d-c3cf-4996-b2cf-a9cb6de3975e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstPropertyName eq \"members\"))(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceId eq \"a9b9855d-c3cf-4996-b2cf-a9cb6de3975e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondPropertyName eq \"members\")))","attrs":["objectClass","uid","etag","createTimestamp","modifyTimestamp","fr-idm-relationship-json"]},"transactionId":"999d5420-e01a-444c-9609-565f1867a746-128175/0","response":

      {"status":"SUCCESSFUL","statusCode":"0","*elapsedTime":19194,*"elapsedTimeUnits":"MILLISECONDS",*"additionalItems":"unindexed"*,"nentries":40000}

      ,"timestamp":"2020-10-09T09:29:56.012Z","_id":"2b2550d1-aad2-4a54-bb3b-9f1b4cce88cf-144”}

       

      Role with 2732 members (under index entry limit):

       

      IDM access log:

      {"roles":["internal/role/openidm-admin","internal/role/openidm-authorized"],"transactionId":"999d5420-e01a-444c-9609-565f1867a746-128381","client":\{"ip":"192.168.1.34","port":55827}

      ,"server":{"ip":"192.168.1.82","port":8443},"http":{"request":

      {"secure":true,"method":"GET","path":"[https://idm.example.com:8443/openidm/managed/role/de581a16-cc91-4cac-84c3-d6c29af4228e/members","queryParameters|https://idm.example.com:8443/openidm/managed/role/de581a16-cc91-4cac-84c3-d6c29af4228e/members]":\{"_queryFilter":["true"]}

      ,"headers":

      {"Accept":["*/*"],"Host":["[idm.example.com:8443|http://idm.example.com:8443/]"],"User-Agent":["python-requests/2.24.0"],"X-OpenIDM-Username":["openidm-admin"]}

      ,"cookies":{}}},"request":{"protocol":"CREST","operation":"QUERY"},"eventName":"access","userId":"openidm-admin","response":

      {"status":"SUCCESSFUL","statusCode":null,*"elapsedTime":206,"*elapsedTimeUnits":"MILLISECONDS"}

      ,"timestamp":"2020-10-09T09:31:13.089Z","_id":"999d5420-e01a-444c-9609-565f1867a746-128385"}

      DS LDAP access log:

      {"eventName":"DJ-LDAP","client":\{"ip":"127.0.0.1","port":60442}

      ,"server":{"ip":"127.0.0.1","port":31636},"request":{"protocol":"LDAPS","operation":"SEARCH","connId":1,"msgId":108,"dn":"ou=relationships,dc=openidm,dc=forgerock,dc=com","scope":"one","filter":"(|(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceId eq \"de581a16-cc91-4cac-84c3-d6c29af4228e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstPropertyName eq \"members\"))(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceId eq \"de581a16-cc91-4cac-84c3-d6c29af4228e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondPropertyName eq \"members\")))","attrs":["objectClass","uid","etag","createTimestamp","modifyTimestamp","fr-idm-relationship-json"]},"transactionId":"999d5420-e01a-444c-9609-565f1867a746-128691/0","response":

      {"status":"SUCCESSFUL","statusCode":"0"*,"elapsedTime":171*,"elapsedTimeUnits":"MILLISECONDS","nentries":2732}

      ,"timestamp":"2020-10-09T09:33:32.955Z","_id":"2b2550d1-aad2-4a54-bb3b-9f1b4cce88cf-728"}

       

      PostgreSQL comparison:

       

      IDM access log (role has 40k members, and relationship table has 400k entries):

       

      {"roles":["internal/role/openidm-admin","internal/role/openidm-authorized"],"transactionId":"01003720-1d31-4158-984a-4f0b8be122f1-748701","client":\{"ip":"192.168.1.34","port":56353}

      ,"server":{"ip":"192.168.1.82","port":8443},"http":{"request":

      {"secure":true,"method":"GET","path":"[https://idme.example.com:8443/openidm/managed/role/059d3bda-40ca-43c2-88b2-86be33d2f0ef/members","queryParameters|https://idme.example.com:8443/openidm/managed/role/059d3bda-40ca-43c2-88b2-86be33d2f0ef/members]":\{"_queryFilter":["true"]}

      ,"headers":

      {"Accept":["*/*"],"Host":["[idme.example.com:8443|http://idme.example.com:8443/]"],"User-Agent":["python-requests/2.24.0"],"X-OpenIDM-Username":["openidm-admin"]}

      ,"cookies":{}}},"request":{"protocol":"CREST","operation":"QUERY"},"eventName":"access","userId":"openidm-admin","response":{"status":"SUCCESSFUL","statusCode":null,"elapsedTime":2174,"elapsedTimeUnits":"MILLISECONDS"},"timestamp":"2020-10-09T09:39:40.519Z","_id":"01003720-1d31-4158-984a-4f0b8be122f1-748706"}

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                brmiller Brendan Miller
                Reporter:
                bradley.tarisznyas Brad Tarisznyas
              • Votes:
                1 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated: