We have 150 users which are connected to an Active Directory using the
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_.SERVICE_ACCOUNT_CLIENT_LINK is null) order by
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
Then,* for each user* 4 queries similar to this, (getting credentials I
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
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
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
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, which no matter the environment, will be really slow.
I sent the issue to the keycloak-user mailing list and the response from Marek was:
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