[keycloak-dev] Reduce number of DB updates

[Marek Posolda]

It may sound logically that with Picketlink+transactions are all update 
calls batched and flushed only once at commit time. However it doesn't 
seem to work this way.

I just tried to run AdapterTest.test1CreateRealm() with 
hibernate.show_sql switched to true. And I am seeing that after each 
setter call we have 4 SQL select calls and 1 SQL update call and I am 
seeing the same in MySQL log.

This may be caused by the Picketlink design as calling 
"partitionManager.update(realm)" doesn't call just DB update, but it is 
doing some DB queries before it. Fact is that for this sequence:
         realmModel.setAccessCodeLifespan(100);
         realmModel.setAccessCodeLifespanUserAction(600);
         realmModel.setCookieLoginAllowed(true);
         realmModel.setEnabled(true);
         realmModel.setName("JUGGLER");
         realmModel.setPrivateKeyPem("0234234");
         realmModel.setPublicKeyPem("0234234");
         realmModel.setTokenLifespan(1000);
         realmModel.setAutomaticRegistrationAfterSocialLogin(true);

we have 36 SQL selects and 9 SQL updates.

Marek

On 11.10.2013 18:04, Stian Thorgersen wrote:
> Unless there's a query it won't flush to the db in any case so calling update*** for every change shouldn't have any performance issues. If we did the same with Mongo that would obviously be a lot worse
>
> ----- Original Message -----
>> From: "Marek Posolda" <mposolda at redhat.com>
>> To: keycloak-dev at lists.jboss.org
>> Sent: Friday, 11 October, 2013 2:40:45 PM
>> Subject: [keycloak-dev] Reduce number of DB updates
>>
>> Hi,
>>
>> I think that one of easiest way to improve performance could be to
>> remove anti-pattern of calling model update after invoke of each setter
>> operation. Basically some objects like RealmAdapter, UserAdapter,
>> RoleAdapter are always updating model after each setter due to code like
>> this:
>>
>>       @Override
>>       public void setSocial(boolean social) {
>>           realm.setSocial(social);
>>           updateRealm();
>>       }
>>
>> On the other hand some others like ApplicationAdapter doesn't use this
>> and instead it has public method available on model like: public void
>> updateApplication() .
>>
>> Can't we use same pattern like ApplicationAdapter also for all other
>> model objects? Only downgrade is that code would need to be updated and
>> calls to updateXXX need to be added, but I think that's better approach
>> than DB update per setter.
>>
>> If you agree, I can create JIRA and contribute PR?
>>
>> Marek
>> _______________________________________________
>> keycloak-dev mailing list
>> keycloak-dev at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/keycloak-dev
>>