Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Deferred
    • Affects Version/s: 10.0.1
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None
    • Docs QE Status:
      NEW
    • QE Status:
      NEW

      Description

      Hi,

      I read a few topics in Keycloak(KC) Users group about slow queries and KC's developers say that everything is OK and "you must scale" KC in your infrastructure. Yes, I know that is cheaper today to buy +10 new VPS because KC can't handle 10K accounts. However, what is the reason? Why you don't want to fix SQL queries and optimize indexes? It's very easy, fill KC with 50K accounts and try to do simple operations. The average response is my case is 150ms.

      I'm not familiar with Java. Do you use ORM or similar to that? Is it possible to analyze all queries with `EXPLAIN`? As a minimum, we have it for MySQL. I tested the latest KC with 50K accounts and it's crazy slow. I used MySQL, KC consumes 100% CPU of one process.

      I even tried to enable logging for slow queries. Just for a few hours, I see 1681 reports where queries consumed more than 100ms. 50K records for MySQL - THIS IS NOTHING! We have millions of records and average query performs with 1-5ms. MySQL is configured for high performance (InnoDB pool in RAM).

      Let's discuss a few interesting queries:

      ```
      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_.REALM_ID='testrealm' and (lower(userentity0_.EMAIL) like '%user@example.com%') order by userentity0_.USERNAME asc limit 100;
      ```

      1. Why do we need ORDER BY to check if email is used? Using ORDER BY VARCHAR/STRING has a negative influence depending on DB type. For example, MySQL will not use an index in this case.
      2. Why do you perform RUNTIME EVALUATION for N rows with `lower()`? It instructs DB to perform `lower()` on each ROW. So, you have 10M of accounts, 10M iterations. In my test example, DB says "examined 50169 rows". Why we can not use the NORMALIZED email from DB, such `EMAIL_CONSTRAINT` or do `lower()` on client-side (Java?)?
      3. Why do you need FULL TEXT SEARCH per Char field via `LIKE %%`? The index will not be used in this case if we have prefix `%`
      4. Why we can't search for use email via `EMAIL_CONSTRAINT = :usermeail`? index will be used in this case.

      So. just a simple operation and ton of issues.

      Next query:
      ```
      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='8e4a6dc8-10fe-40b2-9b2a-2dedc2ca8b57' and userentity0_.REALM_ID='testrealm';
      ```

      1. 50K records with SERVICE_ACCOUNT_CLIENT_LINK=NULL in DB. I don't know what does this field mean.
      2. Adding this index helped:
      ```
      CREATE INDEX `IDX_SERVICE_ACCOUNT_CLIENT_LINK` ON `keycloak`.`USER_ENTITY` (`SERVICE_ACCOUNT_CLIENT_LINK`) USING BTREE;
      ```

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  hmlnarik Hynek Mlnařík
                  Reporter:
                  ikologn Ivan Kravets
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: