Repository navigation
Exact match user query by email is doing a full table scan #8893
Description
Describe the bug
One of our internal projects needs a fast user lookup by email. Reading the API docs the GET user query with email query parameter and exact match query parameter seemed appropriate for this use case.
When tested against a user database of >2M rows the request took 13 seconds. The query was logged in the slow query log and an explain showed that it was filtering all rows. Removing the lower function call from around the email in the where clause allowed it match on the index and return quickly.
We're using mysql 5.7.
Version
11.0.2
Expected behavior
Exact match email query should be fast.
Actual behavior
Exact match email query took 13s to return when run against a user db of 2M rows.
How to Reproduce?
Query a sizable user database using the Keycloak /users endpoint with the parameters ?email=&exact=true.
Anything else?
I tracked the source of this issue to the following line of code. It seems to me that the builder.lower around the email key is unnecessary since email is already forced to lower case on save. The builder.lower causes JPA to emit a lower function call in mysql syntax and the function is applied on every row prior to comparison.
Activity
nicolasbenoist commentedon Nov 24, 2021
Hello @mparlee,
We have discovered the exact same issue today after activating a new feature on Datadog called "Database APM". We have found that 99% of the CPU time was used by this request, forcing us to use a very large Database on AWS.
We have like you around 2M rows for the users.
We SQL request is the follow :
select userentity0_.ID, userentity0_.CREATED_TIMESTAMP, userentity0_.EMAIL, userentity0_.EMAIL_CONSTRAINT, userentity0_.EMAIL_VERIFIED, userentity0_.ENABLED, userentity0_.FEDERATION_LINK, userentity0_.FIRST_NAME, userentity0_.LAST_NAME, userentity0_.NOT_BEFORE, userentity0_.REALM_ID, userentity0_.SERVICE_ACCOUNT_CLIENT_LINK, userentity0_.USERNAME from USER_ENTITY userentity0_ where userentity0_.REALM_ID = ? and ( lower ( userentity0_.USERNAME ) like ? ) order by userentity0_.USERNAME asc limit ?As you mentioned there is 2 problems :
We will try to use this parameter and let you know.
But the lower must be fixed by the Keycloak team. It's a real issue for large DB.
Thanks
Nicolas
nicolasbenoist commentedon Nov 24, 2021
Here are the results of the test :
GET /auth/admin/realms/customer/users?username=xxx
Time : 3s
GET /auth/admin/realms/customer/users?username=xxx&exact=true
Time : 76ms
So basically it changes everything in term of performance.
Nevertheless I think that the "lower" could be avoided but I do not measure the impact on performance.
ahus1 commentedon Feb 11, 2022
Thanks for reporting this. Adding the lower() is necessary to normalize the email addresses stored in the database, as email addresses with the different capitalization are still considered the same email address.
To get a good performance, a database would need to have a function based index for email addresses on the user table.
To those users experiencing this issue, I ask: does adding an index solve the issue for you? If yes, please paste the index you're using here. If such an index is missing in the latest version of Keycloak, please consider creating a pull request for this issue to add it.
cc: @hmlnarik / please comment if I missed something here.
mparlee commentedon Feb 11, 2022
I don't believe it's necessary to normalize the table's email column in this search since emails are already stored in lower case. The query input should be lower cased of course, and that will not impact db performance at all. I have implemented an email search in a SPI that works and is performant. The existing indexes are sufficient.
ahus1 commentedon Feb 14, 2022
@mparlee - yes, I now see that email addresses are already stored lower case. I'm sorry that I missed the comment you left in the original issue above; I now see you mentioned it already up there.
Both the email address and the user name are already stored lowercase in the database.
I'm looking forward to a community pull request. AFAIK USERNAME/EMAIL needs to be separated from FIRST_NAME/LAST_NAME in the switch. To make it consistent, also UserModel.SEARCH should be updated as well.
(please mention my handle on the PR, so I can review the PR)
ahus1 commentedon Feb 14, 2022
Today someone else started a discussion for the new storage implementation if user names should be stored preserving the case.
Please note that there are no plans to change this for the existing JPA store. Please have a look at the discussion and comment to steer this in a direction that works for your and other scenarios.
See #10198 for details.
Improve user search performance
Improve user search performance
Improve user search performance
guusdk commentedon Apr 22, 2022
A while back, I worked on a similar fix in #9111 which basically was a duplicate of what has been done here. The only bit that I added there, was to make sure that searches would first transform the input to lower case. You might want to augment the fixes to do something similar? See commits/83dd829534e9078de683aedaff670d4564c208c2 for details.