Sorry for late reply, but at least I'm replying ;)
On Thu, 24 Jan 2019 at 17:00, Thomas Darimont <
Hello Keycloak developers,
The current Keycloak server docker image comes with some default
configuration (listed below) for the supported databases (mysql, postgres,
mariadb), which work quite well so far.
However, in HA database production environments, we encountered some issues
with the default configuration. We were able to mitigate those issues with
the following general, and in our case PostgreSQL specific, settings.
I'd like to propose adding those settings to the "change-database.cli"
scripts of the default Keycloak docker images.
We faced the following problems:
1. Connections are not always validated before use.
If a database node is not available or has any issues, the connections in
the connection-pool don't reflect this immediately, although the
`check-valid-connection-sql` is set.
2. No timeout configured for database queries.
Some database queries in Keycloak could run quite slowly (due to
performance bugs, inefficient queries etc.). Sometimes this freezes the
admin-console and if the operation is retried in different tabs, could use
up all connections from the pool.
Eventually, the query runs into a timeout, but only after a driver specific
amount of time.
3. Creation of new connections could introduce an unnecessary long delay
We managed to mitigate those issues by using the following additional
settings in production - and haven't seen any problems with database
This mitigates problem 1: This ensures that a connection is "really" tested
# Configure datasource to connection before use
This mitigates problem 2: since we can now explicitly control how long a
query can run at max. --> better transparency.
Doesn't this result in extra overhead for each request? Would it not be
better to use background validation?
# Configure datasource to use explicit query timeout in seconds
This mitigates problem 3:
To reduce the time for connection reuse we also use the following setting
to use the next valid connection first, instead of immediately trying to
create a new one.
It is probably a better default to have 60 seconds timeout, but that may
result in issues during migration.
# Configure datasource to try all other connections before failing
In combination with PostgreSQL we also configured the following, which
helped to make the database error handling more robust:
This improves the PostgreSQL database error handling in Keycloak.
Would make sense to add those by default for PostgreSQL
Note that this document contains useful configurations for other database
vendors as well.
For reference, this is the current postgres change-database.cli script:
write-attribute(name=check-valid-connection-sql, value="SELECT 1")
What do you think about adding the proposed settings?
keycloak-dev mailing list