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!

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(a)redhat.com&gt;: > 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)

On 3 Jan 2018, at 22:59, Cesar Salazar <csalazar(a)devsu.com&gt; 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(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user