Uploaded image for project: 'Keycloak'
  1. Keycloak
  2. KEYCLOAK-6134

GET users endpoint is making lots of requests to the database (its really slow!)

    Details

    • Steps to Reproduce:
      • Add around 150 users to keycloak
      • Call the GET realm/users endpoint
    • Docs QE Status:
      NEW
    • QE Status:
      NEW

      Description

      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, 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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  c3s4r Cesar Salazar
                • Votes:
                  2 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: