The steps to reproduce is to use the keycloak admin API to generate multiple realms in parallel. Note that it not always reproduced. Simple defensive solution might be to add constraint to the table, not sure regrading performance impact. From: Stian Thorgersen [mailto:sthorger@redhat.com] Sent: Monday, January 02, 2017 4:33 PM To: Haim Vana <haimv(a)perfectomobile.com&gt; Cc: keycloak-user(a)lists.jboss.org; Moshe Ben-Shoham <mosheb(a)perfectomobile.com>; Boaz Hamo <boazh(a)perfectomobile.com>; Michael Dikman <michaeld(a)perfectomobile.com&gt; Subject: Re: [keycloak-user] COMPOSITE_ROLE table duplicate rows issue Strange. If you can provide steps to reproduce it we can look into it. Ideally a testcase within our existing testsuite. On 27 December 2016 at 15:53, Haim Vana <haimv@perfectomobile.com<mailto:haimv@perfectomobile.com>> wrote: Hi, We found an issue with the COMPOSITE_ROLE DB table, the issue might have occurred when creating multiple realms in parallel. We noticed that create realm API fails on timeout and DB showed locks on table COMPOSITE_ROLE. Further investigation revealed that the COMPOSITE_ROLE table contains a lot of duplicate rows, instead of about 4000 rows there were over a million rows. Deleting the duplicate rows solved the issue. Any idea what might have caused the duplicated rows ? or how to prevent it ? Also we have about 4000 rows in the COMPOSITE_ROLE row, does it make sense for about 160 realms ? (maybe we need to do some cleanup) Thanks, Haim. The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. _______________________________________________ keycloak-user mailing list keycloak-user@lists.jboss.org<mailto:keycloak-user@lists.jboss.org> https://lists.jboss.org/mailman/listinfo/keycloak-user<https://emea01.... The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.

Thanks for the quick response. We are using your multi-tenancy support (realm for each customer) since we must have separate definitions, different admin user and other attributes for each customer – hence we can't really change that. Can you please elaborate about the performance issues ? is it only within the keycloak UI or also when performing login and generating offline/access tokens via REST ? In addition note that we are not using a single server, we have AWS cluster with 2 active machines (master-master) with shared postgresql DB, Does the performance issues still applies in this architecture ? if so any idea how we can improve it ? (e.g. adding more machines, replace the DB to Mongo if possible, etc) Also what is the recommended number of realms for that kind of architecture ? (currently we have about 207 realms and growing) Thanks again, Haim. From: Stian Thorgersen [mailto:sthorger@redhat.com] Sent: Tuesday, January 03, 2017 7:49 AM To: Haim Vana <haimv(a)perfectomobile.com&gt; Cc: keycloak-user(a)lists.jboss.org; Moshe Ben-Shoham <mosheb(a)perfectomobile.com>; Boaz Hamo <boazh(a)perfectomobile.com>; Michael Dikman <michaeld(a)perfectomobile.com&gt; Subject: Re: [keycloak-user] COMPOSITE_ROLE table duplicate rows issue You can create a bug report with the steps to reproduce. We can't really prioritize it though as we don't really test or recommend using that many realms on a single server. There are known performance impacts of having many realms (quite a few PRs around this atm that we'll look at merging in 3.x) and also some fundamental reasons why it's not quite right (master realm and the composite roles mainly). On 2 January 2017 at 16:26, Haim Vana <haimv@perfectomobile.com<mailto:haimv@perfectomobile.com>> wrote: The steps to reproduce is to use the keycloak admin API to generate multiple realms in parallel. Note that it not always reproduced. Simple defensive solution might be to add constraint to the table, not sure regrading performance impact. From: Stian Thorgersen [mailto:sthorger@redhat.com<mailto:sthorger@redhat.com>] Sent: Monday, January 02, 2017 4:33 PM To: Haim Vana <haimv@perfectomobile.com<mailto:haimv@perfectomobile.com>> Cc: keycloak-user@lists.jboss.org<mailto:keycloak-user@lists.jboss.org>; Moshe Ben-Shoham <mosheb@perfectomobile.com<mailto:mosheb@perfectomobile.com>>; Boaz Hamo <boazh@perfectomobile.com<mailto:boazh@perfectomobile.com>>; Michael Dikman <michaeld@perfectomobile.com<mailto:michaeld@perfectomobile.com>> Subject: Re: [keycloak-user] COMPOSITE_ROLE table duplicate rows issue Strange. If you can provide steps to reproduce it we can look into it. Ideally a testcase within our existing testsuite. On 27 December 2016 at 15:53, Haim Vana <haimv@perfectomobile.com<mailto:haimv@perfectomobile.com>> wrote: Hi, We found an issue with the COMPOSITE_ROLE DB table, the issue might have occurred when creating multiple realms in parallel. We noticed that create realm API fails on timeout and DB showed locks on table COMPOSITE_ROLE. Further investigation revealed that the COMPOSITE_ROLE table contains a lot of duplicate rows, instead of about 4000 rows there were over a million rows. Deleting the duplicate rows solved the issue. Any idea what might have caused the duplicated rows ? or how to prevent it ? Also we have about 4000 rows in the COMPOSITE_ROLE row, does it make sense for about 160 realms ? (maybe we need to do some cleanup) Thanks, Haim. The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. _______________________________________________ keycloak-user mailing list keycloak-user@lists.jboss.org<mailto:keycloak-user@lists.jboss.org> https://lists.jboss.org/mailman/listinfo/keycloak-user<https://emea01.... The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.