We are running Keycloak (10.0.2) with a fairly large amount of users (a bit under 100,000). We are trying to optimize the system resource usage, as we’ve noticed that our Keycloak deployment is pretty hungry for resources.
The load on the database in particular seems very high. One of the queries that is being executed often is one like this (formatting is mine - I’ve also redacted some of the parameter values to protect the innocent):
select userentity0_.ID as ID1_73_, userentity0_.CREATED_TIMESTAMP as CREATED_2_73_, userentity0_.EMAIL as EMAIL3_73_, userentity0_.EMAIL_CONSTRAINT as EMAIL_CO4_73_, userentity0_.EMAIL_VERIFIED as EMAIL_VE5_73_, userentity0_.ENABLED as ENABLED6_73_, userentity0_.FEDERATION_LINK as FEDERATI7_73_, userentity0_.FIRST_NAME as FIRST_NA8_73_, userentity0_.LAST_NAME as LAST_NAM9_73_, userentity0_.NOT_BEFORE as NOT_BEF10_73_, userentity0_.REALM_ID as REALM_I11_73_, userentity0_.SERVICE_ACCOUNT_CLIENT_LINK as SERVICE12_73_, userentity0_.USERNAME as USERNAM13_73_ from USER_ENTITY userentity0_ where userentity0_.SERVICE_ACCOUNT_CLIENT_LINK='533d7XXX-bbXX-44XX-91XX-c343d3XXXXXX' and userentity0_.REALM_ID='our-realm'
This seems to be the
getRealmUserByServiceAccount named query as defined in
Execution of this query seems very inefficient: although there are indices that cover the
REALM_ID column, there is none that covers the
SERVICE_ACCOUNT_CLIENT_LINK column. As a result, all rows in the table are evaluated.
Although USER_ENTRIES are cached, my understanding is that the cache lookup is based on something different than
SERVICE_ACCOUNT_CLIENT_LINK. Therefor, I don’t expect the functionality that executes this query to benefit from existing caches.
As far as I can tell, our application is not making use of any service accounts:
- None of the
USER_ENTITYrows have a non-null value for that column.
- None of the rows in
CLIENThave a value for the
SERVICE_ACCOUNTS_ENABLEDcolumn that’s different from
Can we somehow configure Keycloak to skip this lookup? If not, is there a way to optimize the configuration of Keycloak around this functionality?