summarize-objectstore freezes

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

summarize-objectstore freezes

Joel Richardson-2
Hi all,

Looking for any ideas on the following…

The MouseMine build is getting stuck in the summarize-objectstore postprocess.
It gets most of the way through checking for empty refs and collections.
Then on ThreePrimeUTR it stops. I let it run for 6 hours after it hit this point before killing it.
Just to be sure, I reran this step, and it got stuck at the same spot.

The last log message is this:

2018-08-20 10:30:35 INFO  org.intermine.objectstore.ObjectStoreSummary     - Querying for empty: ThreePrimeUTR.overlappingFeatures


pg_stat_activity shows this:

SELECT 1 AS a1_ WHERE EXISTS(SELECT a2_.id FROM ThreePrimeUTR AS a1_, SequenceFeature AS a2_, OverlappingFeaturesSequenceFeature AS indirect0 WHERE a1_.id = indirect0.SequenceFeature AND indirect0.OverlappingFeatures = a2_.id) LIMIT 1;



Top shows one postgress process using 100% of the cpu (before killing it, that is).
Running this query by hand, I can plug in different table names and it returns fine.
But if I plug in ThreePrimeUTR (or FivePrimeUTR, or UTR), it does not return (at least in the time I’m prepared to wait)
I’ve compared the query plans between ones that return and the UTR ones, and I don’t see any glaring differences.
I’ve vacuumed and analyzed and it makes no difference. 
I can find nothing wrong with the UTR tables themselves. They have the expected number of rows and the field values look ok.
They seem to have all the right indexes.

At this point, I’m stumped. Any ideas/suggestions would be appreciated!!

Thanks,
Joel
-- 
Joel E. Richardson, Ph.D.
Sr. Research Scientist
Mouse Genome Informatics
The Jackson Laboratory
600 Main Street
Bar Harbor, Maine 04609
207-288-6435
---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.
_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

sergio contrino-2
hi joel,
not sure about this, but i think julie had a similar problem last week.
i'll check if it has left traces, but let me know if you solve the issue.
thanks
sergio


On 21/08/18 16:38, Joel Richardson wrote:

> Hi all,
>
> Looking for any ideas on the following…
>
> The MouseMine build is getting stuck in the summarize-objectstore
> postprocess.
> It gets most of the way through checking for empty refs and collections.
> Then on ThreePrimeUTR it stops. I let it run for 6 hours after it hit
> this point before killing it.
> Just to be sure, I reran this step, and it got stuck at the same spot.
>
> The last log message is this:
>
> 2018-08-20 10:30:35 INFO  org.intermine.objectstore.ObjectStoreSummary  
>    - Querying for empty: ThreePrimeUTR.overlappingFeatures
>
>
> pg_stat_activity shows this:
>
> SELECT 1 AS a1_ WHERE EXISTS(SELECT a2_.id FROM ThreePrimeUTR AS a1_,
> SequenceFeature AS a2_, OverlappingFeaturesSequenceFeature AS indirect0
> WHERE a1_.id = indirect0.SequenceFeature AND
> indirect0.OverlappingFeatures = a2_.id) LIMIT 1;
>
>
>
> Top shows one postgress process using 100% of the cpu (before killing
> it, that is).
> Running this query by hand, I can plug in different table names and it
> returns fine.
> But if I plug in ThreePrimeUTR (or FivePrimeUTR, or UTR), it does not
> return (at least in the time I’m prepared to wait)
> I’ve compared the query plans between ones that return and the UTR ones,
> and I don’t see any glaring differences.
> I’ve vacuumed and analyzed and it makes no difference.
> I can find nothing wrong with the UTR tables themselves. They have the
> expected number of rows and the field values look ok.
> They seem to have all the right indexes.
>
> At this point, I’m stumped. Any ideas/suggestions would be appreciated!!
>
> Thanks,
> Joel
> --
> Joel E. Richardson, Ph.D.
> Sr. Research Scientist
> Mouse Genome Informatics
> The Jackson Laboratory
> 600 Main Street
> Bar Harbor, Maine 04609
> 207-288-6435
> [hidden email]
> ---
>
> The information in this email, including attachments, may be
> confidential and is intended solely for the addressee(s). If you believe
> you received this email by mistake, please notify the sender by return
> email as soon as possible.
>
>
> _______________________________________________
> dev mailing list
> [hidden email]
> https://lists.intermine.org/mailman/listinfo/dev
>

