Hello, Keycloak OAuth plugin's presence generates a certain amount of records to pd_templates table on each action of Apiman Manager related to a Keycloak OAuth policy (even read-only ones like listing a plan's policies, etc.). Over time the number of records in pd_templates table can grow to milions resulting in Apiman Manager OoM exceptions. Given these records are basically just text hints it is really funny. A workaround is to periodically dedup the records. I guess this issue should be an easy fix. Affected version is: Apiman 1.2.9.Final + corresponding Keycloak OAuth plugin. _______________________________________________ Apiman-user mailing list Apiman-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/apiman-user

Oh, sorry I missed version info. psql (PostgreSQL) 9.4.12 JDBC connector - PostgreSQL 9.4.1212 Apiman 1.2.9.Final CentOS7 Oracle Java 1.8.0_111 No export/import was done prior this issue. I can say I've been observed slower and slower responses during operation with the Manager OAuth related pages. The trigger action seems to be a GET on a URL pattern like this: apimanui/api-manager/orgs/MyOrg/plans/MyOrgKeycloakOAuth/1/policies/1155 Resulting screen loads a policy config scheme from Keycloak OAuth Plugin and displays it. It also generates N^2 new rows in pd_templates. I've observed 16, 32, 64, 128 and then 256 added new rows... Thanks On Fri, Jun 2, 2017 at 12:26 PM, Marc Savy <marc.savy(a)redhat.com&gt; wrote: > Hi Andy, > > I spent time yesterday evening trying to replicate this after our chat > on IRC, but I haven't been able to trigger it. Perhaps with more > information we can narrow this down. > > Which version of Postgres are you using? Which driver version? > > I've been using: Postgres 9.6.2, Driver 42.1.1, apiman 1.3.0.Final > (should be same as 1.2.9.Final for this). > > Have you done any export-import? > > Have you observed the precise action that corresponds with the > extraneous pd_templates entries being inserted? > > Regards, > Marc > > On 2 June 2017 at 08:05, Andy Yar <andyyar66(a)gmail.com&gt; wrote: >> >> Hello, >> Keycloak OAuth plugin's presence generates a certain amount of records >> to pd_templates table on each action of Apiman Manager related to a >> Keycloak OAuth policy (even read-only ones like listing a plan's >> policies, etc.). >> >> Over time the number of records in pd_templates table can grow to >> milions resulting in Apiman Manager OoM exceptions. Given these >> records are basically just text hints it is really funny. >> >> A workaround is to periodically dedup the records. >> >> I guess this issue should be an easy fix. >> >> Affected version is: Apiman 1.2.9.Final + corresponding Keycloak OAuth plugin. >> _______________________________________________ >> Apiman-user mailing list >> Apiman-user(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/apiman-user

Hi Andy, Can you describe how your API, ClientApp & Plan is set up? e.g. API = [Keycloak OAuth2 Policy] Plan = [<nothing>] ClientApp = [Header Policy] Regards, Marc On 2 June 2017 at 12:20, Andy Yar <andyyar66(a)gmail.com&gt; wrote: > Oh, sorry I missed version info. > > psql (PostgreSQL) 9.4.12 > JDBC connector - PostgreSQL 9.4.1212 > Apiman 1.2.9.Final > CentOS7 > Oracle Java 1.8.0_111 > > No export/import was done prior this issue. > > I can say I've been observed slower and slower responses during > operation with the Manager OAuth related pages. > > The trigger action seems to be a GET on a URL pattern like this: > apimanui/api-manager/orgs/MyOrg/plans/MyOrgKeycloakOAuth/1/policies/1155 > > Resulting screen loads a policy config scheme from Keycloak OAuth > Plugin and displays it. It also generates N^2 new rows in > pd_templates. I've observed 16, 32, 64, 128 and then 256 added new > rows... > > Thanks > > > On Fri, Jun 2, 2017 at 12:26 PM, Marc Savy <marc.savy(a)redhat.com&gt; wrote: >> Hi Andy, >> >> I spent time yesterday evening trying to replicate this after our chat >> on IRC, but I haven't been able to trigger it. Perhaps with more >> information we can narrow this down. >> >> Which version of Postgres are you using? Which driver version? >> >> I've been using: Postgres 9.6.2, Driver 42.1.1, apiman 1.3.0.Final >> (should be same as 1.2.9.Final for this). >> >> Have you done any export-import? >> >> Have you observed the precise action that corresponds with the >> extraneous pd_templates entries being inserted? >> >> Regards, >> Marc >> >> On 2 June 2017 at 08:05, Andy Yar <andyyar66(a)gmail.com&gt; wrote: >>> >>> Hello, >>> Keycloak OAuth plugin's presence generates a certain amount of records >>> to pd_templates table on each action of Apiman Manager related to a >>> Keycloak OAuth policy (even read-only ones like listing a plan's >>> policies, etc.). >>> >>> Over time the number of records in pd_templates table can grow to >>> milions resulting in Apiman Manager OoM exceptions. Given these >>> records are basically just text hints it is really funny. >>> >>> A workaround is to periodically dedup the records. >>> >>> I guess this issue should be an easy fix. >>> >>> Affected version is: Apiman 1.2.9.Final + corresponding Keycloak OAuth plugin. >>> _______________________________________________ >>> Apiman-user mailing list >>> Apiman-user(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/apiman-user _______________________________________________ Apiman-user mailing list Apiman-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/apiman-user

I've enabled logging of org.hibernate.SQL. Now I can see inserts to the "pd_templates" in my server.log: https://hastebin.com/zovefusoyo.sql. These are triggered when browsing an OAuth policy. The "policydefs" table seems to be OK - only a single record for the Keycloak OAuth. On Fri, Jun 2, 2017 at 2:50 PM, Eric Wittmann <eric.wittmann(a)redhat.com&gt; wrote: > This is a very strange problem. I'll add some technical info to this > discussion in case it helps. > > The "pd_templates" table stores potentially multiple html templates for > displaying the *summary* of a configured instance of a policy. It can store > multiple templates by language - this is basically an i18n feature. In > practice, we only really have English language versions, so there should > only be one row in that table for each row in the "policydefs" table. The > "policydefs" table should have one row per policy definition (basically one > row for each item in the Policy Type drop-down when adding a new policy to > an API). > > The ORM class for the pd_templates table is here: > > https://github.com/apiman/apiman/blob/master/manager/ api/beans/src/main/java/io/apiman/manager/api/beans/policies/ PolicyDefinitionTemplateBean.java > > And the policy definition ORM bean is here: > > https://github.com/apiman/apiman/blob/master/manager/ api/beans/src/main/java/io/apiman/manager/api/beans/ policies/PolicyDefinitionBean.java > > This sounds like hibernate making a mess of things (or more likely my > mis-understanding of something hibernate is doing). It's one of the reasons > I'm on the fence about ORM technologies in general. They can be so helpful, > but if you wrote your own SQL statements you'd never have a weird issue like > this. > > In any case, at this point I have no theory about how this could be > happening. > > I assume your "policydefs" table is sensible. So the question is why > hibernate would be duplicating the data in "pd_templates", especially when > doing a simple GET of the information (shouldn't be mutating anything!). > > Could try enabling SQL logging in hibernate to see what's going on. > > But of course trying to replicate the problem locally is always the best > approach. :) > > -Eric > > > On Fri, Jun 2, 2017 at 7:49 AM, Marc Savy <marc.savy(a)redhat.com&gt; wrote: >> >> Hi Andy, >> >> Can you describe how your API, ClientApp & Plan is set up? >> >> e.g. >> >> API = [Keycloak OAuth2 Policy] >> Plan = [<nothing>] >> ClientApp = [Header Policy] >> >> >> Regards, >> Marc >> >> On 2 June 2017 at 12:20, Andy Yar <andyyar66(a)gmail.com&gt; wrote: >> > Oh, sorry I missed version info. >> > >> > psql (PostgreSQL) 9.4.12 >> > JDBC connector - PostgreSQL 9.4.1212 >> > Apiman 1.2.9.Final >> > CentOS7 >> > Oracle Java 1.8.0_111 >> > >> > No export/import was done prior this issue. >> > >> > I can say I've been observed slower and slower responses during >> > operation with the Manager OAuth related pages. >> > >> > The trigger action seems to be a GET on a URL pattern like this: >> > apimanui/api-manager/orgs/MyOrg/plans/MyOrgKeycloakOAuth/1/policies/ 1155 >> > >> > Resulting screen loads a policy config scheme from Keycloak OAuth >> > Plugin and displays it. It also generates N^2 new rows in >> > pd_templates. I've observed 16, 32, 64, 128 and then 256 added new >> > rows... >> > >> > Thanks >> > >> > >> > On Fri, Jun 2, 2017 at 12:26 PM, Marc Savy <marc.savy(a)redhat.com&gt; wrote: >> >> Hi Andy, >> >> >> >> I spent time yesterday evening trying to replicate this after our chat >> >> on IRC, but I haven't been able to trigger it. Perhaps with more >> >> information we can narrow this down. >> >> >> >> Which version of Postgres are you using? Which driver version? >> >> >> >> I've been using: Postgres 9.6.2, Driver 42.1.1, apiman 1.3.0.Final >> >> (should be same as 1.2.9.Final for this). >> >> >> >> Have you done any export-import? >> >> >> >> Have you observed the precise action that corresponds with the >> >> extraneous pd_templates entries being inserted? >> >> >> >> Regards, >> >> Marc >> >> >> >> On 2 June 2017 at 08:05, Andy Yar <andyyar66(a)gmail.com&gt; wrote: >> >>> >> >>> Hello, >> >>> Keycloak OAuth plugin's presence generates a certain amount of records >> >>> to pd_templates table on each action of Apiman Manager related to a >> >>> Keycloak OAuth policy (even read-only ones like listing a plan's >> >>> policies, etc.). >> >>> >> >>> Over time the number of records in pd_templates table can grow to >> >>> milions resulting in Apiman Manager OoM exceptions. Given these >> >>> records are basically just text hints it is really funny. >> >>> >> >>> A workaround is to periodically dedup the records. >> >>> >> >>> I guess this issue should be an easy fix. >> >>> >> >>> Affected version is: Apiman 1.2.9.Final + corresponding Keycloak OAuth >> >>> plugin. >> >>> _______________________________________________ >> >>> Apiman-user mailing list >> >>> Apiman-user(a)lists.jboss.org >> >>> https://lists.jboss.org/mailman/listinfo/apiman-user >> _______________________________________________ >> Apiman-user mailing list >> Apiman-user(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/apiman-user > >

Great thanks! Now we just need to figure out why the heck that would happen... Perhaps the ORM gods are angry. An offering will be required. Note: I wonder what would happen if we put a unique constraint on pd_templates (combination of id and lang columns). That could be a workaround until we found the root cause. -Eric On Tue, Jun 6, 2017 at 2:37 AM, Andy Yar <andyyar66(a)gmail.com&gt; wrote: > I've enabled logging of org.hibernate.SQL. Now I can see inserts to > the "pd_templates" in my server.log: > https://hastebin.com/zovefusoyo.sql. These are triggered when browsing > an OAuth policy. > > The "policydefs" table seems to be OK - only a single record for the > Keycloak OAuth. > > On Fri, Jun 2, 2017 at 2:50 PM, Eric Wittmann <eric.wittmann(a)redhat.com&gt; > wrote: > > This is a very strange problem. I'll add some technical info to this > > discussion in case it helps. > > > > The "pd_templates" table stores potentially multiple html templates for > > displaying the *summary* of a configured instance of a policy. It can > store > > multiple templates by language - this is basically an i18n feature. In > > practice, we only really have English language versions, so there should > > only be one row in that table for each row in the "policydefs" table. > The > > "policydefs" table should have one row per policy definition (basically > one > > row for each item in the Policy Type drop-down when adding a new policy > to > > an API). > > > > The ORM class for the pd_templates table is here: > > > > https://github.com/apiman/apiman/blob/master/manager/api/ > beans/src/main/java/io/apiman/manager/api/beans/policies/Pol > icyDefinitionTemplateBean.java > > > > And the policy definition ORM bean is here: > > > > https://github.com/apiman/apiman/blob/master/manager/api/ > beans/src/main/java/io/apiman/manager/api/beans/policies/ > PolicyDefinitionBean.java > > > > This sounds like hibernate making a mess of things (or more likely my > > mis-understanding of something hibernate is doing). It's one of the > reasons > > I'm on the fence about ORM technologies in general. They can be so > helpful, > > but if you wrote your own SQL statements you'd never have a weird issue > like > > this. > > > > In any case, at this point I have no theory about how this could be > > happening. > > > > I assume your "policydefs" table is sensible. So the question is why > > hibernate would be duplicating the data in "pd_templates", especially > when > > doing a simple GET of the information (shouldn't be mutating anything!). > > > > Could try enabling SQL logging in hibernate to see what's going on. > > > > But of course trying to replicate the problem locally is always the best > > approach. :) > > > > -Eric > > > > > > On Fri, Jun 2, 2017 at 7:49 AM, Marc Savy <marc.savy(a)redhat.com&gt; wrote: > >> > >> Hi Andy, > >> > >> Can you describe how your API, ClientApp & Plan is set up? > >> > >> e.g. > >> > >> API = [Keycloak OAuth2 Policy] > >> Plan = [<nothing>] > >> ClientApp = [Header Policy] > >> > >> > >> Regards, > >> Marc > >> > >> On 2 June 2017 at 12:20, Andy Yar <andyyar66(a)gmail.com&gt; wrote: > >> > Oh, sorry I missed version info. > >> > > >> > psql (PostgreSQL) 9.4.12 > >> > JDBC connector - PostgreSQL 9.4.1212 > >> > Apiman 1.2.9.Final > >> > CentOS7 > >> > Oracle Java 1.8.0_111 > >> > > >> > No export/import was done prior this issue. > >> > > >> > I can say I've been observed slower and slower responses during > >> > operation with the Manager OAuth related pages. > >> > > >> > The trigger action seems to be a GET on a URL pattern like this: > >> > apimanui/api-manager/orgs/MyOrg/plans/MyOrgKeycloakOAuth/1/ > policies/1155 > >> > > >> > Resulting screen loads a policy config scheme from Keycloak OAuth > >> > Plugin and displays it. It also generates N^2 new rows in > >> > pd_templates. I've observed 16, 32, 64, 128 and then 256 added new > >> > rows... > >> > > >> > Thanks > >> > > >> > > >> > On Fri, Jun 2, 2017 at 12:26 PM, Marc Savy <marc.savy(a)redhat.com&gt; > wrote: > >> >> Hi Andy, > >> >> > >> >> I spent time yesterday evening trying to replicate this after our > chat > >> >> on IRC, but I haven't been able to trigger it. Perhaps with more > >> >> information we can narrow this down. > >> >> > >> >> Which version of Postgres are you using? Which driver version? > >> >> > >> >> I've been using: Postgres 9.6.2, Driver 42.1.1, apiman 1.3.0.Final > >> >> (should be same as 1.2.9.Final for this). > >> >> > >> >> Have you done any export-import? > >> >> > >> >> Have you observed the precise action that corresponds with the > >> >> extraneous pd_templates entries being inserted? > >> >> > >> >> Regards, > >> >> Marc > >> >> > >> >> On 2 June 2017 at 08:05, Andy Yar <andyyar66(a)gmail.com&gt; wrote: > >> >>> > >> >>> Hello, > >> >>> Keycloak OAuth plugin's presence generates a certain amount of > records > >> >>> to pd_templates table on each action of Apiman Manager related to a > >> >>> Keycloak OAuth policy (even read-only ones like listing a plan's > >> >>> policies, etc.). > >> >>> > >> >>> Over time the number of records in pd_templates table can grow to > >> >>> milions resulting in Apiman Manager OoM exceptions. Given these > >> >>> records are basically just text hints it is really funny. > >> >>> > >> >>> A workaround is to periodically dedup the records. > >> >>> > >> >>> I guess this issue should be an easy fix. > >> >>> > >> >>> Affected version is: Apiman 1.2.9.Final + corresponding Keycloak > OAuth > >> >>> plugin. > >> >>> _______________________________________________ > >> >>> Apiman-user mailing list > >> >>> Apiman-user(a)lists.jboss.org > >> >>> https://lists.jboss.org/mailman/listinfo/apiman-user > >> _______________________________________________ > >> Apiman-user mailing list > >> Apiman-user(a)lists.jboss.org > >> https://lists.jboss.org/mailman/listinfo/apiman-user > > > > >