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

FED_USER_ATTRIBUTE causing deadlocks during mass registration

    Details

    • Type: Enhancement
    • Status: Triage (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 9.0.0
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None
    • Docs QE Status:
      NEW
    • QE Status:
      NEW

      Description

      Hello,

      we have been running benchmarks of keycloak 9.0.0 (docker cluster + separate infinispan + mysql database) and we were seeing a lot of deadlocks during mass registration (~10 threads concurrently registering ~10k-100k users on a single realm)

      We found out that it was caused by full scans performed on FED_USER_ATTRIBUTE, locking the table and preventing other threads to perform inserts.

      Adding this index causes the issue to disappear:

      alter table FED_USER_ATTRIBUTE ADD INDEX IDX_FU_ATTRIBUTE(ID,USER_ID,REALM_ID,NAME,STORAGE_PROVIDER_ID);
      
      

      (indexing `STORAGE_PROVIDER_ID` allow the optimizer to not perform a full scan)

      Customized container with those changes

      We have built a customized container based on jboss/keycloak:9.0.0, adding a custom user-federation module, jgroups settings for environments where multicast isn't available, and lots of other changes performed using the CLI.

      Where should we look to insert a liquibase migration to create/update this index ? possibly without having to fork or recompile keycloak ?

      Thanks in advance

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                zuzur14 Erwan Arzur
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: