Overall performance issue for Get All User api - GET /Users

[rohit-armorikar]

We are using UAA version: 77.10.0
We are on MYSQL DB: 8.0.mysql_aurora.3.05.2

In ScimUserEndpoint.java, we fetch complete user data from RDS, for given identity_zone_id.

Case 1:
In findUsers() -> syncGroup() -> getGroupsWithMember() → we again fetch the user data from RDS.

Problem: This looks like repeated query on Database.

Can we have a check, before checking in DB for users, that if this user was already fetched from last query, we will avoid querying on User table again.
And only in case the user is coming from another transitive group, we can have this check.

Case 2:
Similarly, in same flow, we fetch default user groups for user.
getDefaultUserGroups(),

Problem: Again this query is being performed repeatedly on Database. For 100 users, 100 times for 1 request.
Whereas the default groups remains same for an identity zone.

Case 3:
We already have index as "CREATE UNIQUE INDEX group_membership_unique_key ON group_membership (member_id,group_id);"
However for below example of query:
SELECT g.id, g.displayName, g.description, g.created, g.lastModified, g.version, g.identity_zone_id
FROM groups g, group_membership gm
WHERE gm.group_id = g.id
AND gm.identity_zone_id = g.identity_zone_id
AND gm.identity_zone_id = 'my_identity_zone'
AND gm.member_id IN ('my_user_id');

The above query is giving us performance issues, so it may require another composite index as below:
CREATE INDEX idx_group_membership_zone_member_group
ON group_membership (identity_zone_id, member_id, group_id);

Can we analyze the performance of this query and add any composite index if required?

[strehle]

@rohit-armorikar Thanks for reporting... we (SAP) use postgresql and we have additional indexes created, however we should check if in mysql something missing or a general problem, means also in postgresql.

We have improved /Users with some PRs @adrianhoelzl-sap can you help here, please ?

FYI @torsten-sap can you ask someone from your team in addition if there is something we know about

[rohit-armorikar]

Thanks @strehle for the update, @adrianhoelzl-sap can you please share more details of the PR for this fix.
Also @strehle do you suggest us to add another composite index as below, for fixing the performance of this query:
CREATE INDEX idx_group_membership_zone_member_group
ON group_membership (identity_zone_id, member_id, group_id);

Or we can expect it will be added in next release of UAA.

[strehle]

Or we can expect it will be added in next release of UAA.

I asked adrian but if he has no direct answer then there is no easy fix.

You can create a PR with your findings and then we can verify and bring it to a realdy.

Please create separate PRs for cases 1/2 and an extra one for 3 because for case 3. I dont see an easy if

[adrianhoelzl-sap]

Hi @rohit-armorikar,

Case 1

Where exactly do you see that user data is queried multiple times? The users should only be queried once and after that, for each user, groups are queried (multiple times if nested group memberships exist).

Case 2

Did you actually encounter queries being executed multiple times here? We would expect the default groups to always be looked up in the IdentityZoneHolder and no query against the DB being necessary.

Case 3

Can you please provide us with the output of EXPLAIN ANALYZE ... for an inefficient example and - after adding the index you proposed - for an example where the index fixes the issue?

Best regards
Adrian

[rohit-armorikar]

Hello @adrianhoelzl-sap
Thanks for your reply.
For Case 1 and Case 2, Will try to add some logging and share that with you, if that helps.

For Case 3, We checked more details,
Below index:
CREATE UNIQUE INDEX group_membership_unique_key ON group_membership (identity_zone_id,member_id,group_id);

This is present in postgres and its missing in mysql, this is the same index which we pointed in case 3,
We are planning to manually add this index on our lower environment and check if its improving performance.
Also there are few other queries as well, which we are checking more, will revert after more analysis.

Really appreciate the support and guidance from you all to get this issue solved on our end.

[rohit-armorikar]

Hello @adrianhoelzl-sap We have create a PR for Case 3, please review and suggest if this will help us to improve performance.
#3347

FYI
@strehle

[rohit-armorikar]

Hello @adrianhoelzl-sap @strehle we have updated the PR for required changes for Case 3.

However for Case 1 and Case 2: I have a doubt, we have observed that our UAA service is making repeated calls to RDS even for same queries.
This is in end increasing load on RDS, whereas the queries remains same.

My question is: Does UAA recommend using some caching framework, which can give the results for such repeated queries from cache, instead of fetching the records from RDS (DB)??
We have seen reference to some configuration for same, see below reference:

uaa:
database:
hibernate:
cache:
use_second_level_cache: true
use_query_cache: true
region.factory_class: org.hibernate.cache.ehcache.EhCacheRegionFactory

Do we recommend using some configuration similar to this for UAA with MYSQL as well??
Your guidance will help us for sure and any reference link / documentation will be helpful for same.

[rohit-armorikar]

@strehle @adrianhoelzl-sap any expertise on usage of below configurations of UAA:
uaa:
database:
hibernate:
cache:
use_second_level_cache: true
use_query_cache: true
region.factory_class: org.hibernate.cache.ehcache.EhCacheRegionFactory

With respect to above comment..

Thanks In Advance.

[rohit-armorikar]

@strehle @adrianhoelzl-sap
Any update on using uaa caching properties, for caching repeated RDS calls for 1 request in UAA.
If any caching configuration properties are supported in UAA by default, which are tested for achieving higher throughput, then please provide reference for same.