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

[Cesar Salazar]

Just wanted to update my findings on this issue: it is related to federated
users. My previous tests were invalid because latency for connecting to
Active Directory was different from the cloud that from my server.

I could reduce the time from 25 to 12 seconds by moving Active Directory to
Azure. It's better but still slow. Finally I ended up implementing a custom
endpoint, because we didn't have enough knowledge / time to provide a good
PR.

I will delete the issue I created in JIRA, but I don't know if I should
create another one to report this slowness issue. Also, the endpoint
doesn't respond if Active Directory is down.

Also, something really weird I found is that I use a token for the admin
user it's a few seconds faster than when making the request using a token
of a regular user. (It really doesn't make sense, but for real, using a
token of an admin user, the request always takes about 4 seconds less).


2018-01-04 9:51 GMT-05:00 Cesar Salazar <csalazar at devsu.com>:

> 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 <(213)%20291-0752>
> M: +593 9 2917 160 (Ecuador)
>



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