Re: Recursive entity fetching in registry

[Christie, Marcus Aaron]

Hi Dimuthu,

I noticed a couple things:

- This line in ExperimentOutputValuePK [6] should check if instanceof 
ExperimentOutputValuePK. As is, my guess is equals() here always returns false, 
and that could lead OpenJPA to thinking it hasn't already loaded this one and 
it needs to do so again.
- You probably don't want to include the ExperimentOutputValuePK.value in the 
primary key since it's a BLOB. Support for indexing BLOBs is limited [7] and 
performance won't be good. In this case I would suggest a surrogate key. Or 
maybe there is another column that could be used instead, like an ordering 
column (if the values are ordered, maybe they're not).

Thanks,

Marcus

[6] 
https://github.com/apache/airavata/blob/param-sweep/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputValuePK.java#L61
 

[7] https://mariadb.com/kb/en/blob/#indexing 



> On Sep 24, 2020, at 12:43 PM, DImuthu Upeksha  
> wrote:
> 
> Hi Marcus,
> 
> I'm doing further testing to pinpoint the error however I have traced the sql 
> commands coming into the mysql database from Airavata when invoking this [5] 
> line. ExperimentOutputValueEntity has reference to ExperimentOutputEntity and 
> then to ExperimentEntity. I have attached the total SQL queries that came for 
> this line execution.
> 
> [5] 
> https://github.com/apache/airavata/blob/param-sweep/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/repositories/expcatalog/ExperimentOutputValueRepository.java#L72
>  
> 
> 
> Thanks
> Dimuthu
> 
> On Wed, Sep 23, 2020 at 6:09 PM Christie, Marcus Aaron  > wrote:
> Hi Dimuthu,
> 
> Thanks for investigating this. I have a few questions/observations:
> - when you fetch an experiment output, it would be understandable if the 
> experiment was then fetched if experimentOutput.getExperiment() was accessed. 
> This would in turn lead to the experiment's experiment outputs being fetch 
> since we have the fetch type set to EAGER [4]. So the sequence you describe 
> doesn't necessarily sound like unexpected behavior.
> - how many times does the recursive fetching go through fetching the same 
> objects?  I'm assuming at some point it stops otherwise we'd see timeouts or 
> stack overflow. Any idea what causes it to eventually stop?
> - do you have a test case that reproduces this behavior?
> - seems that this performance drop is new. The registry code itself hasn't 
> changed recently on master, but we did recently merge in the upgrade of 
> OpenJPA from 2.4.3 to 3.1.0. Could this be related to the newer version?
> - in general this sort of behavior makes me think that equals/hashCode isn't 
> implemented properly somewhere, since that would make it hard for OpenJPA to 
> figure out if it has already loaded an entity into the session. Looking at 
> ExperimentEntity and ExperimentOutputEntity, I don't see any issues there.
> 
> Thanks,
> 
> Marcus
> 
> [4] 
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L89
>  
> 
> 
>> On Sep 22, 2020, at 11:01 AM, DImuthu Upeksha > > wrote:
>> 
>> Hi Folks,
>> 
>> I just noticed that the entity relations that have bidirectional definitions 
>> (OneToMany and ManyToOne) between 2 tables cause recursive object fetching 
>> and it is causing a significant performance drop in database querying. For 
>> example [1] and [2]. When we fetch an experiment output, I can see a stack 
>> of experiment -> experiment outputs -> experiment 
>> 
>> Same issue was mentioned in [3] but the solution is for spring boot json 
>> parsing. As we don't use spring boot, we might have to figure out some other 
>> way to stop this recursive stack creation.
>> 
>> [1] 
>> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L88
>>  
>> 
>> [2] 
>> 

Re: Recursive entity fetching in registry

[Christie, Marcus Aaron]

Hi Dimuthu,

Thanks for investigating this. I have a few questions/observations:
- when you fetch an experiment output, it would be understandable if the 
experiment was then fetched if experimentOutput.getExperiment() was accessed. 
This would in turn lead to the experiment's experiment outputs being fetch 
since we have the fetch type set to EAGER [4]. So the sequence you describe 
doesn't necessarily sound like unexpected behavior.
- how many times does the recursive fetching go through fetching the same 
objects?  I'm assuming at some point it stops otherwise we'd see timeouts or 
stack overflow. Any idea what causes it to eventually stop?
- do you have a test case that reproduces this behavior?
- seems that this performance drop is new. The registry code itself hasn't 
changed recently on master, but we did recently merge in the upgrade of OpenJPA 
from 2.4.3 to 3.1.0. Could this be related to the newer version?
- in general this sort of behavior makes me think that equals/hashCode isn't 
implemented properly somewhere, since that would make it hard for OpenJPA to 
figure out if it has already loaded an entity into the session. Looking at 
ExperimentEntity and ExperimentOutputEntity, I don't see any issues there.

Thanks,

Marcus

[4] 
https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L89

> On Sep 22, 2020, at 11:01 AM, DImuthu Upeksha  
> wrote:
> 
> Hi Folks,
> 
> I just noticed that the entity relations that have bidirectional definitions 
> (OneToMany and ManyToOne) between 2 tables cause recursive object fetching 
> and it is causing a significant performance drop in database querying. For 
> example [1] and [2]. When we fetch an experiment output, I can see a stack of 
> experiment -> experiment outputs -> experiment 
> 
> Same issue was mentioned in [3] but the solution is for spring boot json 
> parsing. As we don't use spring boot, we might have to figure out some other 
> way to stop this recursive stack creation.
> 
> [1] 
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L88
>  
> 
> [2] 
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80
>  
> 
> [3] 
> https://medium.com/@udith.indrakantha/issue-related-with-infinite-recursive-fetching-of-data-from-relationships-between-entity-classes-ffc5fac6c816
>  
> 
> 
> Thanks
> Dimuthu



smime.p7s
Description: S/MIME cryptographic signature

Re: Recursive entity fetching in registry

[DImuthu Upeksha]

Hi Suresh,

I believe this is more of a bug in JPA or we are configuring it in the
wrong way. If we have configured a ManyToOne relationship to a LAZY
initialization [8], it should not load ExperimentEntity if we haven't
invoked getExperiment().get.. methods. But in this case, it's automatically
initializing ExperimentEntity and that's why we are getting this recursive
object creation. What we need to do is to stop this recursive object
creation in bidirectional relationships.

As a general observation and a reply to suresh's suggestion, In the long
run, observing the whole database stack from the top level, I think we need
to consider using some sophisticated framework like spring (spring boot) to
handle JPA transactions. Currently we are coding very close to entity
managers and that might cause unexpected issues if we haven't done it
properly. That (Spring) might facilitate us to either go with GraphQL or
Grpc based on the evaluations we get.

[8]
https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80

Thanks
Dimuthu

On Tue, Sep 22, 2020 at 12:15 PM Suresh Marru  wrote:

> Hi Dimuthu,
>
> I have been wondering if GraphQL will be worthwhile to explore for such
> bulk retrieval’s? I think this will be a low hanging fruit for us to try to
> integrating with something like Hasura[4] it is dominantly used for
> Postgres but they have been actively supporting MySql as well [5]
>
> I am not sure if this will address you point out though, since GraphQL
> spec doesn’t support recursive nested objects [6], simple one to many seems
> to be supported [7] but not bi directional.
>
> Suresh
>
> [4] - https://github.com/hasura/graphql-engine
> [5] - https://hasura.io/docs/1.0/graphql/core/guides/mysql-preview.html
> [6] - https://github.com/graphql/graphql-spec/issues/91
> [7] -
> https://hasura.io/docs/1.0/graphql/core/schema/table-relationships/database-modelling/one-to-many.html
>
>
> On Sep 22, 2020, at 11:01 AM, DImuthu Upeksha 
> wrote:
>
> Hi Folks,
>
> I just noticed that the entity relations that have
> bidirectional definitions (OneToMany and ManyToOne) between 2 tables cause
> recursive object fetching and it is causing a significant performance drop
> in database querying. For example [1] and [2]. When we fetch an
> experiment output, I can see a stack of experiment -> experiment outputs ->
> experiment 
>
> Same issue was mentioned in [3] but the solution is for spring boot json
> parsing. As we don't use spring boot, we might have to figure out some
> other way to stop this recursive stack creation.
>
> [1]
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L88
> [2]
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80
> [3]
> https://medium.com/@udith.indrakantha/issue-related-with-infinite-recursive-fetching-of-data-from-relationships-between-entity-classes-ffc5fac6c816
>
> Thanks
> Dimuthu
>
>
>

Re: Recursive entity fetching in registry

[Suresh Marru]

Hi Dimuthu,

I have been wondering if GraphQL will be worthwhile to explore for such bulk 
retrieval’s? I think this will be a low hanging fruit for us to try to 
integrating with something like Hasura[4] it is dominantly used for Postgres 
but they have been actively supporting MySql as well [5] 

I am not sure if this will address you point out though, since GraphQL spec 
doesn’t support recursive nested objects [6], simple one to many seems to be 
supported [7] but not bi directional.

Suresh

[4] - https://github.com/hasura/graphql-engine 

[5] - https://hasura.io/docs/1.0/graphql/core/guides/mysql-preview.html 
 
[6] - https://github.com/graphql/graphql-spec/issues/91 

[7] - 
https://hasura.io/docs/1.0/graphql/core/schema/table-relationships/database-modelling/one-to-many.html
 

 

> On Sep 22, 2020, at 11:01 AM, DImuthu Upeksha  
> wrote:
> 
> Hi Folks,
> 
> I just noticed that the entity relations that have bidirectional definitions 
> (OneToMany and ManyToOne) between 2 tables cause recursive object fetching 
> and it is causing a significant performance drop in database querying. For 
> example [1] and [2]. When we fetch an experiment output, I can see a stack of 
> experiment -> experiment outputs -> experiment 
> 
> Same issue was mentioned in [3] but the solution is for spring boot json 
> parsing. As we don't use spring boot, we might have to figure out some other 
> way to stop this recursive stack creation.
> 
> [1] 
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L88
>  
> 
> [2] 
> https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80
>  
> 
> [3] 
> https://medium.com/@udith.indrakantha/issue-related-with-infinite-recursive-fetching-of-data-from-relationships-between-entity-classes-ffc5fac6c816
>  
> 
> 
> Thanks
> Dimuthu

Recursive entity fetching in registry

[DImuthu Upeksha]

Hi Folks,

I just noticed that the entity relations that have
bidirectional definitions (OneToMany and ManyToOne) between 2 tables cause
recursive object fetching and it is causing a significant performance drop
in database querying. For example [1] and [2]. When we fetch an
experiment output, I can see a stack of experiment -> experiment outputs ->
experiment 

Same issue was mentioned in [3] but the solution is for spring boot json
parsing. As we don't use spring boot, we might have to figure out some
other way to stop this recursive stack creation.

[1]
https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L88
[2]
https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80
[3]
https://medium.com/@udith.indrakantha/issue-related-with-infinite-recursive-fetching-of-data-from-relationships-between-entity-classes-ffc5fac6c816

Thanks
Dimuthu