--
sergio contrino                  InterMine, University of Cambridge
https://sergiocontrino.github.io           http://www.intermine.org
_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

Joel Richardson-2

Thanks Sergio. I¹m really tearing my hair out. I gave up and started a new
build from scratch.
Given that it takes 36 hrs, I really didn¹t want to do that, but I
couldn¹t think of anything else to try.
We¹ll see if this makes any differenceŠ I¹ll let you know.

Joel

--
Joel E. Richardson, Ph.D.
Sr. Research Scientist
Mouse Genome Informatics
The Jackson Laboratory
600 Main Street
Bar Harbor, Maine 04609
207-288-6435
[hidden email]





On 8/22/18, 7:51 AM, "sergio contrino" <[hidden email]> wrote:

>hi joel,
>not sure about this, but i think julie had a similar problem last week.
>i'll check if it has left traces, but let me know if you solve the issue.
>thanks
>sergio
>
>
>On 21/08/18 16:38, Joel Richardson wrote:
>> Hi all,
>>
>> Looking for any ideas on the followingŠ
>>
>> The MouseMine build is getting stuck in the summarize-objectstore
>> postprocess.
>> It gets most of the way through checking for empty refs and collections.
>> Then on ThreePrimeUTR it stops. I let it run for 6 hours after it hit
>> this point before killing it.
>> Just to be sure, I reran this step, and it got stuck at the same spot.
>>
>> The last log message is this:
>>
>> 2018-08-20 10:30:35 INFO  org.intermine.objectstore.ObjectStoreSummary
>>
>>    - Querying for empty: ThreePrimeUTR.overlappingFeatures
>>
>>
>> pg_stat_activity shows this:
>>
>> SELECT 1 AS a1_ WHERE EXISTS(SELECT a2_.id FROM ThreePrimeUTR AS a1_,
>> SequenceFeature AS a2_, OverlappingFeaturesSequenceFeature AS indirect0
>> WHERE a1_.id = indirect0.SequenceFeature AND
>> indirect0.OverlappingFeatures = a2_.id) LIMIT 1;
>>
>>
>>
>> Top shows one postgress process using 100% of the cpu (before killing
>> it, that is).
>> Running this query by hand, I can plug in different table names and it
>> returns fine.
>> But if I plug in ThreePrimeUTR (or FivePrimeUTR, or UTR), it does not
>> return (at least in the time I¹m prepared to wait)
>> I¹ve compared the query plans between ones that return and the UTR
>>ones,
>> and I don¹t see any glaring differences.
>> I¹ve vacuumed and analyzed and it makes no difference.
>> I can find nothing wrong with the UTR tables themselves. They have the
>> expected number of rows and the field values look ok.
>> They seem to have all the right indexes.
>>
>> At this point, I¹m stumped. Any ideas/suggestions would be appreciated!!
>>
>> Thanks,
>> Joel
>> --
>> Joel E. Richardson, Ph.D.
>> Sr. Research Scientist
>> Mouse Genome Informatics
>> The Jackson Laboratory
>> 600 Main Street
>> Bar Harbor, Maine 04609
>> 207-288-6435
>> [hidden email]
>> ---
>>
>> The information in this email, including attachments, may be
>> confidential and is intended solely for the addressee(s). If you
>>believe
>> you received this email by mistake, please notify the sender by return
>> email as soon as possible.
>>
>>
>> _______________________________________________
>> dev mailing list
>> [hidden email]
>> https://lists.intermine.org/mailman/listinfo/dev
>>
>
>--
>sergio contrino                  InterMine, University of Cambridge
>https://sergiocontrino.github.io           http://www.intermine.org

---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.

_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

Joel Richardson-2
Hi Sergio,

I was thinking (hoping) it would be one of those random things that just
goes away if you try again, but no.
I have done a full build from scratch, and it gets stuck at exactly the
same spot. There is something about ThreePrimeUTR (and FivePrimeUTR) that
causes postgres (apparently) to lose its mind.

Did you find out anything on your end? Did Julie hit this same problem?

One question: could there have been any changes between 1.8.1 and 1.8.5
that might be related?

Thanks,
Joel

--
Joel E. Richardson, Ph.D.
Sr. Research Scientist
Mouse Genome Informatics
The Jackson Laboratory
600 Main Street
Bar Harbor, Maine 04609
207-288-6435
[hidden email]





On 8/22/18, 9:59 AM, "Joel Richardson" <[hidden email]> wrote:

>
>Thanks Sergio. I¹m really tearing my hair out. I gave up and started a new
>build from scratch.
>Given that it takes 36 hrs, I really didn¹t want to do that, but I
>couldn¹t think of anything else to try.
>We¹ll see if this makes any differenceŠ I¹ll let you know.
>
>Joel
>
>--
>Joel E. Richardson, Ph.D.
>Sr. Research Scientist
>Mouse Genome Informatics
>The Jackson Laboratory
>600 Main Street
>Bar Harbor, Maine 04609
>207-288-6435
>[hidden email]
>
>
>
>
>
>On 8/22/18, 7:51 AM, "sergio contrino" <[hidden email]> wrote:
>
>>hi joel,
>>not sure about this, but i think julie had a similar problem last week.
>>i'll check if it has left traces, but let me know if you solve the issue.
>>thanks
>>sergio
>>
>>
>>On 21/08/18 16:38, Joel Richardson wrote:
>>> Hi all,
>>>
>>> Looking for any ideas on the followingŠ
>>>
>>> The MouseMine build is getting stuck in the summarize-objectstore
>>> postprocess.
>>> It gets most of the way through checking for empty refs and
>>>collections.
>>> Then on ThreePrimeUTR it stops. I let it run for 6 hours after it hit
>>> this point before killing it.
>>> Just to be sure, I reran this step, and it got stuck at the same spot.
>>>
>>> The last log message is this:
>>>
>>> 2018-08-20 10:30:35 INFO  org.intermine.objectstore.ObjectStoreSummary
>>>
>>>    - Querying for empty: ThreePrimeUTR.overlappingFeatures
>>>
>>>
>>> pg_stat_activity shows this:
>>>
>>> SELECT 1 AS a1_ WHERE EXISTS(SELECT a2_.id FROM ThreePrimeUTR AS a1_,
>>> SequenceFeature AS a2_, OverlappingFeaturesSequenceFeature AS indirect0
>>> WHERE a1_.id = indirect0.SequenceFeature AND
>>> indirect0.OverlappingFeatures = a2_.id) LIMIT 1;
>>>
>>>
>>>
>>> Top shows one postgress process using 100% of the cpu (before killing
>>> it, that is).
>>> Running this query by hand, I can plug in different table names and it
>>> returns fine.
>>> But if I plug in ThreePrimeUTR (or FivePrimeUTR, or UTR), it does not
>>> return (at least in the time I¹m prepared to wait)
>>> I¹ve compared the query plans between ones that return and the UTR
>>>ones,
>>> and I don¹t see any glaring differences.
>>> I¹ve vacuumed and analyzed and it makes no difference.
>>> I can find nothing wrong with the UTR tables themselves. They have the
>>> expected number of rows and the field values look ok.
>>> They seem to have all the right indexes.
>>>
>>> At this point, I¹m stumped. Any ideas/suggestions would be
>>>appreciated!!
>>>
>>> Thanks,
>>> Joel
>>> --
>>> Joel E. Richardson, Ph.D.
>>> Sr. Research Scientist
>>> Mouse Genome Informatics
>>> The Jackson Laboratory
>>> 600 Main Street
>>> Bar Harbor, Maine 04609
>>> 207-288-6435
>>> [hidden email]
>>> ---
>>>
>>> The information in this email, including attachments, may be
>>> confidential and is intended solely for the addressee(s). If you
>>>believe
>>> you received this email by mistake, please notify the sender by return
>>> email as soon as possible.
>>>
>>>
>>> _______________________________________________
>>> dev mailing list
>>> [hidden email]
>>> https://lists.intermine.org/mailman/listinfo/dev
>>>
>>
>>--
>>sergio contrino                  InterMine, University of Cambridge
>>https://sergiocontrino.github.io           http://www.intermine.org
>

---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.
_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

sergio contrino-2
hi joel,
ok. unfortunately julie is on holiday at the moment. i'll check revision
history and let you know.
i wonder if there is some mistakes in the order of post processes.
sergio

