Group members endpoint extremely slow

want to start here before opening a “bug” because it is quite possible environment/config specific.

we’ve noticed 15-20 second latency querying auth/admin/realms/realm/groups/<group_id>/members . group size does not appear to be a factor, i can make a test group with 5 members and get the same result as larger groups (largest is only ~50 members).

first noticed on 13.x but have validated the same happens after upgrading to 15.x (do not want to jump to 16+ since the config change is dramatic enough we’re instead investing in migrating to quarkus). resource monitoring for containers/DB look fine, nothing in slow query log, nothing obvious in app logs.

i can open a bug if it’s a better avenue, but wondered if anyone has seen similar behavior or can think of tuning which might help.

thank you in advance.

1 Like

Do the problem happen always? Or just happen for the first API call for the group?

Just my experience, I observed that, keycloak need to fire many SQL (sequentially) to retrieve data from DB (and then cache them in infinispan). Subsequent API call will have faster performance.

For the above case, you can turn on the hibernate sql logging to have a look.

thanks for the response! that would make sense, but we see it on every query consistently across production and test environments (doesn’t seem like warmup issue or environment-specific). same behavior from a couple different client libraries.

i am still doing DB analysis, hoping it’s something like an index or simple tunable. i’d say we are on the small side DB-wise, only ~150k users.

got hopeful when i found KEYCLOAK-18727 Improve user search query by artur-baltabayev · Pull Request #8346 · keycloak/keycloak · GitHub but that was slow for obvious reasons (full text search/table scan) and unrelated here. searching issues/PRs i wasn’t able to find anyone else complaining about this so it is interesting.

in case it sparks ideas, our stack is aws alb → ecs containers (keycloak 13.0.1, same behavior in 15.1.1) → aurora postgresql.