<div dir="ltr">It shouldn't be necessary for a DBA to tweak the scheme to get decent performance, we need to make sure the required indexes are there already.</div><div class="gmail_extra"><br><div class="gmail_quote">On 31 March 2016 at 14:18, Marek Posolda <span dir="ltr"><<a href="mailto:mposolda@redhat.com" target="_blank">mposolda@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000"><span class="">
<div>On 31/03/16 13:59, Thomas Darimont
wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Hello Marek,
<div><br>
</div>
<div>thanks für this interesting hint - our postgres admin
recommended the following script to me to find foreig-key
index candidates:</div>
<div><a href="https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql" target="_blank">https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql</a><br>
</div>
<div><br>
</div>
<div>Running that an a fresh keycloak postgres db will return no
results... but if you disable the where condition (that checks
for usage)</div>
<div>at the end you'll get an IMHO useful list of index
candidates.</div>
<div>I put up a gist with the modified query and results here: <a href="https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e" target="_blank"></a><a href="https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e" target="_blank">https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e</a></div>
</div>
</blockquote></span>
Thanks Thomas for the interesting script! <br>
<br>
I've actually didn't add indexes to all foreign keys, but just to
those, which are widely used and are supposed to contain big number
of records. So especially the tables related to user model. Also I
needed to add some indexes to columns, which are not foreign keys
(those were needed for both MySQL and PostgreSQL), for example EMAIL
of USER_ENTITY table as Keycloak often needs to lookup users by
email.<span class="HOEnZb"><font color="#888888"><br>
<br>
Marek</font></span><div><div class="h5"><br>
<blockquote type="cite">
<div dir="ltr">
<div><br>
</div>
<div>Cheers,</div>
<div>Thomas</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">2016-03-31 12:27 GMT+02:00 Marek
Posolda <span dir="ltr"><<a href="mailto:mposolda@redhat.com" target="_blank">mposolda@redhat.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I've did
loads of testing for create/load/delete big number of users<br>
into database and I've send PR for add some additional
indexes into<br>
database to improve performance of operations with big
number of users<br>
in DB - <a href="https://github.com/keycloak/keycloak/pull/2456" rel="noreferrer" target="_blank">https://github.com/keycloak/keycloak/pull/2456</a><br>
<br>
Note that adding DB indexes is tricky as the performance
gain (or loss)<br>
might be highly dependent on underlying database. Some
example:<br>
<br>
1) PostgreSQL is adding the indexes by default just for
primary keys and<br>
unique constraints. But it doesn't add indexes for foreign
keys. So when<br>
you have 50K users in DB and 150K role mappings (each user
member of 3<br>
roles), then this query take more than 20 ms:<br>
<br>
select * from USER_ROLE_MAPPING where USER_ID='123';<br>
<br>
<br>
2) MySQL is adding the btree indexes by default even for
foreign keys.<br>
So the same query like above took less than 1 ms by default.<br>
<br>
<br>
So to improve the time on postgres, the index for USER_ID
column in<br>
USER_ROLE_MAPPING is needed, however for MySQL adding the
index doesn't<br>
have any effect as it's already there. In the end, I've
explicitly added<br>
the indexes into changelog file by default and I can see
very good<br>
performance for both mysql and postgresql and operations are
not<br>
dependent on number of users in DB.<br>
<br>
However in theory, adding the indexes may have bad effect
for some<br>
databases and slow down the time for create new user etc. I
personally<br>
don't want to test performance with all supported DBs :) So
another<br>
approach might be to not explicitly add the indexes and let
DBA to do<br>
that. I've added the possibility to enable logging some
overall<br>
statistics from Hibernate, so people can check it by
themselves. Let me<br>
know if you rather want to go this way and not add indexes
by default.<br>
<br>
Marek<br>
_______________________________________________<br>
keycloak-dev mailing list<br>
<a href="mailto:keycloak-dev@lists.jboss.org" target="_blank">keycloak-dev@lists.jboss.org</a><br>
<a href="https://lists.jboss.org/mailman/listinfo/keycloak-dev" rel="noreferrer" target="_blank">https://lists.jboss.org/mailman/listinfo/keycloak-dev</a><br>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
</div></div></div>
<br>_______________________________________________<br>
keycloak-dev mailing list<br>
<a href="mailto:keycloak-dev@lists.jboss.org">keycloak-dev@lists.jboss.org</a><br>
<a href="https://lists.jboss.org/mailman/listinfo/keycloak-dev" rel="noreferrer" target="_blank">https://lists.jboss.org/mailman/listinfo/keycloak-dev</a><br></blockquote></div><br></div>