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 <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 <https://mariadb.com/kb/en/blob/#indexing> > On Sep 24, 2020, at 12:43 PM, DImuthu Upeksha <dimuthu.upeks...@gmail.com> > 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 > > <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 <machr...@iu.edu > <mailto:machr...@iu.edu>> 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 > > <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 <dimuthu.upeks...@gmail.com >> <mailto:dimuthu.upeks...@gmail.com>> 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 >> >> <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 >> >> <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 >> >> <https://medium.com/@udith.indrakantha/issue-related-with-infinite-recursive-fetching-of-data-from-relationships-between-entity-classes-ffc5fac6c816> >> >> Thanks >> Dimuthu > > <sql.log.zip>
smime.p7s
Description: S/MIME cryptographic signature