On 23/08/18 15:56, Joel Richardson wrote:

> Hi Sergio,
>
> I was thinking (hoping) it would be one of those random things that just
> goes away if you try again, but no.
> I have done a full build from scratch, and it gets stuck at exactly the
> same spot. There is something about ThreePrimeUTR (and FivePrimeUTR) that
> causes postgres (apparently) to lose its mind.
>
> Did you find out anything on your end? Did Julie hit this same problem?
>
> One question: could there have been any changes between 1.8.1 and 1.8.5
> that might be related?
>
> Thanks,
> Joel
>

--
sergio contrino                  InterMine, University of Cambridge
https://sergiocontrino.github.io           http://www.intermine.org
_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

Joel Richardson-2

Here¹s what we have right now:

<post-process name="do-sources"/>
    <post-process name="transfer-sequences"/>
    <post-process name="create-references"/>
    <post-process name="create-chromosome-locations-and-lengths"/>
    <post-process name="create-location-overlap-index"/>
    <post-process name="create-overlap-view"/>
    <post-process name="create-attribute-indexes"/>
    <post-process name="create-autocomplete-index"/>
    <post-process name="summarise-objectstore"/>
    <post-process name="create-search-index"/>

I have had several successful builds prior this with the above
post-process order.



Joel


--
Joel E. Richardson, Ph.D.
Sr. Research Scientist
Mouse Genome Informatics
The Jackson Laboratory
600 Main Street
Bar Harbor, Maine 04609
207-288-6435
[hidden email]





On 8/23/18, 11:15 AM, "sergio contrino" <[hidden email]> wrote:

>hi joel,
>ok. unfortunately julie is on holiday at the moment. i'll check revision
>history and let you know.
>i wonder if there is some mistakes in the order of post processes.
>sergio
>
>On 23/08/18 15:56, Joel Richardson wrote:
>> Hi Sergio,
>>
>> I was thinking (hoping) it would be one of those random things that just
>> goes away if you try again, but no.
>> I have done a full build from scratch, and it gets stuck at exactly the
>> same spot. There is something about ThreePrimeUTR (and FivePrimeUTR)
>>that
>> causes postgres (apparently) to lose its mind.
>>
>> Did you find out anything on your end? Did Julie hit this same problem?
>>
>> One question: could there have been any changes between 1.8.1 and 1.8.5
>> that might be related?
>>
>> Thanks,
>> Joel
>>
>
>--
>sergio contrino                  InterMine, University of Cambridge
>https://sergiocontrino.github.io           http://www.intermine.org

---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.

_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

sergio contrino-2
hi joel,
i did not see any specific culprit in the changes between 1.8.1 and 1.8.5.
i would change your objectstoresummary.config.properties (in
yourmine/dbmodel/resources ) to add

ignore.counts=org.intermine.model.bio.Sequence.residues
org.intermine.model.bio.SequenceFeature.overlappingFeatures

(i think you should already have the first in place).

for testing, i would put a dump just before the process failing,
something like
<post-process name="create-autocomplete-index" dump="true"/>

or probably better just comment out the summarise and then you will have
the final dump of the database (no need to rebuild), and the summarise
will be run when deploying the webapp, so testing should be quicker.

checking
http://www.mousemine.org/mousemine
i don't see any UTR in the current version.
also i don't see a mousemine directory in your repo (are you building
out of flymine?)
it could be that you are already ignoring the counts for overlapping
features.
if i can access your code and data (after you made a dump) i can try to
run it here.

thanks
sergio


On 23/08/18 17:30, Joel Richardson wrote:

>
> Here¹s what we have right now:
>
> <post-process name="do-sources"/>
>      <post-process name="transfer-sequences"/>
>      <post-process name="create-references"/>
>      <post-process name="create-chromosome-locations-and-lengths"/>
>      <post-process name="create-location-overlap-index"/>
>      <post-process name="create-overlap-view"/>
>      <post-process name="create-attribute-indexes"/>
>      <post-process name="create-autocomplete-index"/>
>      <post-process name="summarise-objectstore"/>
>      <post-process name="create-search-index"/>
>
> I have had several successful builds prior this with the above
> post-process order.
>
>
>
> Joel
>
>

--
sergio contrino                  InterMine, University of Cambridge
https://sergiocontrino.github.io           http://www.intermine.org
_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: summarize-objectstore freezes

Joel Richardson-2




On 8/24/18, 7:16 AM, "sergio contrino" <[hidden email]> wrote:

>hi joel,
>i did not see any specific culprit in the changes between 1.8.1 and 1.8.5.

Thanks for checking.

>i would change your objectstoresummary.config.properties (in
>yourmine/dbmodel/resources ) to add
>
>ignore.counts=org.intermine.model.bio.Sequence.residues
>org.intermine.model.bio.SequenceFeature.overlappingFeatures
>
>(i think you should already have the first in place).

OK will do, thanks. We are ignoring Sequence.residues already. If I add
SequenceFeature.overlappingFeatures, won’t that circumvent the whole
problem? That would definitely be a welcome solution at this point!

>
>for testing, i would put a dump just before the process failing,
>something like
><post-process name="create-autocomplete-index" dump="true"/>

Huh, I never knew you could add a dump to a post-process. Thanks!

>
>or probably better just comment out the summarise and then you will have
>the final dump of the database (no need to rebuild), and the summarise
>will be run when deploying the webapp, so testing should be quicker.
>
>checking
>http://www.mousemine.org/mousemine
>i don't see any UTR in the current version.

Right, none of this is on public yet. But you can try test.mousemine.org.
It contains the last successful dev build.

>also i don't see a mousemine directory in your repo (are you building
>out of flymine?)

No we have mousemine in a separate repo here:
https://github.com/mgijax/mousemine

>it could be that you are already ignoring the counts for overlapping
>features.
>if i can access your code and data (after you made a dump) i can try to
>run it here.

Thanks for all of this. Hopefully I can simply ignore that field in the
counts and all of this becomes much less urgent.
In any case...

I looked more closely at the query plans generated for different tables.
There seem to be two forms generated. One uses a triple nested loop (!!) -
see attached plans for ThreePrimeUTR and CDS. One uses a nested-loop
inside a merge-join (see attached plan for Exon). The triple nested loop
used for ThreePrimeUTR initially caught my eye. But then I saw that the
same strategy is used for CDS, which is only a little smaller but takes <2
sec, so I stopped looking at it.

Then I tried an alternative formulation of the problem query, and (wonder
of wonders) it worked!
So it seems that the query plan is the real issue. Here’s the way the
query is currently formulated:

SELECT 1 AS a1_ WHERE EXISTS(
  SELECT a2_.id FROM ThreePrimeUTR AS a1_, SequenceFeature AS a2_,
OverlappingFeaturesSequenceFeature AS indirect0
  WHERE a1_.id = indirect0.SequenceFeature
  AND indirect0.OverlappingFeatures = a2_.id) I
LIMIT 1;

Here’s an alternative form that returns in a couple seconds:

SELECT 1 AS _a1
FROM OverlappingFeaturesSequenceFeature
WHERE SequenceFeature IN (SELECT id FROM ThreePrimeUTR)
LIMIT 1;

See the last attachment for the plan



Cheers,
Joel

>
>thanks
>sergio
>
>
>On 23/08/18 17:30, Joel Richardson wrote:
>>
>> Here¹s what we have right now:
>>
>> <post-process name="do-sources"/>
>>      <post-process name="transfer-sequences"/>
>>      <post-process name="create-references"/>
>>      <post-process name="create-chromosome-locations-and-lengths"/>
>>      <post-process name="create-location-overlap-index"/>
>>      <post-process name="create-overlap-view"/>
>>      <post-process name="create-attribute-indexes"/>
>>      <post-process name="create-autocomplete-index"/>
>>      <post-process name="summarise-objectstore"/>
>>      <post-process name="create-search-index"/>
>>
>> I have had several successful builds prior this with the above
>> post-process order.
>>
>>
>>
>> Joel
>>
>>
>
>--
>sergio contrino                  InterMine, University of Cambridge
>https://sergiocontrino.github.io           http://www.intermine.org
---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.

_______________________________________________
dev mailing list
[hidden email]
https://lists.intermine.org/mailman/listinfo/dev

ThreePrimeUTR-qplan.rtf (3K) Download Attachment
Exon-qplan.rtf (3K) Download Attachment
CDS-qplan.rtf (2K) Download Attachment
alternate-ThreePrimeUTR-qplan.rtf (2K) Download Attachment