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

Performance degradation when using query _fields param and returnByDefault is enabled

    XMLWordPrintable

    Details

    • Target Version/s:
    • Verified Version/s:
    • Support Ticket IDs:

      Description

      It's been reported that when effectiveRoles and effectiveAssignments returnbyDefault is enabled and when roles are not queried by the client, IDM will still try to get the data from the DB even though it's not needed.

      Re-production steps:

      1. Setup IDM with a DB as repo (example here is with mysql)
      2. Create a managed user and assign him some roles (e.g 3 roles)
      3. Add another role using HTTP PATCH quering the _id field only. Note that effectiveRoles and effectiveAssignments returnbyDefault flag is true (by default). For example:
        curl --request PATCH 'http://idm.example.com:18080/openidm/managed/user/38e568fc-98aa-45b5-802c-bc9c75ec50bb?_fields=_id' \
        --header 'Content-Type: application/json' \
        --header 'X-OpenIDM-Username: openidm-admin' \
        --header 'X-OpenIDM-Password: openidm-admin' \
        --data-raw '[
            {
              "operation": "add",
              "field": "/roles/-",
              "value": {"_ref" : "managed/role/{{roleId}}"}
            }
         ]'

        Observe the IDM logs, you will see the following SQL queries:

        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/user' AND obj.objectid  = '38e568fc-98aa-45b5-802c-bc9c75ec50bb' LIMIT 1 (=>user UID)
        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/role' AND obj.objectid  = 'f44e258a-fd8f-448c-be7f-f9fb15a37ffc' LIMIT 1 (=>role1)
        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/role' AND obj.objectid  = 'a6154802-dc9d-44b2-b052-8962e50052a2' LIMIT 1 (=>role2)
        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/role' AND obj.objectid  = 'bb25b05b-ccb3-43c5-b2ae-36468405f43e' LIMIT 1 (=>role3)
        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/role' AND obj.objectid  = 'da77f7b7-44a9-4134-8a62-5feba6cda90b' LIMIT 1 (=>role4)
      4. Now try adding a 5th role, however, this time disable the returnByDefault flag for the effectiveRoles and effectiveAssignments.
         curl --request PATCH 'http://idm.example.com:18080/openidm/managed/user/38e568fc-98aa-45b5-802c-bc9c75ec50bb?_fields=_id' \
        --header 'Content-Type: application/json' \
        --header 'X-OpenIDM-Username: openidm-admin' \
        --header 'X-OpenIDM-Password: openidm-admin' \
        --data-raw '[
            {
              "operation": "add",
              "field": "/roles/-",
              "value": {"_ref" : "managed/role/921d5ef2-8dd6-4869-9f1f-68032bdf19d1"}
            }
         ]'
        Response:
        {"_id""38e568fc-98aa-45b5-802c-bc9c75ec50bb","_rev""37"}

        Observe the IDM logs

        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/user' AND obj.objectid  = '38e568fc-98aa-45b5-802c-bc9c75ec50bb' LIMIT 1 (=>user UID)
        SQL: SELECT obj.objectid , obj.rev, obj.fullobject FROM openidm.objecttypes objtype, openidm.managedobjects obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = 'managed/role' AND obj.objectid  = '921d5ef2-8dd6-4869-9f1f-68032bdf19d1' LIMIT 1 (=>role5)

         

      This can have a serious effect on Database performance whe multiple relationships are linked to the user. It's been observed that when the flag is disabled, the response times are 85% faster.

      This is linked to OPENIDM-9360.

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dhogan Dirk Hogan
              Reporter:
              anastasios.kampas Anastasios Kampas
              QA Assignee:
              Chris Drake Chris Drake
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: