There are a number of issues around having a large number of realms. We have a general issue open to support this: https://issues.jboss.org/browse/KEYCLOAK-4593 We haven't prioritized this in the past, but that has changed and we would like to get this sorted out. There's a few more related PRs including the one you linked: https://github.com/keycloak/keycloak/pull/3557 https://github.com/keycloak/keycloak/pull/3561 On 10 May 2017 at 12:35, John D. Ament <john.d.ament(a)gmail.com&gt; wrote: > Hi, > > After enabling Keycloak and starting work on a multi-tenant application, > it > was noted that the admin console started to get very slow in keycloak. > After some searching around, it seemed like this was an already reported > issue [1] and a fix underway [2]. I was wondering if this fix would make > it into 3.2? > > If additional testing is needed, I'd be happy to help out. Deleting 161 > realms with minimal clients and users took me 15 minutes via the REST API. > > [1]: https://issues.jboss.org/browse/KEYCLOAK-4858 > [2]: https://github.com/keycloak/keycloak/pull/4095 > _______________________________________________ > keycloak-user mailing list > keycloak-user(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/keycloak-user >

Stian, We just got a report of a new issue, not sure if its related to the existing but I can create a ticket on your side if it makes sense. When accessing /auth/realms/master/protocol/openid-connect/token we are seeing 3k SQLs being executed of this format: select compositer0_.COMPOSITE as COMPOSIT1_16_0_, compositer0_.CHILD_ROLE as CHILD_RO2_16_0_, roleentity1_.ID as ID1_38_1_, roleentity1_.CLIENT as CLIENT8_38_1_, roleentity1_.CLIENT_REALM_CONSTRAINT as CLIENT_R2_38_1_, roleentity1_.CLIENT_ROLE as CLIENT_R3_38_1_, roleentity1_.DESCRIPTION as DESCRIPT4_38_1_, roleentity1_.NAME as NAME5_38_1_, roleentity1_.REALM as REALM9_38_1_, roleentity1_.REALM_ID as REALM_ID6_38_1_, roleentity1_.SCOPE_PARAM_REQUIRED as SCOPE_PA7_38_1_ from COMPOSITE_ROLE compositer0_ inner join KEYCLOAK_ROLE roleentity1_ on compositer0_.CHILD_ROLE=roleentity1_.ID where compositer0_.COMPOSITE=? On Wed, May 10, 2017 at 12:40 PM John D. Ament <john.d.ament(a)gmail.com&gt; wrote: > Stian, > > Good news. Glad to see these things get prioritized. So far they look > like they're matching the problems I'm running into, specifically around > the whoami endpoint and overall number of SQLs (2800 queries in one of my > tests) and the total number of DB connections allocated within that one > request (3200+). > > John > > > On Wed, May 10, 2017 at 8:02 AM Stian Thorgersen <sthorger(a)redhat.com&gt; > wrote: > >> There are a number of issues around having a large number of realms. We >> have a general issue open to support this: >> https://issues.jboss.org/browse/KEYCLOAK-4593 >> >> We haven't prioritized this in the past, but that has changed and we >> would like to get this sorted out. >> >> There's a few more related PRs including the one you linked: >> https://github.com/keycloak/keycloak/pull/3557 >> https://github.com/keycloak/keycloak/pull/3561 >> >> On 10 May 2017 at 12:35, John D. Ament <john.d.ament(a)gmail.com&gt; wrote: >> >>> Hi, >>> >>> After enabling Keycloak and starting work on a multi-tenant >>> application, it >>> was noted that the admin console started to get very slow in keycloak. >>> After some searching around, it seemed like this was an already reported >>> issue [1] and a fix underway [2]. I was wondering if this fix would >>> make >>> it into 3.2? >>> >>> If additional testing is needed, I'd be happy to help out. Deleting 161 >>> realms with minimal clients and users took me 15 minutes via the REST >>> API. >>> >>> [1]: https://issues.jboss.org/browse/KEYCLOAK-4858 >>> [2]: https://github.com/keycloak/keycloak/pull/4095 >>> >> _______________________________________________ >>> keycloak-user mailing list >>> keycloak-user(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/keycloak-user >>> >> >>

Stian, No, I don't believe its in that PR. This seems to be the table "CHILD_ROLE" which has a large number of queries being executed against it. But I'm not sure which entity that maps to in your persistence.xml https://github.com/keycloak/keycloak/blob/master/model/jpa/src/ main/resources/META-INF/persistence.xml John On Wed, May 24, 2017 at 3:54 AM Stian Thorgersen <sthorger(a)redhat.com&gt; wrote: > Sure, please create a JIRA and link it to https://issues.jboss.org/ > browse/KEYCLOAK-4593 > > Does this PR help: https://github.com/keycloak/keycloak/pull/3561? > > On 23 May 2017 at 15:04, John D. Ament <john.d.ament(a)gmail.com&gt; wrote: > >> Stian, >> >> We just got a report of a new issue, not sure if its related to the >> existing but I can create a ticket on your side if it makes sense. >> >> When accessing /auth/realms/master/protocol/openid-connect/token we are >> seeing 3k SQLs being executed of this format: >> >> select compositer0_.COMPOSITE as COMPOSIT1_16_0_, >> compositer0_.CHILD_ROLE as CHILD_RO2_16_0_, roleentity1_.ID as ID1_38_1_, >> roleentity1_.CLIENT as CLIENT8_38_1_, roleentity1_.CLIENT_REALM_CONSTRAINT >> as CLIENT_R2_38_1_, roleentity1_.CLIENT_ROLE as CLIENT_R3_38_1_, >> roleentity1_.DESCRIPTION as DESCRIPT4_38_1_, roleentity1_.NAME as >> NAME5_38_1_, roleentity1_.REALM as REALM9_38_1_, roleentity1_.REALM_ID as >> REALM_ID6_38_1_, roleentity1_.SCOPE_PARAM_REQUIRED as SCOPE_PA7_38_1_ >> from COMPOSITE_ROLE compositer0_ inner join KEYCLOAK_ROLE roleentity1_ on >> compositer0_.CHILD_ROLE=roleentity1_.ID where compositer0_.COMPOSITE=? >> >> On Wed, May 10, 2017 at 12:40 PM John D. Ament <john.d.ament(a)gmail.com&gt; >> wrote: >> >>> Stian, >>> >>> Good news. Glad to see these things get prioritized. So far they look >>> like they're matching the problems I'm running into, specifically around >>> the whoami endpoint and overall number of SQLs (2800 queries in one of my >>> tests) and the total number of DB connections allocated within that one >>> request (3200+). >>> >>> John >>> >>> >>> On Wed, May 10, 2017 at 8:02 AM Stian Thorgersen <sthorger(a)redhat.com&gt; >>> wrote: >>> >>>> There are a number of issues around having a large number of realms. >>>> We have a general issue open to support this: >>>> https://issues.jboss.org/browse/KEYCLOAK-4593 >>>> >>>> We haven't prioritized this in the past, but that has changed and we >>>> would like to get this sorted out. >>>> >>>> There's a few more related PRs including the one you linked: >>>> https://github.com/keycloak/keycloak/pull/3557 >>>> https://github.com/keycloak/keycloak/pull/3561 >>>> >>>> On 10 May 2017 at 12:35, John D. Ament <john.d.ament(a)gmail.com&gt; wrote: >>>> >>>>> Hi, >>>>> >>>>> After enabling Keycloak and starting work on a multi-tenant >>>>> application, it >>>>> was noted that the admin console started to get very slow in keycloak. >>>>> After some searching around, it seemed like this was an already >>>>> reported >>>>> issue [1] and a fix underway [2]. I was wondering if this fix would >>>>> make >>>>> it into 3.2? >>>>> >>>>> If additional testing is needed, I'd be happy to help out. Deleting >>>>> 161 >>>>> realms with minimal clients and users took me 15 minutes via the REST >>>>> API. >>>>> >>>>> [1]: https://issues.jboss.org/browse/KEYCLOAK-4858 >>>>> [2]: https://github.com/keycloak/keycloak/pull/4095 >>>>> >>>> _______________________________________________ >>>>> keycloak-user mailing list >>>>> keycloak-user(a)lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/keycloak-user >>>>> >>>> >>>> >

Ok,so I think I have a fix working, but one question I have is whether the existing PR for performance fixes will be getting merged in to 3.2? While its a different problem it touches a lot of the same areas so it will create some conflicts if either gets merged first. LIkewise if I have a fix for this, would you consider it part of 3.2? It also seems to me that there's an inherent problem with how some of the authorizations are done via Keycloak. Specifically, it seems that a client authenticated to master is getting the roles from all realms, which is really what is causing these problems. So while I can fix queries, without a fix in that area this type of problem can keep popping up. On Wed, May 24, 2017 at 9:42 AM Stian Thorgersen <sthorger(a)redhat.com&gt; wrote: > That's used by composite roles. It is probably invoked on all roles in the > realm. Could probably be fetched eagerly rather than lazy. Can you create a > JIRA please? > > On 24 May 2017 at 12:11, John D. Ament <john.d.ament(a)gmail.com&gt; wrote: > >> Stian, >> >> No, I don't believe its in that PR. This seems to be the table >> "CHILD_ROLE" which has a large number of queries being executed against >> it. But I'm not sure which entity that maps to in your persistence.xml >> https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/resou... >> >> John >> >> On Wed, May 24, 2017 at 3:54 AM Stian Thorgersen <sthorger(a)redhat.com&gt; >> wrote: >> >>> Sure, please create a JIRA and link it to >>> https://issues.jboss.org/browse/KEYCLOAK-4593 >>> >>> Does this PR help: https://github.com/keycloak/keycloak/pull/3561? >>> >>> On 23 May 2017 at 15:04, John D. Ament <john.d.ament(a)gmail.com&gt; wrote: >>> >>>> Stian, >>>> >>>> We just got a report of a new issue, not sure if its related to the >>>> existing but I can create a ticket on your side if it makes sense. >>>> >>>> When accessing /auth/realms/master/protocol/openid-connect/token we are >>>> seeing 3k SQLs being executed of this format: >>>> >>>> select compositer0_.COMPOSITE as COMPOSIT1_16_0_, >>>> compositer0_.CHILD_ROLE as CHILD_RO2_16_0_, roleentity1_.ID as ID1_38_1_, >>>> roleentity1_.CLIENT as CLIENT8_38_1_, roleentity1_.CLIENT_REALM_CONSTRAINT >>>> as CLIENT_R2_38_1_, roleentity1_.CLIENT_ROLE as CLIENT_R3_38_1_, >>>> roleentity1_.DESCRIPTION as DESCRIPT4_38_1_, roleentity1_.NAME as >>>> NAME5_38_1_, roleentity1_.REALM as REALM9_38_1_, roleentity1_.REALM_ID as >>>> REALM_ID6_38_1_, roleentity1_.SCOPE_PARAM_REQUIRED as SCOPE_PA7_38_1_ from >>>> COMPOSITE_ROLE compositer0_ inner join KEYCLOAK_ROLE roleentity1_ on >>>> compositer0_.CHILD_ROLE=roleentity1_.ID where compositer0_.COMPOSITE=? >>>> >>>> On Wed, May 10, 2017 at 12:40 PM John D. Ament <john.d.ament(a)gmail.com&gt; >>>> wrote: >>>> >>>>> Stian, >>>>> >>>>> Good news. Glad to see these things get prioritized. So far they >>>>> look like they're matching the problems I'm running into, specifically >>>>> around the whoami endpoint and overall number of SQLs (2800 queries in one >>>>> of my tests) and the total number of DB connections allocated within that >>>>> one request (3200+). >>>>> >>>>> John >>>>> >>>>> >>>>> On Wed, May 10, 2017 at 8:02 AM Stian Thorgersen <sthorger(a)redhat.com&gt; >>>>> wrote: >>>>> >>>>>> There are a number of issues around having a large number of realms. >>>>>> We have a general issue open to support this: >>>>>> https://issues.jboss.org/browse/KEYCLOAK-4593 >>>>>> >>>>>> We haven't prioritized this in the past, but that has changed and we >>>>>> would like to get this sorted out. >>>>>> >>>>>> There's a few more related PRs including the one you linked: >>>>>> https://github.com/keycloak/keycloak/pull/3557 >>>>>> https://github.com/keycloak/keycloak/pull/3561 >>>>>> >>>>>> On 10 May 2017 at 12:35, John D. Ament <john.d.ament(a)gmail.com&gt; >>>>>> wrote: >>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> After enabling Keycloak and starting work on a multi-tenant >>>>>>> application, it >>>>>>> was noted that the admin console started to get very slow in >>>>>>> keycloak. >>>>>>> After some searching around, it seemed like this was an already >>>>>>> reported >>>>>>> issue [1] and a fix underway [2]. I was wondering if this fix would >>>>>>> make >>>>>>> it into 3.2? >>>>>>> >>>>>>> If additional testing is needed, I'd be happy to help out. Deleting >>>>>>> 161 >>>>>>> realms with minimal clients and users took me 15 minutes via the >>>>>>> REST API. >>>>>>> >>>>>>> [1]: https://issues.jboss.org/browse/KEYCLOAK-4858 >>>>>>> [2]: https://github.com/keycloak/keycloak/pull/4095 >>>>>>> >>>>>> _______________________________________________ >>>>>>> keycloak-user mailing list >>>>>>> keycloak-user(a)lists.jboss.org >>>>>>> https://lists.jboss.org/mailman/listinfo/keycloak-user >>>>>>> >>>>>> _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user

Bill, Is this something for 3.2? If I had to guess, based on what I"m seeing, yes, this would fix the underlying issues. I'm assuming that the association to the realms involves loading all of the realms from the database, and specifically granting for all of the existing permissions? John On Thu, May 25, 2017 at 11:11 PM Bill Burke <bburke(a)redhat.com <mailto:bburke@redhat.com>> wrote: [keycloak-dev] See below thread. I think that this problem might be solved by the work I'm doing. I'm changing the admin console to not include roles in the token. The Admin REST API instead will see that the token was generated for the console client (by "aud" claim) and look up role mappings directly. I have to do this anyways because with the new fine grain admin permissions, I don't want admins to have to change the scope of the admin console client every time a new fine grain permission policy is specified. On 5/24/17 11:07 PM, John D. Ament wrote: > Ok,so I think I have a fix working, but one question I have is whether the > existing PR for performance fixes will be getting merged in to 3.2? While > its a different problem it touches a lot of the same areas so it will > create some conflicts if either gets merged first. LIkewise if I have a > fix for this, would you consider it part of 3.2? > > It also seems to me that there's an inherent problem with how some of the > authorizations are done via Keycloak. Specifically, it seems that a client > authenticated to master is getting the roles from all realms, which is > really what is causing these problems. So while I can fix queries, without > a fix in that area this type of problem can keep popping up. > > On Wed, May 24, 2017 at 9:42 AM Stian Thorgersen <sthorger(a)redhat.com <mailto:sthorger@redhat.com>> > wrote: > >> That's used by composite roles. It is probably invoked on all roles in the >> realm. Could probably be fetched eagerly rather than lazy. Can you create a >> JIRA please? >> >> On 24 May 2017 at 12:11, John D. Ament <john.d.ament(a)gmail.com <mailto:john.d.ament@gmail.com>> wrote: >> >>> Stian, >>> >>> No, I don't believe its in that PR. This seems to be the table >>> "CHILD_ROLE" which has a large number of queries being executed against >>> it. But I'm not sure which entity that maps to in your persistence.xml >>> https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/resou... >>> >>> John >>> >>> On Wed, May 24, 2017 at 3:54 AM Stian Thorgersen <sthorger(a)redhat.com <mailto:sthorger@redhat.com>> >>> wrote: >>> >>>> Sure, please create a JIRA and link it to >>>> https://issues.jboss.org/browse/KEYCLOAK-4593 >>>> >>>> Does this PR help: https://github.com/keycloak/keycloak/pull/3561? >>>> >>>> On 23 May 2017 at 15:04, John D. Ament <john.d.ament(a)gmail.com <mailto:john.d.ament@gmail.com>> wrote: >>>> >>>>> Stian, >>>>> >>>>> We just got a report of a new issue, not sure if its related to the >>>>> existing but I can create a ticket on your side if it makes sense. >>>>> >>>>> When accessing /auth/realms/master/protocol/openid-connect/token we are >>>>> seeing 3k SQLs being executed of this format: >>>>> >>>>> select compositer0_.COMPOSITE as COMPOSIT1_16_0_, >>>>> compositer0_.CHILD_ROLE as CHILD_RO2_16_0_, roleentity1_.ID as ID1_38_1_, >>>>> roleentity1_.CLIENT as CLIENT8_38_1_, roleentity1_.CLIENT_REALM_CONSTRAINT >>>>> as CLIENT_R2_38_1_, roleentity1_.CLIENT_ROLE as CLIENT_R3_38_1_, >>>>> roleentity1_.DESCRIPTION as DESCRIPT4_38_1_, roleentity1_.NAME as >>>>> NAME5_38_1_, roleentity1_.REALM as REALM9_38_1_, roleentity1_.REALM_ID as >>>>> REALM_ID6_38_1_, roleentity1_.SCOPE_PARAM_REQUIRED as SCOPE_PA7_38_1_ from >>>>> COMPOSITE_ROLE compositer0_ inner join KEYCLOAK_ROLE roleentity1_ on >>>>> compositer0_.CHILD_ROLE=roleentity1_.ID where compositer0_.COMPOSITE=? >>>>> >>>>> On Wed, May 10, 2017 at 12:40 PM John D. Ament <john.d.ament(a)gmail.com <mailto:john.d.ament@gmail.com>> >>>>> wrote: >>>>> >>>>>> Stian, >>>>>> >>>>>> Good news. Glad to see these things get prioritized. So far they >>>>>> look like they're matching the problems I'm running into, specifically >>>>>> around the whoami endpoint and overall number of SQLs (2800 queries in one >>>>>> of my tests) and the total number of DB connections allocated within that >>>>>> one request (3200+). >>>>>> >>>>>> John >>>>>> >>>>>> >>>>>> On Wed, May 10, 2017 at 8:02 AM Stian Thorgersen <sthorger(a)redhat.com <mailto:sthorger@redhat.com>> >>>>>> wrote: >>>>>> >>>>>>> There are a number of issues around having a large number of realms. >>>>>>> We have a general issue open to support this: >>>>>>> https://issues.jboss.org/browse/KEYCLOAK-4593 >>>>>>> >>>>>>> We haven't prioritized this in the past, but that has changed and we >>>>>>> would like to get this sorted out. >>>>>>> >>>>>>> There's a few more related PRs including the one you linked: >>>>>>> https://github.com/keycloak/keycloak/pull/3557 >>>>>>> https://github.com/keycloak/keycloak/pull/3561 >>>>>>> >>>>>>> On 10 May 2017 at 12:35, John D. Ament <john.d.ament(a)gmail.com <mailto:john.d.ament@gmail.com>> >>>>>>> wrote: >>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> After enabling Keycloak and starting work on a multi-tenant >>>>>>>> application, it >>>>>>>> was noted that the admin console started to get very slow in >>>>>>>> keycloak. >>>>>>>> After some searching around, it seemed like this was an already >>>>>>>> reported >>>>>>>> issue [1] and a fix underway [2]. I was wondering if this fix would >>>>>>>> make >>>>>>>> it into 3.2? >>>>>>>> >>>>>>>> If additional testing is needed, I'd be happy to help out. Deleting >>>>>>>> 161 >>>>>>>> realms with minimal clients and users took me 15 minutes via the >>>>>>>> REST API. >>>>>>>> >>>>>>>> [1]: https://issues.jboss.org/browse/KEYCLOAK-4858 >>>>>>>> [2]: https://github.com/keycloak/keycloak/pull/4095 >>>>>>>> >>>>>>> _______________________________________________ >>>>>>>> keycloak-user mailing list >>>>>>>> keycloak-user(a)lists.jboss.org <mailto:keycloak-user@lists.jboss.org> >>>>>>>> https://lists.jboss.org/mailman/listinfo/keycloak-user >>>>>>>> >>>>>>> > _______________________________________________ > keycloak-user mailing list > keycloak-user(a)lists.jboss.org <mailto:keycloak-user@lists.jboss.org> > https://lists.jboss.org/mailman/listinfo/keycloak-user _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org <mailto:keycloak-user@lists.jboss.org> https://lists.jboss.org/mailman/listinfo/keycloak-user