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 (
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):
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 < ?