DELETE /realms/:realm/orgs/:orgId takes ages (seconds)

[mkyc]

When calling delete organization with this method it takes about 2.5 seconds to delete single organization on my installation while any other operation takes milliseconds. Running it in parallel doesn't change that value very much, it just takes longer for each request to finish (10 threads mean that requests will take ~8 times longer).

Connecting it with behavior that you cannot delete realm that contains organizations it makes any realm creation / deletion automation pretty hard if you have enough organizations (couple thousands in tests in my case).

PH2 version: quay.io/phasetwo/phasetwo-keycloak:24.0.5

[xgp]

@mkyc How many organizations do you have? How many members per organization? How many identity providers?

I don't see the same behavior, but it may be due to different data.

[mkyc]

in example test realm I had:

• over 6k organizations
• lets say about 200 members per organization (but it's hard to count for me right now)
• 3 identity providers

[xgp]

Thanks for the detail. Is that 3 identity providers per organization?

I ask because this line in the delete event handler: https://github.com/p2-inc/keycloak-orgs/blob/main/src/main/java/io/phasetwo/service/resource/OrganizationResourceProviderFactory.java#L218

Has a lazy approach that iterates through all of the identity providers in the system: https://github.com/p2-inc/keycloak-orgs/blob/main/src/main/java/io/phasetwo/service/model/jpa/OrganizationAdapter.java#L322-L323

in order to find the ones for that organization.

[mkyc]

Nope. Inside organization there is no identity provider. Those are on realm level.

One more thing I can think of which might influence that behavior is that I have Infinispan cache defined between two nodes for HA.

[xgp]

Nothing happens related to orgs in infinispan. It is a strange behavior, as deleting one row, and a few hundred foreign key records shouldn't be a big deal.

However, how often do you plan to actually be deleting orgs? I know it's not an ideal performance characteristic, but is it going to happen often?

[mkyc]

In general that is tests related issue.

We have number of test and dev environments. Each environment gets its own realm. Development and qa operations introduce some weird stuff into that environments (which is expected and understandable). Every some time someone decides, that this environment is "too broken" and we basically delete it and create new one.

Due to CI processes we have sometimes thousands of organizations in realm, so that removal time issue is noticeable.

[xgp]

@mkyc Thanks for the description. We're not really targeting such test use cases, so I doubt we will spend the time to debug this one. I'll leave it open if you have interest in trying to optimize the delete time. My guess is Hibernate is doing something naive like iterating through all the members and running an individual SQL DELETE. I'd suggest starting with running your tests with Hibernate set to DEBUG logging so you can see what's happening on a delete. Might be some JPA magic you could do to improve the performance.