From a quick glance this seems like a authz services issue - Pedro can you

Thanks for your feedback. Yes, I am aware of adding an index manually will resolve this issue and we will do that. However, I think that these kind of indexes should come with Keycloak automatically and not be required to add manually. Are there any official view on this issue? Is it expected that we should add indexes ourselves? I would like to point out that the same issue seems to cause long response times when saving client configuration in the admin UI when you have a few hundred thousands of users in the user store. - Johannes On Thu, Sep 19, 2019 at 7:44 PM Ioan Eugen Stan <ieugen(a)netdava.com&gt; wrote: > > Hi, > > You probably did it but I am going to say it anyway. You can add an index manually without issues. The index is transparent and probably will not aftect inserts. this is a workaround. Very nice of you for reporting it. > > I don't know if/how keycloak handles the DB details => are there guides on how to handle situations like the above, in the db. > > Personally I chose to embrace SQL and prefer it to Java whenever possible. > > > > Eugen Stan > Netdava International > > > Mesaj original > > > > De la: johannes(a)kodet.no > Trimis: 19 septembrie 2019 20:26 > Către: keycloak-dev(a)lists.jboss.org > Subiect: [keycloak-dev] Cache empty result for client service account user lookup > > > We have a performance issue with the token exchange service in a realm > with a large amount of users. > > The problem seems to be that token exchange uses client permissions to > evaluate if a client to client token exchange is allowed. The client > permissions evaluator > (org.keycloak.services.resources.admin.permissions.ClientPermissions#canExchangeTo) > creates a new ClientModelIdentity object which in the constructor also > loads the client's service account user model, if one exists. > The service account user lookup result for clients without service > account enabled, isn't cached. Since the service_account_client_link > column in the user_entity database table isn't indexed, this lookup > causes a table scan which takes several seconds for each token > exchange. > > One way to solve this, is to cache the empty service account user lookup. > I have pushed a proof of concept at > https://github.com/keycloak/keycloak/compare/master...knutz3n:cache-empty... > > Is this something you would consider merging? Do you have suggestions > for improvement on this fix? Do you know of other places which must > trigger an eviction in addition to > org.keycloak.services.managers.ClientManager#enableServiceAccount? > > - Johannes > _______________________________________________ > keycloak-dev mailing list > keycloak-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/keycloak-dev _______________________________________________ keycloak-dev mailing list keycloak-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-dev

Hi, On 26.09.2019 13:01, Stian Thorgersen wrote: > From a quick glance this seems like a authz services issue - Pedro can > you take a look? > > It should not be necessary to add custom indexes, in fact we recommend > against that as it may cause issues with db migration if custom dB > schema have been applied. Could you pleae elaborate the statement: "we recommend against that as it may cause issues with db migration if custom dB schema have been applied" ? What kind of issues are you thinking about?

Regards, Eugen > > On Wed, 25 Sep 2019, 10:33 Johannes Knutsen, <johannes(a)kodet.no > <mailto:johannes@kodet.no>> wrote: > > Thanks for your feedback. > Yes, I am aware of adding an index manually will resolve this issue > and we will do that. However, I think that these kind of indexes > should come with Keycloak automatically and not be required to add > manually. > > Are there any official view on this issue? Is it expected that we > should add indexes ourselves? > I would like to point out that the same issue seems to cause long > response times when saving client configuration in the admin UI when > you have a few hundred thousands of users in the user store. > > - Johannes > > On Thu, Sep 19, 2019 at 7:44 PM Ioan Eugen Stan > <ieugen(a)netdava.com <mailto:ieugen@netdava.com>> wrote: > > > > Hi, > > > > You probably did it but I am going to say it anyway. You can add > an index manually without issues. The index is transparent and > probably will not aftect inserts. this is a workaround. Very nice > of you for reporting it. > > > > I don't know if/how keycloak handles the DB details => are there > guides on how to handle situations like the above, in the db. > > > > Personally I chose to embrace SQL and prefer it to Java whenever > possible. > > > > > > > > Eugen Stan > > Netdava International > > > > > > Mesaj original > > > > > > > > De la: johannes(a)kodet.no <mailto:johannes@kodet.no> > > Trimis: 19 septembrie 2019 20:26 > > Către: keycloak-dev(a)lists.jboss.org > <mailto:keycloak-dev@lists.jboss.org> > > Subiect: [keycloak-dev] Cache empty result for client service > account user lookup > > > > > > We have a performance issue with the token exchange service in a > realm > > with a large amount of users. > > > > The problem seems to be that token exchange uses client > permissions to > > evaluate if a client to client token exchange is allowed. The client > > permissions evaluator > > > (org.keycloak.services.resources.admin.permissions.ClientPermissions#canExchangeTo) > > creates a new ClientModelIdentity object which in the > constructor also > > loads the client's service account user model, if one exists. > > The service account user lookup result for clients without service > > account enabled, isn't cached. Since the service_account_client_link > > column in the user_entity database table isn't indexed, this lookup > > causes a table scan which takes several seconds for each token > > exchange. > > > > One way to solve this, is to cache the empty service account > user lookup. > > I have pushed a proof of concept at > > > https://github.com/keycloak/keycloak/compare/master...knutz3n:cache-empty... > > > > Is this something you would consider merging? Do you have > suggestions > > for improvement on this fix? Do you know of other places which must > > trigger an eviction in addition to > > org.keycloak.services.managers.ClientManager#enableServiceAccount? > > > > - Johannes > > _______________________________________________ > > keycloak-dev mailing list > > keycloak-dev(a)lists.jboss.org <mailto:keycloak-dev@lists.jboss.org> > > https://lists.jboss.org/mailman/listinfo/keycloak-dev > > _______________________________________________ > keycloak-dev mailing list > keycloak-dev(a)lists.jboss.org <mailto:keycloak-dev@lists.jboss.org> > https://lists.jboss.org/mailman/listinfo/keycloak-dev >