<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">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">https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e</a></div><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">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>