[OPENIDM-14314] Performance degradation when using query _fields param and returnByDefault is enabled Created: 28/Jan/20  Updated: 23/Jul/20  Resolved: 23/Jul/20

Status: Closed
Project: OpenIDM
Component/s: Module - Relationships, Module - Roles
Affects Version/s: 6.5.0.2
Fix Version/s: 7.0.0

Type: Bug Priority: Major
Reporter: Anastasios Kampas Assignee: Dirk Hogan
Resolution: Fixed Votes: 0
Labels: CLARK, release-notes
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Relates
relates to OPENIDM-9360 Align "returnByDefault" behavior betw... Closed
relates to OPENIDM-11601 OpenIDM Needs to Rework how it Querie... Closed
Target Version/s:
Verified Version/s:
QA Assignee: Chris Drake
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.

 



 Comments   
Comment by Dirk Hogan [ 28/Jan/20 ]

Anastasios Kampas from my reading of the code, setting returnByDefault to false on effectiveRoles and effectiveAssignments should prevent the corresponding scripts from being run. Are you sure the database activity is due to the invocation of these scripts? You could tell for sure by putting console log statements in these scripts, and see if these logs appear when returnByDefault is set to false on effectiveRoles and effectiveAssignments.

FWIW, I am working on https://bugster.forgerock.org/jira/browse/OPENIDM-11601, which will replace the effectiveRoles and effectiveAssignment script invocations on every managed user GET invocation with config-driven logic which will calculate and persist these (or any other configured) virtual properties only when the connected graph topology changes, as dictated by the relationship signaling configuration. Thus you will get up-to-date state in the virtual properties which will only be calculated when necessary, and not on every GET as is currently the case. So you will get your performance improvement without the peril of stale data.

Comment by Anastasios Kampas [ 28/Jan/20 ]

The customer has indeed disabled the flag as a workaround however the expectation is that since _fields=_id is requested, the relationships do not need to be calculated.
I can't tell if the database activity is coming from the referenced scripts however I can see a different number of SQL queries when returnByDefault is true and false (e.g with false, existing relationships are not calculated). The customer's log has the same observations.

This could end up being a duplicate of OPENIDM-11601 then

 

Comment by Dirk Hogan [ 22/May/20 ]

This issue is fixed by https://bugster.forgerock.org/jira/browse/OPENIDM-11601

The bottom line: effectiveRoles and effectiveAssignments will only be re-calculated when any part of the graph topology user->roles->assignments is changed, not on every GET of the user. So when you add a role to a user, or an assignment to a role which is assigned to a user, the user will be notified of this change, and virtual properties re-calculated, and persisted. When the user is next read, the virtual properties will not be re-calculated. So you can leave returnByDefault as true, and these virtual properties will be re-calculated and persisted only when the user's role and assignment graph topology changes - not on every read.

Comment by Chris Drake [ 22/Jul/20 ]

Tests Passed
Suite: relationships.relationship_derived_virtual_props
OpenIDM: 97af0b2474
pyforge 40f748f778

Comment by Lana Frost [ 23/Jul/20 ]

Reopening to add to release notes

Generated at Tue Mar 09 10:13:58 UTC 2021 using Jira 7.13.12#713012-sha1:6e07c38070d5191bbf7353952ed38f111754533a.