[keycloak-user] GET users endpoint is making lots of requests to the database (its really slow!)

[Marek Posolda]

On 03/01/18 23:59, Cesar Salazar wrote:
> Hi,
>
> First of all, thanks for the great work on keycloak!
>
> We are using keycloak for an application, and it was working great (until
> we launched to production)
>
> We have 150 users which are connected to an Active Directory using the
> Federation functionality.
>
> It works, but the endpoint GET /{realm}/users takes about 23 seconds to
> respond (Keycloak running on a container in GKE backed by a mysql server on
> Google Cloud SQL).
>
> I enabled mysql logging and the problem seems to be that just for
> responding this endpoint, the server makes 901 queries to the database!
>
> These are the queries:
>
> First query, to get the users:
>
> select userentity0_.ID as ID1_71_, userentity0_.CREATED_TIMESTAMP as
> CREATED_2_71_, userentity0_.EMAIL as EMAIL3_71_,
> userentity0_.EMAIL_CONSTRAINT as EMAIL_CO4_71_, userentity0_.EMAIL_VERIFIED
> as EMAIL_VE5_71_, userentity0_.ENABLED as ENABLED6_71_,
> userentity0_.FEDERATION_LINK as FEDERATI7_71_, userentity0_.FIRST_NAME as
> FIRST_NA8_71_, userentity0_.LAST_NAME as LAST_NAM9_71_,
> userentity0_.NOT_BEFORE as NOT_BEF10_71_, userentity0_.REALM_ID as
> REALM_I11_71_, userentity0_.SERVICE_ACCOUNT_CLIENT_LINK as SERVICE12_71_,
> userentity0_.USERNAME as USERNAM13_71_ from USER_ENTITY userentity0_ where
> userentity0_.REALM_ID='my-realm' and
> (userentity0_.SERVICE_ACCOUNT_CLIENT_LINK is null) order by
> userentity0_.USERNAME
>
>
> Then,* for each user *a query like this, (getting user attributes I guess)
>
> select attributes0_.USER_ID as USER_ID4_67_0_, attributes0_.ID as
> ID1_67_0_, attributes0_.ID as ID1_67_1_, attributes0_.NAME as NAME2_67_1_,
> attributes0_.USER_ID as USER_ID4_67_1_, attributes0_.VALUE as VALUE3_67_1_
> from USER_ATTRIBUTE attributes0_ where
> attributes0_.USER_ID='b920df7c-a419-4150-86bd-9f81c7ea0b70'
>
>
> Then,* for each user* 4 queries similar to this, (getting credentials I
> guess)
>
> select credential0_.ID as ID1_18_, credential0_.ALGORITHM as ALGORITH2_18_,
> credential0_.COUNTER as COUNTER3_18_, credential0_.CREATED_DATE as
> CREATED_4_18_, credential0_.DEVICE as DEVICE5_18_, credential0_.DIGITS as
> DIGITS6_18_, credential0_.HASH_ITERATIONS as HASH_ITE7_18_,
> credential0_.PERIOD as PERIOD8_18_, credential0_.SALT as SALT9_18_,
> credential0_.TYPE as TYPE10_18_, credential0_.USER_ID as USER_ID12_18_,
> credential0_.VALUE as VALUE11_18_ from CREDENTIAL credential0_ where
> credential0_.USER_ID='94525793-297b-4895-ab2b-7cf8b580e9fa' and
> credential0_.TYPE='totp'
>
>
> A query with type *totp* is queried 2 times, the other 2 times are queried
> with type *hotp* and *password*
>
> and finally one more query* for each user* (getting required actions I
> guess)
>
> select requiredac0_.USER_ID as USER_ID2_77_0_, requiredac0_.REQUIRED_ACTION
> as REQUIRED1_77_0_, requiredac0_.REQUIRED_ACTION as REQUIRED1_77_1_,
> requiredac0_.USER_ID as USER_ID2_77_1_ from USER_REQUIRED_ACTION
> requiredac0_ where
> requiredac0_.USER_ID='94525793-297b-4895-ab2b-7cf8b580e9fa'
>
>
> So, in total, for 150 users, Keycloak is making 901 requests to the
> database! If I increase the number to 500 users, will it be 30001
> requests???
>
> How can this be improved? Is there something wrong I'm doing in the
> configuration? Does this happens only with Federated users?
I don't think it is specific only for federated users. IMO the same will 
happen for 150 non-federated users too.

You can create JIRA, but not sure if we are able to fix it on our side, 
we are using JPA/Hibernate under the covers and I think it doesn't 
easily allow something like "batch" query to retrieve attributes, 
requiredActions, credentials in single SQL query for current page of 
users...

Maybe the options for you to improve this are:
- Improve DB connection and make sure that there is no big network 
latency between DB and Keycloak (It seems this is the big issue in your 
env).
- Use LDAP No-Import mode

Marek
>
> Thanks!
>