[keycloak-dev] Slow query performance for client with large data volume

[Alexandre Héroux]

After some benchmark tests, a point comes where the performance of the JPA
repository become a bottle neck.

At this point, we are doing tests with:

   - 21 560 391 COMPOSITE_ROLE rows
   - 14 774 947 USER_ROLE_MAPPING rows
   - 1 332 324 KEYCLOAK_ROLE rows
   - 191 034 CREDENTIAL rows
   - 191 034 USER_ENTITY rows
   - 145 905 PROTOCOL_MAPPER_CONFIG rows
   - 191 033  USER_REQUIRED_ACTION rows
   - 30 187 PROTOCOL_MAPPER rows
   - 5 031 CLIENT rows
   - 10 054 REDIRECT_URIS rows
   - 5 026 WEB_ORIGINS rows
   - 6 AUTHENTICATOR_CONFIG_ENTRY rows
   - 27 COMPONENT_CONFIG rows
   - 6 IDENTITY_PROVIDER_CONFIG rows
   - 41 AUTHENTICATION_EXECUTION rows
   - 16 AUTHENTICATION_FLOW rows
   - 4 AUTHENTICATOR_CONFIG rows
   - 2 CLIENT_DEFAULT_ROLES rows
   - 10 COMPONENT rows
   - 39 DATABASECHANGELOG rows
   - 1 DATABASECHANGELOGLOCK rows
   - 1 DATABASECHANGELOG_EXAMPLE_EN rows
   - 2 IDENTITY_PROVIDER rows
   - 1 MIGRATION_MODEL rows
   - 1 REALM rows
   - 13 REALM_ATTRIBUTE rows
   - 2 REALM_DEFAULT_ROLES rows
   - 1 REALM_EVENTS_LISTENERS rows
   - 1 REALM_REQUIRED_CREDENTIAL rows
   - 11 REALM_SUPPORTED_LOCALES rows
   - 5 REQUIRED_ACTION_PROVIDER rows
   - 2 SCOPE_MAPPING rows
   - 1 USER_ATTRIBUTE rows


1) When we are trying to load clients for migration/administration purpose,
Keycloak load each client one by one and perform lazy loading.

2) When the administrator delete a client, listeners are notified and some
of them reload all clients from the realm to load resources for each
occurrence from resourceServerStore one by one in a foreach loop.

With the current test volume, that take approximatively 1 hour 3 minutes to
:

   - load reaml's clients into memory
   - delete 11 clients
   - delete 50 Users
   - delete 340 user/role association
   - delete 85 roles
   - create 11 clients
   - create 85 roles
   - create 50 users
   - create 340 user/role association

After some tuning we can optimize the query and surrounded services to take
only 5 minutes

*Impacted projects*

   - keycloak-authz-policy-common
   -
      org.keycloak.authorization.policy.provider.user.UserPolicyProviderFactory
   - keycloak-model-infinispan
      -
      org.keycloak.models.authorization.infinispan.CachedResourceServerStore
      - org.keycloak.models.cache.infinispan.RealmCacheSession
   - keycloak-model-jpa
      - org.keycloak.authorization.jpa.entities.ResourceServerEntity
      - org.keycloak.models.jpa.entities.ClientEntity
      - org.keycloak.models.jpa.entities.ScopeMappingEntity
      - org.keycloak.authorization.jpa.store.JPAResourceServerStore
      - org.keycloak.models.jpa.JpaRealmProvider
      - keycloak-server-spi-private
   - org.keycloak.authorization.store.ResourceServerStore
      - org.keycloak.authorization.store.syncronization.RealmSynchronizer
      - org.keycloak.authorization.store.syncronization.UserSynchronizer
      - org.keycloak.migration.migrators.MigrateTo2_1_0
      - keycloak-services
   - org.keycloak.authorization.admin.AuthorizationService
      - org.keycloak.services.resources.admin.ClientsResource