[keycloak-dev] Added DB indexes

[Thomas Raehalme]

+1 For pre-defined standard indices. The easier the installation process is
to get up and running the better it is for the product.

On Tue, Apr 5, 2016 at 8:58 AM, Stian Thorgersen <sthorger at redhat.com>
wrote:

> 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
>>
>
>
> _______________________________________________
> 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/92ac2ec4/attachment-0001.html