Hello Marek, thanks für this interesting hint - our postgres admin recommended the following script to me to find foreig-key index candidates: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql Running that an a fresh keycloak postgres db will return no results... but if you disable the where condition (that checks for usage) at the end you'll get an IMHO useful list of index candidates. I put up a gist with the modified query and results here: https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e

Cheers, Thomas 2016-03-31 12:27 GMT+02:00 Marek Posolda <mposolda(a)redhat.com <mailto:mposolda@redhat.com>>: I've did loads of testing for create/load/delete big number of users into database and I've send PR for add some additional indexes into database to improve performance of operations with big number of users in DB - https://github.com/keycloak/keycloak/pull/2456 Note that adding DB indexes is tricky as the performance gain (or loss) might be highly dependent on underlying database. Some example: 1) PostgreSQL is adding the indexes by default just for primary keys and unique constraints. But it doesn't add indexes for foreign keys. So when you have 50K users in DB and 150K role mappings (each user member of 3 roles), then this query take more than 20 ms: select * from USER_ROLE_MAPPING where USER_ID='123'; 2) MySQL is adding the btree indexes by default even for foreign keys. So the same query like above took less than 1 ms by default. So to improve the time on postgres, the index for USER_ID column in USER_ROLE_MAPPING is needed, however for MySQL adding the index doesn't have any effect as it's already there. In the end, I've explicitly added the indexes into changelog file by default and I can see very good performance for both mysql and postgresql and operations are not dependent on number of users in DB. However in theory, adding the indexes may have bad effect for some databases and slow down the time for create new user etc. I personally don't want to test performance with all supported DBs :) So another approach might be to not explicitly add the indexes and let DBA to do that. I've added the possibility to enable logging some overall statistics from Hibernate, so people can check it by themselves. Let me know if you rather want to go this way and not add indexes by default. Marek _______________________________________________ keycloak-dev mailing list keycloak-dev(a)lists.jboss.org <mailto:keycloak-dev@lists.jboss.org> https://lists.jboss.org/mailman/listinfo/keycloak-dev

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. On 31 March 2016 at 14:18, Marek Posolda <mposolda(a)redhat.com&gt; wrote: > On 31/03/16 13:59, Thomas Darimont wrote: > > Hello Marek, > > thanks für this interesting hint - our postgres admin recommended the > following script to me to find foreig-key index candidates: > > https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql > > Running that an a fresh keycloak postgres db will return no results... > but if you disable the where condition (that checks for usage) > at the end you'll get an IMHO useful list of index candidates. > I put up a gist with the modified query and results here: > <https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e> > https://gist.github.com/thomasdarimont/992aaad6af704f40fd4483af3d42290e > > Thanks Thomas for the interesting script! > > 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. > > Marek > > > Cheers, > Thomas > > 2016-03-31 12:27 GMT+02:00 Marek Posolda <mposolda(a)redhat.com&gt;: > >> I've did loads of testing for create/load/delete big number of users >> into database and I've send PR for add some additional indexes into >> database to improve performance of operations with big number of users >> in DB - https://github.com/keycloak/keycloak/pull/2456 >> >> Note that adding DB indexes is tricky as the performance gain (or loss) >> might be highly dependent on underlying database. Some example: >> >> 1) PostgreSQL is adding the indexes by default just for primary keys and >> unique constraints. But it doesn't add indexes for foreign keys. So when >> you have 50K users in DB and 150K role mappings (each user member of 3 >> roles), then this query take more than 20 ms: >> >> select * from USER_ROLE_MAPPING where USER_ID='123'; >> >> >> 2) MySQL is adding the btree indexes by default even for foreign keys. >> So the same query like above took less than 1 ms by default. >> >> >> So to improve the time on postgres, the index for USER_ID column in >> USER_ROLE_MAPPING is needed, however for MySQL adding the index doesn't >> have any effect as it's already there. In the end, I've explicitly added >> the indexes into changelog file by default and I can see very good >> performance for both mysql and postgresql and operations are not >> dependent on number of users in DB. >> >> However in theory, adding the indexes may have bad effect for some >> databases and slow down the time for create new user etc. I personally >> don't want to test performance with all supported DBs :) So another >> approach might be to not explicitly add the indexes and let DBA to do >> that. I've added the possibility to enable logging some overall >> statistics from Hibernate, so people can check it by themselves. Let me >> know if you rather want to go this way and not add indexes by default. >> >> Marek >> _______________________________________________ >> keycloak-dev mailing list >> keycloak-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/keycloak-dev >> > > > > _______________________________________________ > keycloak-dev mailing list > keycloak-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/keycloak-dev > _______________________________________________ keycloak-dev mailing list keycloak-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-dev