[keycloak-user] Keycloak in production. Use MongoDB or an RDBMS flavour?

[Marek Posolda]

On 11/05/16 17:43, Ton Swieb wrote:
> Hi Marek,
>
> Thank you for your answer. So if I understand you correctly there are
> no plans to drop Mongo support in the near feature. Good to know.
AFAIK we will keep mongo as many people are using that. But I am not in 
position to promise anything related this :-)
>
> How many (concurrent) users did you need to use to see a performance
> difference between Mongo and MySQL?
Depends on test scenario. For example if you have test for concurrent 
login/logout of same set of users, then you might not see so big 
difference. That's because keycloak has caching layer, so once user (or 
other object) is read from DB for first time, then it's cached and next 
reads of same user read this user from cache instead of from DB. So 
performance of DB is not so visible here due to huge amount of caching.

The difference might be more visible if you do some write operations 
(like creating or deleting users). For example test for write/deleting 
of users. I've just tried to run performance for create and delete 10000 
users. The results:
MySQL:
8:08:47,096 INFO  Operation 'create 10000 users' average: 227756 ms
08:08:47,096 INFO  Operation 'delete 10000 users' average: 110315 ms

Mongo:
08:14:57,957 INFO  Operation 'create 10000 users' average: 157432 ms
08:14:57,958 INFO  Operation 'delete 10000 users' average: 49908 ms

You can see that difference is not so big. That's because in 1.9 
release, we did lot of tuning for RDBMS model and we added some DB 
indexes to tune performance. However for Mongo, we did not yet do this 
tuning.Once we do that, the difference will be likely bigger. You can 
see here the indexes we did for RDBMS for inspiration if you want to 
tune your mongo:

https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.0.xml#L78-80
https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.2.xml#L22-59


If you want to try the performance test, you can do this (with MySQL, 
assuming you have DB "keycloak" on localhost:3306 and user "keycloak" 
with password "keycloak"):

cd $KEYCLOAK_SOURCES
mvn clean install -DskipTests=true
cd testsuite/integration-arquillian/tests/other/jpa-performance
mvn clean install 
-Dkeycloak.connectionsJpa.url=jdbc:mysql://localhost/keycloak 
-Dkeycloak.connectionsJpa.driver=com.mysql.jdbc.Driver 
-Dkeycloak.connectionsJpa.user=keycloak 
-Dkeycloak.connectionsJpa.password=keycloak 
-Dmany.users.read.after.create=true -Dmany.users.create.objects=true


with mongo, you can run the same test with last command like this 
(assuming mongo running on localhost, test will use DB "keycloak-perf" ):

mvn clean install -Dkeycloak.realm.provider=mongo 
-Dkeycloak.user.provider=mongo 
-Dkeycloak.userSessionPersister.provider=mongo 
-Dkeycloak.eventsStore.provider=mongo 
-Dkeycloak.connectionsMongo.db=keycloak-perf 
-Dmany.users.read.after.create=true -Dmany.users.create.objects=true

See the files for more available properties:
https://github.com/keycloak/keycloak/blob/master/testsuite/integration-arquillian/tests/other/jpa-performance/README.md
https://github.com/keycloak/keycloak/blob/master/testsuite/integration-arquillian/tests/base/src/test/resources/META-INF/keycloak-server.json

>
> I assume the lack of transaction support in Mongo only becomes an
> issue with multi row/document transactions. Are multi row/document
> transactions used commonly in the Keycloak application or are most
> transactions limited to a single row/document?
No, most of operations in admin console usually create/update just 
single object. Same if user updates his profile in account management or 
new user registers, it's just single user row involved. There are some 
exceptions for this - for example if you create new realm or import 
realm, there are transactions under many different objects in different 
collection. But creating or importing realm is usually very rare operation.

Marek
>
> Regards, Ton
>
> 2016-05-11 9:45 GMT+02:00 Marek Posolda <mposolda at redhat.com>:
>> On 10/05/16 14:18, Ton Swieb wrote:
>>> Hi,
>>>
>>> I understand from the Keycloak documentation that both MongoDB and
>>> multiple flavours of RDBMS are supported, but I cannot find any
>>> recommendation whether to use MongoDB or an RDBMS by default.
>>>
>>> Which one is best suited for the Keycloak product?
>>> I am anticipating a user base of around 10000 users (mainly via
>>> Identity Brokering), will use offline tokens and use Keycloak as an
>>> Identity Broker for a SAML IdP. I am starting from a green field
>>> situation and do not have any restrictions on using a specific DB.
>>>
>>> I found a comment of Bill Birke on the Keycloak developer
>>> mailing-list,
>>> http://lists.jboss.org/pipermail/keycloak-dev/2015-July/004924.html,
>>> wishing he could drop Mongo and not seeing any advantages of using
>>> Mongo, but unfortunately the thread does not end with a
>>> conclusion/decision :-)
>>>
>>> What is the current position of the Keycloak team about using Mongo?
>> We added Mongo support very early (somewhen around 2013) as an alternative
>> storage, which was at that time required by other project, which consumed
>> keycloak. The second project (Liveoak) is not under active development
>> anymore, but in the meantime, a lot of people started to use Keycloak with
>> Mongo and it seems that some of them already in production.
>>
>> The advantage of Mongo is good performance and scalability. At some point,
>> when I tested performance with bigger number of users, I saw much better
>> performance for Mongo then for MySQL. Also Mongo has support for DB
>> clustering and sharding (some RDBMS has it too AFAIK, but usually you need
>> to pay for them, which is not the case with Mongo ;)) . On the other hand,
>> biggest disadvantage of Mongo is missing support for transactions. So in
>> theory, if some error/bad situation happens, you can theoretically end with
>> partially inconsistent data in DB.
>>
>> Marek
>>>
>>> In which scenario should I consider using MongoDB over an RDBMS or
>>> vice versa? There are off course the usual pro/con's between NoSQL and
>>> RDBMS, but I would like to know to what extend they hold true when it
>>> comes to using Keycloak in production or whether Keycloak is optimized
>>> specifically for NoSQL or RDBMS.
>>>
>>> Regards,
>>>
>>> Ton
>>>