Description
Description
Update default-persistence.xml and add the following properties
<property name="hibernate.log_slow_query" value="10000"/> <!-- a threshold in ms when a query is considered slow, might be configurable later -->
<property name="hibernate.use_sql_comments" value="true"/>Document or enable logging of slow slow queries, similar to this:
--log-level=info,org.hibernate.SQL_SLOW:info
Discussion
No response
Motivation
When Keycloak has to work with many realms, clients, or persistent (offline) user sessions, some database operations may run slower than desired. Slow database operations can have various causes: too much load on the database, inefficient queries, or missing indexes on database tables.
These problems usually become visible through higher latency, lower throughput, or increased errors in queries.
To avoid poor performance and outages, it is helpful to identify slow database queries in advance and mitigate them accordingly.
The Keycloak infrastructure already has a tool for this: Hibernates Slow Query Log.
The slow query log can be used to identify slow queries and react to them early before any service degradation occurs.
Details
See slow query log documentation: https://docs.jboss.org/hibernate/orm/6.6/introduction/html_single/Hibernate_Introduction.html#slow-queries.
The additional use_sql_comments property prepends the generated query with the JPA QL query, or name to make them easier to find in the code base.
With the slow query log enabled, server admins can see log statements like the following in the server log. (I used log_slow_query=5000 for this example):
11:46:01 INFO [or.hi.SQL_SLOW] (executor-thread-1) Slow query took 5007 milliseconds [/* dynamic native SQL query */ SELECT pg_sleep(5)]
Unfortunately, because of this issue there is currently no reliable way to pass those additional persistence properties to the hibernate configruation except copying the existing default-persistence.xml file in a custom extenion in the src/main/resources folder and export it to an extension.jar copied to $KEYCLOAK_HOME/providers.
See: #19427 (comment) for a description of the workaround.