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

[Rob Shepherd]

Cesar,

Do you have Caching enabled?

Realm > User Federation > Cache Settings > Cache Policy


I posted to the list a few week ago to point out that with Caching enabled, the Keycloak was actually making More requests to a custom federated user backend.

http://lists.jboss.org/pipermail/keycloak-user/2017-November/012230.html <http://lists.jboss.org/pipermail/keycloak-user/2017-November/012230.html>


I appreciate that you use a different form of user federation, but it is the same unexpected scenario.

Rob




> On 3 Jan 2018, at 22:59, Cesar Salazar <csalazar at devsu.com> 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?
> 
> Thanks!
> 
> -- 
> *Cesar Salazar*
> Development Manager
> DEVSU | www.devsu.com
> skype: cesarsalazar007
> P: (213)-291-0752
> M: +593 9 2917 160 (Ecuador)
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user