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

[Cesar Salazar]

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)