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

[Cesar Salazar]

Hi Marek, thanks for your answer. I just created a ticket in JIRA:
https://issues.jboss.org/browse/KEYCLOAK-6134

I was able to reduce the time to around 2.5 - 3 seconds by installing
Keycloak and mysql dockerized on bare metal (on a powerful server), but
still it should be considered slow, taking into account that we plan to
have not less than a few thousand users... and also considering that it's
not affordable to have such environment just for the authentication service.

If 150 users require 901 queries, and it takes around 3 seconds to respond,
with 3000 users, we would have 18001 requests, which would mean around 60
seconds for querying the users. (calculated using simple rule of three)

I tried the solution of using LDAP No-Import mode, but that doesn't solve
my use case, since I do need the /realm/users endpoint to return all the
users (not only allow them to login).

Anyways, for now we will stop scaling and I will ask one of my developers
to take a look at the code to see if we can find a fix and send a PR or
otherwise we will implement our own endpoint to get all the users (which is
something we need for our app).

Thanks!


2018-01-04 5:39 GMT-05:00 Marek Posolda <mposolda at redhat.com>:

> 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!
>>
>>
>


-- 
*Cesar Salazar*
Development Manager
DEVSU | www.devsu.com
skype: cesarsalazar007
P: (213)-291-0752
M: +593 9 2917 160 (Ecuador)