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

[Pedro Igor Silva]

Are you able to send a PR with your changes so we can discuss them from
there ?

On Wed, Apr 26, 2017 at 12:00 PM, Alexandre Héroux <
alexandre.heroux at infinisolution.ca> wrote:

> 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
> _______________________________________________
> keycloak-dev mailing list
> keycloak-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-dev
>