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

deleteExpiredClientSessions very slow on MariaDB

    XMLWordPrintable

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Done
    • 12.0.2, 13.0.1, 14.0.0
    • 15.1.0
    • Storage
    • None

    Description

      We are using Keycloak together with MariaDB, and there is one statement that regularly causes problems.

      It is "deleteExpiredClientSessions" in PersistentClientSessionEntity:

      delete from PersistentClientSessionEntity sess
       
      where sess.userSessionId IN (
        select
      u.userSessionId
       
      from PersistentUserSessionEntity u
        where
      u.realmId = :realmId
           AND
      u.offline = :offline
           AND
      u.lastSessionRefresh < :lastSessionRefresh
      )

      MariaDB cannot optimize the subquery, resulting in long execution time, which leads to problems for users checking their tokens (probably because of table lock). Currently it gets that bad that the statement somehow freezes in the database, only DB admin intervention can remedy it.

      I propose to change the statement to an join (given as SQL here):

      delete c
      from OFFLINE_CLIENT_SESSION c join OFFLINE_USER_SESSION u
      where c.user_session_id = u.user_session_id   and u.realm_id = ? 
         and u.offline_flag = ? 
         and u.last_session_refresh < ?

      Attachments

        Issue Links

          Activity

            People

              rhn-support-rmartinc Ricardo Martin Camarero
              bstumm Boris Stumm (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: