Hello,
I’m working on migrating our authorization over from a custom solution to Keycloak UMA. However, locally after I have migrated ~120k resources over with ~475k policies. When I now try to exchange an access token+permission ticket for an RPT token using POST /auth/realms/${keycloak_realm}/protocol/openid-connect/token with grant-type uma-ticket, the 1st call is taking ~15-30 seconds to complete. Has anyone else seen this behavior?
This is with no load on the keycloak server and it’s the only request happening. I’m running Keycloak 4.8.3.Final and MySQL 5.6.45 Community Edition w/INNODB. My local machine is a late model i7 hex core w/32GiB of RAM and m.2 SSD.
Watching the docker stats while the request is running shows that the MySQL container is pegging the CPU. When I run mysql performance and profiling, it’s showing a query that is doing a full table scan and dominating the performance of MySQL. I’m guessing the query is created via an ORM because it looks to contain some pretty gnarly predicates and joins. When I run the explain on the query nothing is jumping out. However, the performance numbers are showing a tong of rows being scanned. I tried manually creating a couple of indexes but that did not seem to help.
I’ve attached the visual explain and the query below. Any help would be appreciated.
select
policyenti0_.ID as ID1_65_0_,
resourceen2_.ID as ID1_66_1_,
scopeentit4_.ID as ID1_67_2_,
policyenti6_.ID as ID1_65_3_,
policyenti0_.DECISION_STRATEGY as DECISION2_65_0_,
policyenti0_.DESCRIPTION as DESCRIPT3_65_0_,
policyenti0_.LOGIC as LOGIC4_65_0_,
policyenti0_.NAME as NAME5_65_0_,
policyenti0_.OWNER as OWNER6_65_0_,
policyenti0_.RESOURCE_SERVER_ID as RESOURCE8_65_0_,
policyenti0_.TYPE as TYPE7_65_0_,
resourceen2_.DISPLAY_NAME as DISPLAY_2_66_1_,
resourceen2_.ICON_URI as ICON_URI3_66_1_,
resourceen2_.NAME as NAME4_66_1_,
resourceen2_.OWNER as OWNER5_66_1_,
resourceen2_.OWNER_MANAGED_ACCESS as OWNER_MA6_66_1_,
resourceen2_.RESOURCE_SERVER_ID as RESOURCE8_66_1_,
resourceen2_.TYPE as TYPE7_66_1_,
resources1_.POLICY_ID as POLICY_I1_61_0__,
resources1_.RESOURCE_ID as RESOURCE2_61_0__,
scopeentit4_.DISPLAY_NAME as DISPLAY_2_67_2_,
scopeentit4_.ICON_URI as ICON_URI3_67_2_,
scopeentit4_.NAME as NAME4_67_2_,
scopeentit4_.RESOURCE_SERVER_ID as RESOURCE5_67_2_,
scopes3_.POLICY_ID as POLICY_I1_71_1__,
scopes3_.SCOPE_ID as SCOPE_ID2_71_1__,
policyenti6_.DECISION_STRATEGY as DECISION2_65_3_,
policyenti6_.DESCRIPTION as DESCRIPT3_65_3_,
policyenti6_.LOGIC as LOGIC4_65_3_,
policyenti6_.NAME as NAME5_65_3_,
policyenti6_.OWNER as OWNER6_65_3_,
policyenti6_.RESOURCE_SERVER_ID as RESOURCE8_65_3_,
policyenti6_.TYPE as TYPE7_65_3_,
associated5_.POLICY_ID as POLICY_I1_1_2__,
associated5_.ASSOCIATED_POLICY_ID as ASSOCIAT2_1_2__
from
RESOURCE_SERVER_POLICY policyenti0_ left outer join
RESOURCE_POLICY resources1_ on policyenti0_.ID=resources1_.POLICY_ID left outer join
RESOURCE_SERVER_RESOURCE resourceen2_ on resources1_.RESOURCE_ID=resourceen2_.ID inner join
SCOPE_POLICY scopes3_ on policyenti0_.ID=scopes3_.POLICY_ID inner join
RESOURCE_SERVER_SCOPE scopeentit4_ on scopes3_.SCOPE_ID=scopeentit4_.ID inner join
ASSOCIATED_POLICY associated5_ on policyenti0_.ID=associated5_.POLICY_ID inner join
RESOURCE_SERVER_POLICY policyenti6_ on associated5_.ASSOCIATED_POLICY_ID=policyenti6_.ID
where
policyenti0_.RESOURCE_SERVER_ID='f75754d9-4c13-4710-b358-7aea027c50c4' and
(exists (select policyenti9_.ID from RESOURCE_SERVER_SCOPE scopeentit7_ inner join SCOPE_POLICY policies8_ on scopeentit7_.ID=policies8_.SCOPE_ID inner join RESOURCE_SERVER_POLICY policyenti9_ on policies8_.POLICY_ID=policyenti9_.ID where scopeentit7_.RESOURCE_SERVER_ID='f75754d9-4c13-4710-b358-7aea027c50c4' and policyenti9_.RESOURCE_SERVER_ID='f75754d9-4c13-4710-b358-7aea027c50c4' and policyenti9_.ID=policyenti0_.ID and policyenti9_.TYPE='scope' and (scopeentit7_.ID in ('1fd80023-ee52-48bb-a18c-79762c308026')))) and
not (exists (select resourceen11_.ID from RESOURCE_POLICY resources10_, RESOURCE_SERVER_RESOURCE resourceen11_ where policyenti0_.ID=resources10_.POLICY_ID and resources10_.RESOURCE_ID=resourceen11_.ID));