[keycloak-dev] Added DB indexes

[Stian Thorgersen]

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 at redhat.com> 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 at 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 at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/keycloak-dev
>>
>
>
>
> _______________________________________________
> keycloak-dev mailing list
> keycloak-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/keycloak-dev/attachments/20160405/61666bf9/attachment.html