[keycloak-user] User REST API: n+1 selects

[Adrian Gonzalez]

 On Group API, I think it's less critical, first loading is resource consuming, but afterwards, the groups are in cache.
With a db with 1000 groups (no hierarchy)
1. The first call to /groups with first=0, max=100 issues 3076 SQL statements.If I add @Fetch(Subselect), the number of statements is reduced to 2077 (2 times n+1 select, n being 1000).Then the rest is done in https://github.com/keycloak/keycloak/blob/a743600b344763ce2e7f70a625f590a8425fc5f3/model/infinispan/src/main/java/org/keycloak/models/cache/infinispan/entities/CachedGroup.java#L47-L55
2. later calls are cached, so no more db access (until a group is updated.removed I assume)

    Le mardi 13 février 2018 à 00:26:21 UTC+1, Adrian Gonzalez <adr_gonzalez at yahoo.fr> a écrit :  
 
  Here are some test results:
Laptop: Intel Core i7-6820HQ CPU 2.70GHz × 8 16 o RAMsettings as per https://github.com/keycloak/keycloak/blob/3.4.1.CR1/testsuite/performance/README.provisioning-parameters.mdInjector, KC and postgres are on the same laptop.

Scenario: /users call with username criteria returning a 100 users page.1000 total users in db.
Before optimization:
 1 thread, no wait tx/s: 9 avg response time (ms): 110 CPU: java=80%, postgres=20% mem 5 thread, no wait tx/s: 31 avg response time (ms): 149 CPU: java=628%, postgres=135% (19 processes with 7.6%) mem: 1g used 10 thread, no wait tx/s: 41.5 avg response time (ms): 219 CPU: java=613%, postgres=135% (9 processes with 15%) mem: 400m used <note: same tx/s for 20threads)
After optimization 1 thread, no wait tx/s: 132 avg response time (ms): 7 CPU: java=85%, postgres=13% mem 250m 5 thread, no wait tx/s: 365 avg response time (ms): 13 CPU: java=440%, postgres=40% mem: 1g used 10 thread, no wait tx/s: 385 avg response time (ms): 25 CPU: java=600%, postgres=54% mem: 1Go used 20 thread, no wait tx/s: 450 avg response time (ms): 42 CPU: java=600%, postgres=57% mem: 1.2Go used <note: same tx/s for 30threads)
Cheers,Adrian


    Le lundi 12 février 2018 à 22:56:22 UTC+1, Adrian Gonzalez <adr_gonzalez at yahoo.fr> a écrit :  
 
  As a workaround for issue 2, we can activate Permissions in realm-management Client (I've created a custom realm to avoid working on master realm).This way, we ensure root.realmResourceServer() is not null.But that's a bit convoluted :(
 
    Le lundi 12 février 2018 à 22:37:08 UTC+1, Adrian Gonzalez <adr_gonzalez at yahoo.fr> a écrit :  
 
 Hello,
I'm testing KC 3.4.3 REST API and I get n+1 selects (aka 701 selects when asking for a page of 100 users).

Issue 1:Looking at the code, there's n+1 select on the following fields UserEntity: - attributes- requiredActions- credentials 7The n+1 select is triggered by https://github.com/keycloak/keycloak/blob/8e53ccf5abb4d7cc3ab8d5abc9d078a7f8725e8a/services/src/main/java/org/keycloak/services/resources/admin/UsersResource.java#L215
This can be solved by annotated these attributes with @Fetch(FetchMode.SUBSELECT).I also tried using EntityGraph, but it doesn't work since we're using Collection types (instead of Set) and because we're doing pagination while fetching ToMany associations.

Issue 2: n+1 select because we don't cache null values
We have this select executed n times:select    resourcese0_.ID as ID1_60_0_,    resourcese0_.ALLOW_RS_REMOTE_MGMT as ALLOW_RS2_60_0_,    resourcese0_.POLICY_ENFORCE_MODE as POLICY_E3_60_0_ from    RESOURCE_SERVER resourcese0_ where    resourcese0_.ID=?
This one is done here:https://github.com/keycloak/keycloak/blob/8e53ccf5abb4d7cc3ab8d5abc9d078a7f8725e8a/services/src/main/java/org/keycloak/services/resources/admin/UsersResource.java#L216

In default setup,root.realmResourceServer() is null.Since StoreFactoryCacheSession doesn't cache null values, the return value never gets cached.I don't know how to easily solve that one.
Should I create an issue ?
Thanks