How long should ik take to load the User created annotation track from the MySQL backend?

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

How long should ik take to load the User created annotation track from the MySQL backend?

Wim S
Hi,

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script.

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo 
2.0.6.

In Apollo
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming

--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Jacques Dainat-4
Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <[hidden email]> wrote:

Hi,

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script.

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo 
2.0.6.

In Apollo
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].

--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <[hidden email]> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <[hidden email]> wrote:

Hi,

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script.

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo 
2.0.6.

In Apollo
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].


--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Wim S
Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes. 
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far.

To reproduce you could try this public reference genome and gene model:
ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct.

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use.
Also part of the time CPU usage is only between 20% and 70%.

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation.
We don't (yet) have well defined DEV/QA/PROD installations.

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know.

Thank you.


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="nMnvD-1gBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">jacque...@...> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called <a title="agat_sp_webApollo_compliant.pl" href="https://github.com/NBISweden/AGAT/blob/master/bin/agat_sp_webApollo_compliant.pl" style="color:rgb(3,102,214);text-decoration:none;font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;;font-size:14.000000953674316px;white-space:nowrap" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;">agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): <a href="https://github.com/NBISweden/AGAT" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;">https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
<a href="http://nbis.se/about/staff/jacques-dainat" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;">http://nbis.se/about/staff/jacques-dainat
<a href="http://nbis.se" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGxNxynknDHkfoCeTsXXEuiYWfHow&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGxNxynknDHkfoCeTsXXEuiYWfHow&#39;;return true;">http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="nMnvD-1gBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">wim...@...> wrote:

Hi,

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script.

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo 
2.0.6.

In Apollo
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


--
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="nMnvD-1gBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">apo...@....



--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Wim S

The virtual machine on which we installed Apollo does not seems to have ample resources. Not sure how much the machine was used today, but it should not run easily out of CPYU or memory.

apollo monitoring.png



Op maandag 30 maart 2020 13:00:12 UTC+2 schreef Wim S:
Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes. 
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far.

To reproduce you could try this public reference genome and gene model:
<a href="ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz" target="_blank" rel="nofollow" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
<a href="ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3" target="_blank" rel="nofollow" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct.

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use.
Also part of the time CPU usage is only between 20% and 70%.

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation.
We don't (yet) have well defined DEV/QA/PROD installations.

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know.

Thank you.


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <[hidden email]> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called <a title="agat_sp_webApollo_compliant.pl" href="https://github.com/NBISweden/AGAT/blob/master/bin/agat_sp_webApollo_compliant.pl" style="color:rgb(3,102,214);text-decoration:none;font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;;font-size:14.000000953674316px;white-space:nowrap" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;">agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): <a href="https://github.com/NBISweden/AGAT" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;">https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
<a href="http://nbis.se/about/staff/jacques-dainat" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;">http://nbis.se/about/staff/jacques-dainat
<a href="http://nbis.se" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGxNxynknDHkfoCeTsXXEuiYWfHow&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGxNxynknDHkfoCeTsXXEuiYWfHow&#39;;return true;">http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <[hidden email]> wrote:

Hi,

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script.

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo 
2.0.6.

In Apollo
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].




--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn

What are the specs on that machine? 

Its also possible that multiple instances were started within that machine.  I would ‘ps -ef | grep catalina’  just in case. 

Nathan


On Mar 30, 2020, at 9:21 AM, Wim S <[hidden email]> wrote:

The virtual machine on which we installed Apollo does not seems to have ample resources. Not sure how much the machine was used today, but it should not run easily out of CPYU or memory. 
<apollo monitoring.png>


Op maandag 30 maart 2020 13:00:12 UTC+2 schreef Wim S:
Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@lbl.gov.




<apollo monitoring.png>

--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn
In reply to this post by Wim S

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <[hidden email]> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@lbl.gov.





--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Wim S

apollo monitoring.png



As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used.
There is only 1 Tomcat/catalina process running. 

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes.

2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE).

4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database.

I hope this information is useful.

Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: <a href="https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;">https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="wCy10pVgAgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">wim...@...> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
<a href="ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
<a href="ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@<a href="http://nbis.se/" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called <a title="agat_sp_webApollo_compliant.pl" href="https://github.com/NBISweden/AGAT/blob/master/bin/agat_sp_webApollo_compliant.pl" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(3,102,214);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;;font-size:14.000000953674316px;white-space:nowrap" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;">agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): <a href="https://github.com/NBISweden/AGAT" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;">https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
<a href="http://nbis.se/about/staff/jacques-dainat" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;">http://nbis.se/about/staff/jacques-dainat
<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@<a href="http://gmail.com/" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@<a href="http://lbl.gov/" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;">lbl.gov.






--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn

On Mar 31, 2020, at 6:14 AM, Wim S <[hidden email]> wrote:

<apollo monitoring.png>


As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used. 
There is only 1 Tomcat/catalina process running.  

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes. 

That is a goodly amount. 

How many organisms / users , etc. etc.  Probably doesn’t matter, but doesn’t hurt to cross things off the list. 


2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE). 

This is very helpful.  I’ll try to mimic and tune this for MySQL.  


4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

I know very productive groups promote everything as you and your colleagues do, even with a reasonably large set of annotations.

I also know folks that promote only changes to the predicted genes and then merge the GFF3 back later. 

We are working towards making this seamless regardless of your methodology, but not sure how long that will take. 

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database. 

I hope this information is useful. 

This was all very helpful. 

Hopefully I can make some inroads, otherwise I’ll try to suggest some workarounds. 

Will be working on the issue here: https://github.com/GMOD/Apollo/issues/2414

Nathan


Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <wim...@gmail.com> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@lbl.gov.






<apollo monitoring.png>

--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Wim S
Thank you for having a look. Most of the time the number of concurrent users in our Apollo installation is quite small, I would guess between 1 to 4.   Much more people have access.
We try to make the same data (e.g. gene models and RNAseq reads) available via multiple browsers and data tables.  Depending on the required level of detail and the desired type of interaction, people can choose in which browser or table to have a look.

There are something like 30 organism in our Apollo installation. For some organisms there are multiple (historical or different strain) references loaded to Apollo.
As can be guessed from the number of concurrent users, most of the time only a single or few organism are used concurrently.

Wim



Op dinsdag 31 maart 2020 15:43:24 UTC+2 schreef Nathan Dunn:

On Mar 31, 2020, at 6:14 AM, Wim S <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="sMUPXuiUAgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">wim...@...> wrote:

<apollo monitoring.png>


As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used. 
There is only 1 Tomcat/catalina process running.  

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes. 

That is a goodly amount. 

How many organisms / users , etc. etc.  Probably doesn’t matter, but doesn’t hurt to cross things off the list. 


2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE). 

This is very helpful.  I’ll try to mimic and tune this for MySQL.  


4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

I know very productive groups promote everything as you and your colleagues do, even with a reasonably large set of annotations.

I also know folks that promote only changes to the predicted genes and then merge the GFF3 back later. 

We are working towards making this seamless regardless of your methodology, but not sure how long that will take. 

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database. 

I hope this information is useful. 

This was all very helpful. 

Hopefully I can make some inroads, otherwise I’ll try to suggest some workarounds. 

Will be working on the issue here: <a href="https://github.com/GMOD/Apollo/issues/2414" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FGMOD%2FApollo%2Fissues%2F2414\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHlHqvs6CHDQttzpXFzssQ4S_C2Ew&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FGMOD%2FApollo%2Fissues%2F2414\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHlHqvs6CHDQttzpXFzssQ4S_C2Ew&#39;;return true;">https://github.com/GMOD/Apollo/issues/2414

Nathan


Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: <a href="https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;">https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <wim...@<a href="http://gmail.com/" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
<a href="ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
<a href="ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called <a title="agat_sp_webApollo_compliant.pl" href="https://github.com/NBISweden/AGAT/blob/master/bin/agat_sp_webApollo_compliant.pl" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(3,102,214);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;;font-size:14.000000953674316px;white-space:nowrap" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;">agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): <a href="https://github.com/NBISweden/AGAT" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;">https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
<a href="http://nbis.se/about/staff/jacques-dainat" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;">http://nbis.se/about/staff/jacques-dainat
<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@<a href="http://gmail.com/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@<a href="http://lbl.gov/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;">lbl.gov.






<apollo monitoring.png>


--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn

Perfect thanks. 

Nathan



On Mar 31, 2020, at 8:22 AM, Wim S <[hidden email]> wrote:

Thank you for having a look. Most of the time the number of concurrent users in our Apollo installation is quite small, I would guess between 1 to 4.   Much more people have access.
We try to make the same data (e.g. gene models and RNAseq reads) available via multiple browsers and data tables.  Depending on the required level of detail and the desired type of interaction, people can choose in which browser or table to have a look. 

There are something like 30 organism in our Apollo installation. For some organisms there are multiple (historical or different strain) references loaded to Apollo. 
As can be guessed from the number of concurrent users, most of the time only a single or few organism are used concurrently. 

Wim



Op dinsdag 31 maart 2020 15:43:24 UTC+2 schreef Nathan Dunn:

On Mar 31, 2020, at 6:14 AM, Wim S <wim...@gmail.com> wrote:

<apollo monitoring.png>


As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used. 
There is only 1 Tomcat/catalina process running.  

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes. 

That is a goodly amount. 

How many organisms / users , etc. etc.  Probably doesn’t matter, but doesn’t hurt to cross things off the list. 


2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE). 

This is very helpful.  I’ll try to mimic and tune this for MySQL.  


4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

I know very productive groups promote everything as you and your colleagues do, even with a reasonably large set of annotations.

I also know folks that promote only changes to the predicted genes and then merge the GFF3 back later. 

We are working towards making this seamless regardless of your methodology, but not sure how long that will take. 

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database. 

I hope this information is useful. 

This was all very helpful. 

Hopefully I can make some inroads, otherwise I’ll try to suggest some workarounds. 

Will be working on the issue here: https://github.com/GMOD/Apollo/issues/2414

Nathan


Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <wim...@gmail.com> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@lbl.gov.






<apollo monitoring.png>




--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn

A couple of suggestions as I have limited bandwidth (such the long delay):

1 - if you add additional logging (it will really slow things down) per https://genomearchitect.readthedocs.io/en/latest/Configure.html#logging-configuration  . . . adding the debug 'org.hibernate.SQL' and trace 'org.hibernate.type' will allow you to see any SQL statement that is hanging for a minute in the tomcat logs.  If you could share that it will narrow down the search.

2 - can you try this by dumping and moving to PostgreSQL and see if you encounter similar problems?   Conversely if you send me a dump privately (with a known user / password) and a couple of JBrowse directories I can probably more quickly assess the problem.

3 - Using a profiler such as YourKit (or others) can track IO through the JVM to pull out the bad SQL statements. 

Nathan


On Tuesday, March 31, 2020 at 8:53:26 AM UTC-7, Nathan Dunn wrote:

Perfect thanks. 

Nathan



On Mar 31, 2020, at 8:22 AM, Wim S <[hidden email]> wrote:

Thank you for having a look. Most of the time the number of concurrent users in our Apollo installation is quite small, I would guess between 1 to 4.   Much more people have access.
We try to make the same data (e.g. gene models and RNAseq reads) available via multiple browsers and data tables.  Depending on the required level of detail and the desired type of interaction, people can choose in which browser or table to have a look. 

There are something like 30 organism in our Apollo installation. For some organisms there are multiple (historical or different strain) references loaded to Apollo. 
As can be guessed from the number of concurrent users, most of the time only a single or few organism are used concurrently. 

Wim



Op dinsdag 31 maart 2020 15:43:24 UTC+2 schreef Nathan Dunn:

On Mar 31, 2020, at 6:14 AM, Wim S <wim...@<a href="http://gmail.com/" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

<apollo monitoring.png>


As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used. 
There is only 1 Tomcat/catalina process running.  

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes. 

That is a goodly amount. 

How many organisms / users , etc. etc.  Probably doesn’t matter, but doesn’t hurt to cross things off the list. 


2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE). 

This is very helpful.  I’ll try to mimic and tune this for MySQL.  


4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

I know very productive groups promote everything as you and your colleagues do, even with a reasonably large set of annotations.

I also know folks that promote only changes to the predicted genes and then merge the GFF3 back later. 

We are working towards making this seamless regardless of your methodology, but not sure how long that will take. 

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database. 

I hope this information is useful. 

This was all very helpful. 

Hopefully I can make some inroads, otherwise I’ll try to suggest some workarounds. 

Will be working on the issue here: <a href="https://github.com/GMOD/Apollo/issues/2414" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FGMOD%2FApollo%2Fissues%2F2414\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHlHqvs6CHDQttzpXFzssQ4S_C2Ew&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FGMOD%2FApollo%2Fissues%2F2414\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHlHqvs6CHDQttzpXFzssQ4S_C2Ew&#39;;return true;">https://github.com/GMOD/Apollo/issues/2414

Nathan


Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: <a href="https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;">https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <wim...@<a href="http://gmail.com/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
<a href="ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
<a href="ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called <a title="agat_sp_webApollo_compliant.pl" href="https://github.com/NBISweden/AGAT/blob/master/bin/agat_sp_webApollo_compliant.pl" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(3,102,214);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;;font-size:14.000000953674316px;white-space:nowrap" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;">agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): <a href="https://github.com/NBISweden/AGAT" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;">https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
<a href="http://nbis.se/about/staff/jacques-dainat" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;">http://nbis.se/about/staff/jacques-dainat
<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@<a href="http://gmail.com/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@<a href="http://lbl.gov/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;">lbl.gov.






<apollo monitoring.png>




--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Wim S
Thank you for the information. As a first step I enabled the logging. I asked the sys admin to share the tomcat log file with me.
Can you share to which other log file the output should be written? And where I can find these log files?

Switching chromosomes now takes longer, c.a. 4 minutes instead of c.a. half a minute.

log4j.main = {
            debug
'grails.app',
                 
'org.bbop.apollo',
                 
'org.hibernate.SQL'
            trace
'org.hibernate.type'



Op woensdag 8 april 2020 22:12:30 UTC+2 schreef Nathan Dunn:

A couple of suggestions as I have limited bandwidth (such the long delay):

1 - if you add additional logging (it will really slow things down) per <a href="https://genomearchitect.readthedocs.io/en/latest/Configure.html#logging-configuration" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FConfigure.html%23logging-configuration\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHRozYERagqIBTdt0iYe9S3wvxqhQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FConfigure.html%23logging-configuration\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHRozYERagqIBTdt0iYe9S3wvxqhQ&#39;;return true;">https://genomearchitect.readthedocs.io/en/latest/Configure.html#logging-configuration  . . . adding the debug 'org.hibernate.SQL' and trace 'org.hibernate.type' will allow you to see any SQL statement that is hanging for a minute in the tomcat logs.  If you could share that it will narrow down the search.

2 - can you try this by dumping and moving to PostgreSQL and see if you encounter similar problems?   Conversely if you send me a dump privately (with a known user / password) and a couple of JBrowse directories I can probably more quickly assess the problem.

3 - Using a profiler such as YourKit (or others) can track IO through the JVM to pull out the bad SQL statements. 

Nathan


On Tuesday, March 31, 2020 at 8:53:26 AM UTC-7, Nathan Dunn wrote:

Perfect thanks. 

Nathan



On Mar 31, 2020, at 8:22 AM, Wim S <<a href="javascript:" rel="nofollow" target="_blank" gdf-obfuscated-mailto="_PmNp0gaAAAJ" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">wim...@...> wrote:

Thank you for having a look. Most of the time the number of concurrent users in our Apollo installation is quite small, I would guess between 1 to 4.   Much more people have access.
We try to make the same data (e.g. gene models and RNAseq reads) available via multiple browsers and data tables.  Depending on the required level of detail and the desired type of interaction, people can choose in which browser or table to have a look. 

There are something like 30 organism in our Apollo installation. For some organisms there are multiple (historical or different strain) references loaded to Apollo. 
As can be guessed from the number of concurrent users, most of the time only a single or few organism are used concurrently. 

Wim



Op dinsdag 31 maart 2020 15:43:24 UTC+2 schreef Nathan Dunn:

On Mar 31, 2020, at 6:14 AM, Wim S <wim...@<a href="http://gmail.com/" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

<apollo monitoring.png>


As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used. 
There is only 1 Tomcat/catalina process running.  

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes. 

That is a goodly amount. 

How many organisms / users , etc. etc.  Probably doesn’t matter, but doesn’t hurt to cross things off the list. 


2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE). 

This is very helpful.  I’ll try to mimic and tune this for MySQL.  


4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

I know very productive groups promote everything as you and your colleagues do, even with a reasonably large set of annotations.

I also know folks that promote only changes to the predicted genes and then merge the GFF3 back later. 

We are working towards making this seamless regardless of your methodology, but not sure how long that will take. 

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database. 

I hope this information is useful. 

This was all very helpful. 

Hopefully I can make some inroads, otherwise I’ll try to suggest some workarounds. 

Will be working on the issue here: <a href="https://github.com/GMOD/Apollo/issues/2414" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FGMOD%2FApollo%2Fissues%2F2414\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHlHqvs6CHDQttzpXFzssQ4S_C2Ew&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FGMOD%2FApollo%2Fissues%2F2414\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHlHqvs6CHDQttzpXFzssQ4S_C2Ew&#39;;return true;">https://github.com/GMOD/Apollo/issues/2414

Nathan


Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: <a href="https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgenomearchitect.readthedocs.io%2Fen%2Flatest%2FTroubleshooting.html%23tomcat-memory\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGjQOgrmhsxftVFH8FFo3DpD9aZNw&#39;;return true;">https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <wim...@<a href="http://gmail.com/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
<a href="ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
<a href="ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;" onclick="this.href=&#39;ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3&#39;;return true;">ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called <a title="agat_sp_webApollo_compliant.pl" href="https://github.com/NBISweden/AGAT/blob/master/bin/agat_sp_webApollo_compliant.pl" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(3,102,214);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;;font-size:14.000000953674316px;white-space:nowrap" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT%2Fblob%2Fmaster%2Fbin%2Fagat_sp_webApollo_compliant.pl\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNG274bQrVuliYYJEFVvdXdIos1BiA&#39;;return true;">agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): <a href="https://github.com/NBISweden/AGAT" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FNBISweden%2FAGAT\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHMisWfqBC5Qz-LO4PKDz2_IR4Jbw&#39;;return true;">https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
<a href="http://nbis.se/about/staff/jacques-dainat" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2Fabout%2Fstaff%2Fjacques-dainat\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGwsMH2unnm6c3hZZ6XhgLffpZ4og&#39;;return true;">http://nbis.se/about/staff/jacques-dainat
<a href="http://nbis.se/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fnbis.se%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSEVRJEPxM-pzEaSFbeIzjy3OAZw&#39;;return true;">http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@<a href="http://gmail.com/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://gmail.com/&#39;;return true;" onclick="this.href=&#39;http://gmail.com/&#39;;return true;">gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@<a href="http://lbl.gov/" rel="nofollow" style="margin:0px;padding:0px;border:0px none;text-decoration:none;color:rgb(102,17,204)" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Flbl.gov%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHL-KZEv3o0yE1wlBJyjjaCCLY0Jw&#39;;return true;">lbl.gov.






<apollo monitoring.png>





--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

nathandunn

The tomcat log is the important one. 

If there is a way you get database logging, that would be amazing as well:


Cheers,

Nathan



On Apr 22, 2020, at 11:21 AM, Wim S <[hidden email]> wrote:

Thank you for the information. As a first step I enabled the logging. I asked the sys admin to share the tomcat log file with me. 
Can you share to which other log file the output should be written? And where I can find these log files?

Switching chromosomes now takes longer, c.a. 4 minutes instead of c.a. half a minute. 

log4j.main = {
            debug 
'grails.app',
                  
'org.bbop.apollo',
                  
'org.hibernate.SQL'
            trace 
'org.hibernate.type'



Op woensdag 8 april 2020 22:12:30 UTC+2 schreef Nathan Dunn:

A couple of suggestions as I have limited bandwidth (such the long delay):

1 - if you add additional logging (it will really slow things down) per https://genomearchitect.readthedocs.io/en/latest/Configure.html#logging-configuration  . . . adding the debug 'org.hibernate.SQL' and trace 'org.hibernate.type' will allow you to see any SQL statement that is hanging for a minute in the tomcat logs.  If you could share that it will narrow down the search.

2 - can you try this by dumping and moving to PostgreSQL and see if you encounter similar problems?   Conversely if you send me a dump privately (with a known user / password) and a couple of JBrowse directories I can probably more quickly assess the problem.

3 - Using a profiler such as YourKit (or others) can track IO through the JVM to pull out the bad SQL statements. 

Nathan


On Tuesday, March 31, 2020 at 8:53:26 AM UTC-7, Nathan Dunn wrote:

Perfect thanks. 

Nathan



On Mar 31, 2020, at 8:22 AM, Wim S <wim...@gmail.com> wrote:

Thank you for having a look. Most of the time the number of concurrent users in our Apollo installation is quite small, I would guess between 1 to 4.   Much more people have access.
We try to make the same data (e.g. gene models and RNAseq reads) available via multiple browsers and data tables.  Depending on the required level of detail and the desired type of interaction, people can choose in which browser or table to have a look. 

There are something like 30 organism in our Apollo installation. For some organisms there are multiple (historical or different strain) references loaded to Apollo. 
As can be guessed from the number of concurrent users, most of the time only a single or few organism are used concurrently. 

Wim



Op dinsdag 31 maart 2020 15:43:24 UTC+2 schreef Nathan Dunn:

On Mar 31, 2020, at 6:14 AM, Wim S <wim...@gmail.com> wrote:

<apollo monitoring.png>


As shown in the screenshot, the virtual machine has 8 CPU and 32GB of memory, of which only 10GB is used. 
There is only 1 Tomcat/catalina process running.  

1. Most species I think have around 30K genes. The highest that I could find via a quick check has 38K genes spread over 9 chromosomes. 

That is a goodly amount. 

How many organisms / users , etc. etc.  Probably doesn’t matter, but doesn’t hurt to cross things off the list. 


2&3
As far as I can tell most of the time is spend in the Java process. During the half minute, to a minute that it takes to switch chromosomes, I can see a java process running on the machine were were we installed Apollo. That process is at that time using between 20% to 100% CPU.  When not using the Apollo website, or just scrolling in the website, the java process only takes between 0% and 2% CPU. Our DB admin told me that the time is not spend in the MySQL database, but somewhere else.  Please let me know if you have further tips on to debug where the time is being spend (without having the run the code in a IDE). 

This is very helpful.  I’ll try to mimic and tune this for MySQL.  


4. My colleagues that use Apollo for their work thought it would be best to have the full set of genes loaded to the user created annotations track. This makes it easy to have an overview of the predicted and curated genes, and the RNAseq evidence. Otherwise another track always needs to be open to show the predicted (not (yet) curated) genes.  Is this also how other people use this track and how it is recommended to use this track?

I know very productive groups promote everything as you and your colleagues do, even with a reasonably large set of annotations.

I also know folks that promote only changes to the predicted genes and then merge the GFF3 back later. 

We are working towards making this seamless regardless of your methodology, but not sure how long that will take. 

5. Our DB admin profiled the load on the DB during the switching of chromosomes. He found he could make some minor improvements by adding an index. But nothing significant. According to our DB admin the vast majority of the  time is being spend outside of the MySQL database. 

I hope this information is useful. 

This was all very helpful. 

Hopefully I can make some inroads, otherwise I’ll try to suggest some workarounds. 

Will be working on the issue here: https://github.com/GMOD/Apollo/issues/2414

Nathan


Wim



Op maandag 30 maart 2020 23:44:35 UTC+2 schreef Nathan Dunn:

Just some quick clarifications / suggestions:

1 - how many annotations per chromosome do you have worst-case? 

2 - where is your CPU taking the longest . . in the DB or on a java process or Chrome? 

3 - is that a resting CPU at 20-70%?  If so, you need more memory: https://genomearchitect.readthedocs.io/en/latest/Troubleshooting.html#tomcat-memory

4 - f you can get away with ONLY promoting changes from your predicted GFF3 track into the manual (yellow) annotation panel at top (and then merging back at the end) it should work significantly better.   I realize that merging the GFF3’s is not ideal (and something we are working on), but it is where we are at.  

5 - I’ll look at potentially making MySQL improvements, including potentially adding missing indexes.  I’m backlogged right now, so it might be a week or so, but whatever you can clarify would be great.

Thanks,

Nathan


On Mar 30, 2020, at 4:00 AM, Wim S <wim...@gmail.com> wrote:

Hi Jacques and Nathan,

We have 10+ genomes and gene models loaded to Apollo 2.5.0. Most species also have something like 30K genes defined on the reference genomes.  
We noticed the slowness for multiple organism/species/reference genomes. We did not see an exception thus far. 

To reproduce you could try this public reference genome and gene model:
ftp://ftp.solgenomics.net/tomato_genome/assembly/build_2.50/S_lycopersicum_chromosomes.2.50.fa.gz
ftp://ftp.solgenomics.net/tomato_genome/annotation/ITAG2.4_release/ITAG2.4_gene_models.gff3

We are running a direct local installation with MySQL backend.  Our DB admin noticed that an index was missing / not correct. 

alter table feature add index (name(84)); -> was current max length of name
Could you pass that along to the developers?


During a top on the VM where we installed Apollo I only see for part of the duration of the switching of the chromosome 100% cpu use. 
Also part of the time CPU usage is only between 20% and 70%. 

Dumping and reloading the database and switching to PostgreSQL is a bit difficult since we already have people use the Apollo installation. 
We don't (yet) have well defined DEV/QA/PROD installations. 

Can you see if the linked genome and gene model can  or can't be used to reproduce the issue? Both would be interesting to know. 

Thank you. 


Op donderdag 26 maart 2020 15:53:46 UTC+1 schreef Nathan Dunn:


In general I would expect 10 seconds max for it to load between chromosomes, but I would have to look at your individual data. 

If either of you are able to send me a GFF3 + FASTA, I would generally use YourKit to evaluate them.   

Best-case I find a fix, worse-case I can use that data to benchmark future versions of Apollo.

A few other thoughts:

- how are you running it? I’m assuming direct installation because Docker and EC2 both use PostgreSQL. 

- it might be worth trying to dump the database and load it into a 2.5 instance instead of using the perl script, but it might be exactly the same

- there were some issues with indexing in mysql.  I can’t find it, but would be curious if you had the same performance problems with PostgreSQL

- do you see CPU spikes with top?  I’m guessing its just a crazy long query.  We should probably be doing more incremental fetches, but I want to see if that is the problem first. 

Thanks,

Nathan




On Mar 26, 2020, at 7:42 AM, Jacques Dainat <jacque...@nbis.se> wrote:

Hello,

We had slownesses too when migrating and realised that much more information where saved into the DB. A simple GFF annotation (~30 000 genes) could end-up in million of entry in the DB. 
Which make things sometimes lagging and slow when we have many genomes.
We found a way to improve the things by removing all useless information (attributes) from the GFF before to load it into the user track.
You can use our script called agat_sp_webApollo_compliant.pl 
The script is part of AGAT (Another Gff Analysis Toolkit): https://github.com/NBISweden/AGAT   
 
Best regards,

Jacques 

--------------------------------------------------------------------------------
Jacques Dainat, Ph.D.
NBIS (National Bioinformatics Infrastructure Sweden)
http://nbis.se/about/staff/jacques-dainat
http://nbis.se

                                   — Contact — 
Address: Uppsala University, Biomedicinska Centrum
Department of Medical Biochemistry Microbiology, Genomics
Husargatan 3, box 582
S-75123 Uppsala Sweden
Phone: +46 18 471 46 25
--------------------------------------------------------------------------------

On 26 Mar 2020, at 15:07, Wim S <wim...@gmail.com> wrote:

Hi, 

We updated from Apollo  2.0.6 to  Apollo 2.5.0.
We loaded data into the user created annotation track via the add_features_from_gff3_to_annotations
.pl script. 

The input ggf3 was either the original gff3 file or the gff3 file downloaded from Apollo  
2.0.6.

In Apollo 
2.0.6 it takes around 20 seconds to switch between different chromosomes for the same user created annotation track.

In Apollo 2.5.0  it takes around 40+ seconds to switch between different chromosomes for the same user created annotation track. Sometimes more than a minute.

We tried to keep the configuration as much as possible the same. Is it expected that switching chromosome now takes longer in Apollo 2.5.0?

My IT colleagues ruled out that the issue was on the MySQL backed.  The 2 potential causes left are the network and the Apollo2 website/VM as causes of the increased loading time. The network is the same as for apollo 2.0.6

Can you share what the expected loading time is? And how to further debug the increased loading time?

Thank you


URL
/apollo2/3085962…/jbrowse/data/trackList.json?v=0.7076918721941394
Duration
9.14 s (561.07 ms network transfer + 8.58 s resource loading)
 
URL
/apollo2/3085962…/AnnotationEditorService
Duration
1.1 min (1.0 min network transfer + 3.17 s resource loading)
*-> /apollo2/stomp/319/8cmmbx56/xhr_streaming


-- 
To unsubscribe from this group and stop receiving emails from it, send an email to apo...@lbl.gov.






<apollo monitoring.png>







--
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
Reply | Threaded
Open this post in threaded view
|

Re: How long should ik take to load the User created annotation track from the MySQL backend?

Wim S
Hi,

Here is already some logs from the database.





# Time:

200423 16:06:16
# User@Host: apollo2_user[apollo2_user] @ our_apollo_server.net [XX.XX.XXX.XXX]
# Thread_id: 1890016  Schema: apollo2_prod  QC_hit: No
# Query_time: 47.152124  Lock_time: 0.000182  Rows_sent: 17  Rows_examined: 18793282
use apollo2_prod;
SET timestamp
=1587650776;
select organism4_.id as col_0_0_, count(distinct feature0_.id) as col_1_0_, organism4_.id as id1_53_, organism4_.version as version2_53_, organism4_.abbreviation as abbrevia3_53_, organism4_.blatdb as blatdb4_53_, organism4_.comment as comment5_53_, organism4_.common_name as common_n6_53_, organism4_.data_added_via_web_services as data_add7_53_, organism4_.directory as director8_53_, organism4_.genome_fasta as genome_f9_53_, organism4_.genome_fasta_index as genome_10_53_, organism4_.genus as genus11_53_, organism4_.metadata as metadat12_53_, organism4_.non_default_translation_table as non_def13_53_, organism4_.obsolete as obsolet14_53_, organism4_.public_mode as public_15_53_, organism4_.species as species16_53_, organism4_.valid as valid17_53_ from feature feature0_ left outer join feature_relationship parentfeat1_ on feature0_.id=parentfeat1_.parent_feature_id inner join feature_location featureloc2_ on feature0_.id=featureloc2_.feature_id inner join sequence sequence3_ on featureloc2_.sequence_id=sequence3_.id inner join organism organism4_ on sequence3_.organism_id=organism4_.id where  not (exists (select childfeatu5_.id from feature_relationship childfeatu5_ where feature0_.id=childfeatu5_.child_feature_id)) and (organism4_.id in (16 , 29 , 18 , 21 , 37 , 15 , 28 , 20 , 8 , 19 , 14 , 13 , 24 , 17 , 6 , 34 , 32 , 4 , 12 , 23 , 25 , 43 , 9 , 26 , 27 , 30 , 11 , 31 , 7 , 39 , 33 , 38 , 10 , 22 , 3)) and (feature0_.class in ('org.bbop.apollo.RepeatRegion' , 'org.bbop.apollo.Terminator' , 'org.bbop.apollo.TransposableElement' , 'org.bbop.apollo.Gene' , 'org.bbop.apollo.Pseudogene' , 'org.bbop.apollo.Deletion' , 'org.bbop.apollo.Insertion' , 'org.bbop.apollo.Substitution' , 'org.bbop.apollo.SNV' , 'org.bbop.apollo.SNP' , 'org.bbop.apollo.MNV' , 'org.bbop.apollo.MNP' , 'org.bbop.apollo.Indel')) group by organism4_.id;
# Time: 200423 16:06:19
# User@Host: apollo2_user[apollo2_user] @ our_apollo_server.net [XX.XX.XXX.XXX]
# Thread_id: 1890016  Schema: apollo2_prod  QC_hit: No
# Query_time: 2.493831  Lock_time: 0.000120  Rows_sent: 1  Rows_examined: 1062868
SET timestamp
=1587650779;
select count(distinct sequenceal0_.id) as col_0_0_ from feature sequenceal0_ inner join feature_location featureloc1_ on sequenceal0_.id=featureloc1_.feature_id inner join sequence sequence2_ on featureloc1_.sequence_id=sequence2_.id inner join organism organism3_ on sequence2_.organism_id=organism3_.id inner join feature_property featurepro4_ on sequenceal0_.id=featurepro4_.feature_id where sequenceal0_.class in ('org.bbop.apollo.SequenceAlterationArtifact', 'org.bbop.apollo.DeletionArtifact', 'org.bbop.apollo.SubstitutionArtifact', 'org.bbop.apollo.InsertionArtifact') and featurepro4_.tag='justification' and (featurepro4_.value like 'Effect of %') and organism3_.id=43;
# Time: 200423 16:10:31
# User@Host: apollo2_user[apollo2_user] @ our_apollo_server.net [XX.XX.XXX.XXX]
# Thread_id: 1890016  Schema: apollo2_prod  QC_hit: No
# Query_time: 10.162820  Lock_time: 0.000559  Rows_sent: 198648  Rows_examined: 1825770
SET timestamp
=1587651031;
select this_.id as id1_27_22_, this_.version as version2_27_22_, this_.date_created as date_cre3_27_22_, this_.dbxref_id as dbxref_i4_27_22_, this_.description as descript5_27_22_, this_.is_analysis as is_analy6_27_22_, this_.is_obsolete as is_obsol7_27_22_, this_.last_updated as last_upd8_27_22_, this_.md5checksum as md9_27_22_, this_.name as name10_27_22_, this_.sequence_length as sequenc11_27_22_, this_.status_id as status_12_27_22_, this_.symbol as symbol13_27_22_, this_.unique_name as unique_14_27_22_, this_.alternate_cv_term as alterna16_27_22_, this_.class_name as class_n17_27_22_, this_.custom_alternate_cv_term as custom_18_27_22_, this_.custom_class_name as custom_19_27_22_, this_.custom_cv_term as custom_20_27_22_, this_.custom_ontology_id as custom_21_27_22_, this_.cv_term as cv_term22_27_22_, this_.meta_data as meta_da23_27_22_, this_.ontology_id as ontolog24_27_22_, this_.alteration_residue as alterat25_27_22_, this_.reference_allele_id as referen26_27_22_, this_.deletion_length as deletio27_27_22_, this_.analysis_feature_id as analysi28_27_22_, this_.class as class15_27_22_, childfeatu3_.child_feature_id as child_fe3_27_24_, childfeatu3_.id as id1_38_24_, childfeatu3_.id as id1_38_0_, childfeatu3_.version as version2_38_0_, childfeatu3_.child_feature_id as child_fe3_38_0_, childfeatu3_.parent_feature_id as parent_f4_38_0_, childfeatu3_.rank as rank5_38_0_, childfeatu3_.value as value6_38_0_, feature4_.id as id1_27_1_, feature4_.version as version2_27_1_, feature4_.date_created as date_cre3_27_1_, feature4_.dbxref_id as dbxref_i4_27_1_, feature4_.description as descript5_27_1_, feature4_.is_analysis as is_analy6_27_1_, feature4_.is_obsolete as is_obsol7_27_1_, feature4_.last_updated as last_upd8_27_1_, feature4_.md5checksum as md9_27_1_, feature4_.name as name10_27_1_, feature4_.sequence_length as sequenc11_27_1_, feature4_.status_id as status_12_27_1_, feature4_.symbol as symbol13_27_1_, feature4_.unique_name as unique_14_27_1_, feature4_.alternate_cv_term as alterna16_27_1_, feature4_.class_name as class_n17_27_1_, feature4_.custom_alternate_cv_term as custom_18_27_1_, feature4_.custom_class_name as custom_19_27_1_, feature4_.custom_cv_term as custom_20_27_1_, feature4_.custom_ontology_id as custom_21_27_1_, feature4_.cv_term as cv_term22_27_1_, feature4_.meta_data as meta_da23_27_1_, feature4_.ontology_id as ontolog24_27_1_, feature4_.alteration_residue as alterat25_27_1_, feature4_.reference_allele_id as referen26_27_1_, feature4_.deletion_length as deletio27_27_1_, feature4_.analysis_feature_id as analysi28_27_1_, feature4_.class as class15_27_1_, featureloc5_.feature_id as feature_3_27_25_, featureloc5_.id as id1_33_25_, featureloc5_.id as id1_33_2_, featureloc5_.version as version2_33_2_, featureloc5_.feature_id as feature_3_33_2_, featureloc5_.fmax as fmax4_33_2_, featureloc5_.fmin as fmin5_33_2_, featureloc5_.is_fmax_partial as is_fmax_6_33_2_, featureloc5_.is_fmin_partial as is_fmin_7_33_2_, featureloc5_.locgroup as locgroup8_33_2_, featureloc5_.phase as phase9_33_2_, featureloc5_.rank as rank10_33_2_, featureloc5_.residue_info as residue11_33_2_, featureloc5_.sequence_id as sequenc12_33_2_, featureloc5_.strand as strand13_33_2_, featureloc5_.FMAX-featureloc5_.FMIN as formula0_2_, sequence6_.id as id1_76_3_, sequence6_.version as version2_76_3_, sequence6_.sequence_end as sequence3_76_3_, sequence6_.length as length4_76_3_, sequence6_.name as name5_76_3_, sequence6_.organism_id as organism6_76_3_, sequence6_.seq_chunk_size as seq_chun7_76_3_, sequence6_.sequence_start as sequence8_76_3_, featuredbx7_.feature_featuredbxrefs_id as feature_1_27_26_, dbxref8_.id as dbxref_i2_28_26_, dbxref8_.id as id1_23_4_, dbxref8_.accession as accessio3_23_4_, dbxref8_.db_id as db_id4_23_4_, dbxref8_.description as descript5_23_4_, featureloc1_.id as id1_33_5_, featureloc1_.version as version2_33_5_, featureloc1_.feature_id as feature_3_33_5_, featureloc1_.fmax as fmax4_33_5_, featureloc1_.fmin as fmin5_33_5_, featureloc1_.is_fmax_partial as is_fmax_6_33_5_, featureloc1_.is_fmin_partial as is_fmin_7_33_5_, featureloc1_.locgroup as locgroup8_33_5_, featureloc1_.phase as phase9_33_5_, featureloc1_.rank as rank10_33_5_, featureloc1_.residue_info as residue11_33_5_, featureloc1_.sequence_id as sequenc12_33_5_, featureloc1_.strand as strand13_33_5_, featureloc1_.FMAX-featureloc1_.FMIN as formula0_5_, sequence10_.id as id1_76_6_, sequence10_.version as version2_76_6_, sequence10_.sequence_end as sequence3_76_6_, sequence10_.length as length4_76_6_, sequence10_.name as name5_76_6_, sequence10_.organism_id as organism6_76_6_, sequence10_.seq_chunk_size as seq_chun7_76_6_, sequence10_.sequence_start as sequence8_76_6_, featurepro11_.feature_id as feature_3_27_27_, featurepro11_.id as id1_35_27_, featurepro11_.id as id1_35_7_, featurepro11_.version as version2_35_7_, featurepro11_.feature_id as feature_3_35_7_, featurepro11_.rank as rank4_35_7_, featurepro11_.tag as tag5_35_7_, featurepro11_.type_id as type_id6_35_7_, featurepro11_.value as value7_35_7_, featurepro11_.class as class8_35_7_, owners12_.feature_owners_id as feature_1_27_28_, user13_.id as user_id2_32_28_, user13_.id as id1_50_8_, user13_.version as version2_50_8_, user13_.first_name as first_na3_50_8_, user13_.inactive as inactive4_50_8_, user13_.last_name as last_nam5_50_8_, user13_.metadata as metadata6_50_8_, user13_.password_hash as password7_50_8_, user13_.username as username8_50_8_, parentfeat14_.parent_feature_id as parent_f4_27_29_, parentfeat14_.id as id1_38_29_, parentfeat14_.id as id1_38_9_, parentfeat14_.version as version2_38_9_, parentfeat14_.child_feature_id as child_fe3_38_9_, parentfeat14_.parent_feature_id as parent_f4_38_9_, parentfeat14_.rank as rank5_38_9_, parentfeat14_.value as value6_38_9_, feature15_.id as id1_27_10_, feature15_.version as version2_27_10_, feature15_.date_created as date_cre3_27_10_, feature15_.dbxref_id as dbxref_i4_27_10_, feature15_.description as descript5_27_10_, feature15_.is_analysis as is_analy6_27_10_, feature15_.is_obsolete as is_obsol7_27_10_, feature15_.last_updated as last_upd8_27_10_, feature15_.md5checksum as md9_27_10_, feature15_.name as name10_27_10_, feature15_.sequence_length as sequenc11_27_10_, feature15_.status_id as status_12_27_10_, feature15_.symbol as symbol13_27_10_, feature15_.unique_name as unique_14_27_10_, feature15_.alternate_cv_term as alterna16_27_10_, feature15_.class_name as class_n17_27_10_, feature15_.custom_alternate_cv_term as custom_18_27_10_, feature15_.custom_class_name as custom_19_27_10_, feature15_.custom_cv_term as custom_20_27_10_, feature15_.custom_ontology_id as custom_21_27_10_, feature15_.cv_term as cv_term22_27_10_, feature15_.meta_data as meta_da23_27_10_, feature15_.ontology_id as ontolog24_27_10_, feature15_.alteration_residue as alterat25_27_10_, feature15_.reference_allele_id as referen26_27_10_, feature15_.deletion_length as deletio27_27_10_, feature15_.analysis_feature_id as analysi28_27_10_, feature15_.class as class15_27_10_, childfeatu16_.child_feature_id as child_fe3_27_30_, childfeatu16_.id as id1_38_30_, childfeatu16_.id as id1_38_11_, childfeatu16_.version as version2_38_11_, childfeatu16_.child_feature_id as child_fe3_38_11_, childfeatu16_.parent_feature_id as parent_f4_38_11_, childfeatu16_.rank as rank5_38_11_, childfeatu16_.value as value6_38_11_, featuredbx17_.feature_featuredbxrefs_id as feature_1_27_31_, dbxref18_.id as dbxref_i2_28_31_, dbxref18_.id as id1_23_12_, dbxref18_.accession as accessio3_23_12_, dbxref18_.db_id as db_id4_23_12_, dbxref18_.description as descript5_23_12_, featureloc19_.feature_id as feature_3_27_32_, featureloc19_.id as id1_33_32_, featureloc19_.id as id1_33_13_, featureloc19_.version as version2_33_13_, featureloc19_.feature_id as feature_3_33_13_, featureloc19_.fmax as fmax4_33_13_, featureloc19_.fmin as fmin5_33_13_, featureloc19_.is_fmax_partial as is_fmax_6_33_13_, featureloc19_.is_fmin_partial as is_fmin_7_33_13_, featureloc19_.locgroup as locgroup8_33_13_, featureloc19_.phase as phase9_33_13_, featureloc19_.rank as rank10_33_13_, featureloc19_.residue_info as residue11_33_13_, featureloc19_.sequence_id as sequenc12_33_13_, featureloc19_.strand as strand13_33_13_, featureloc19_.FMAX-featureloc19_.FMIN as formula0_13_, sequence20_.id as id1_76_14_, sequence20_.version as version2_76_14_, sequence20_.sequence_end as sequence3_76_14_, sequence20_.length as length4_76_14_, sequence20_.name as name5_76_14_, sequence20_.organism_id as organism6_76_14_, sequence20_.seq_chunk_size as seq_chun7_76_14_, sequence20_.sequence_start as sequence8_76_14_, featurepro21_.feature_id as feature_3_27_33_, featurepro21_.id as id1_35_33_, featurepro21_.id as id1_35_15_, featurepro21_.version as version2_35_15_, featurepro21_.feature_id as feature_3_35_15_, featurepro21_.rank as rank4_35_15_, featurepro21_.tag as tag5_35_15_, featurepro21_.type_id as type_id6_35_15_, featurepro21_.value as value7_35_15_, featurepro21_.class as class8_35_15_, owners22_.feature_owners_id as feature_1_27_34_, user23_.id as user_id2_32_34_, user23_.id as id1_50_16_, user23_.version as version2_50_16_, user23_.first_name as first_na3_50_16_, user23_.inactive as inactive4_50_16_, user23_.last_name as last_nam5_50_16_, user23_.metadata as metadata6_50_16_, user23_.password_hash as password7_50_16_, user23_.username as username8_50_16_, parentfeat24_.parent_feature_id as parent_f4_27_35_, parentfeat24_.id as id1_38_35_, parentfeat24_.id as id1_38_17_, parentfeat24_.version as version2_38_17_, parentfeat24_.child_feature_id as child_fe3_38_17_, parentfeat24_.parent_feature_id as parent_f4_38_17_, parentfeat24_.rank as rank5_38_17_, parentfeat24_.value as value6_38_17_, feature25_.id as id1_27_18_, feature25_.version as version2_27_18_, feature25_.date_created as date_cre3_27_18_, feature25_.dbxref_id as dbxref_i4_27_18_, feature25_.description as descript5_27_18_, feature25_.is_analysis as is_analy6_27_18_, feature25_.is_obsolete as is_obsol7_27_18_, feature25_.last_updated as last_upd8_27_18_, feature25_.md5checksum as md9_27_18_, feature25_.name as name10_27_18_, feature25_.sequence_length as sequenc11_27_18_, feature25_.status_id as status_12_27_18_, feature25_.symbol as symbol13_27_18_, feature25_.unique_name as unique_14_27_18_, feature25_.alternate_cv_term as alterna16_27_18_, feature25_.class_name as class_n17_27_18_, feature25_.custom_alternate_cv_term as custom_18_27_18_, feature25_.custom_class_name as custom_19_27_18_, feature25_.custom_cv_term as custom_20_27_18_, feature25_.custom_ontology_id as custom_21_27_18_, feature25_.cv_term as cv_term22_27_18_, feature25_.meta_data as meta_da23_27_18_, feature25_.ontology_id as ontolog24_27_18_, feature25_.alteration_residue as alterat25_27_18_, feature25_.reference_allele_id as referen26_27_18_, feature25_.deletion_length as deletio27_27_18_, feature25_.analysis_feature_id as analysi28_27_18_, feature25_.class as class15_27_18_, featureloc26_.feature_id as feature_3_27_36_, featureloc26_.id as id1_33_36_, featureloc26_.id as id1_33_19_, featureloc26_.version as version2_33_19_, featureloc26_.feature_id as feature_3_33_19_, featureloc26_.fmax as fmax4_33_19_, featureloc26_.fmin as fmin5_33_19_, featureloc26_.is_fmax_partial as is_fmax_6_33_19_, featureloc26_.is_fmin_partial as is_fmin_7_33_19_, featureloc26_.locgroup as locgroup8_33_19_, featureloc26_.phase as phase9_33_19_, featureloc26_.rank as rank10_33_19_, featureloc26_.residue_info as residue11_33_19_, featureloc26_.sequence_id as sequenc12_33_19_, featureloc26_.strand as strand13_33_19_, featureloc26_.FMAX-featureloc26_.FMIN as formula0_19_, sequence27_.id as id1_76_20_, sequence27_.version as version2_76_20_, sequence27_.sequence_end as sequence3_76_20_, sequence27_.length as length4_76_20_, sequence27_.name as name5_76_20_, sequence27_.organism_id as organism6_76_20_, sequence27_.seq_chunk_size as seq_chun7_76_20_, sequence27_.sequence_start as sequence8_76_20_, status28_.id as id1_35_21_, status28_.version as version2_35_21_, status28_.feature_id as feature_3_35_21_, status28_.rank as rank4_35_21_, status28_.tag as tag5_35_21_, status28_.type_id as type_id6_35_21_, status28_.value as value7_35_21_ from feature this_ left outer join feature_relationship childfeatu3_ on this_.id=childfeatu3_.child_feature_id left outer join feature feature4_ on childfeatu3_.parent_feature_id=feature4_.id left outer join feature_location featureloc5_ on feature4_.id=featureloc5_.feature_id left outer join sequence sequence6_ on featureloc5_.sequence_id=sequence6_.id left outer join feature_dbxref featuredbx7_ on this_.id=featuredbx7_.feature_featuredbxrefs_id left outer join dbxref dbxref8_ on featuredbx7_.dbxref_id=dbxref8_.id inner join feature_location featureloc1_ on this_.id=featureloc1_.feature_id left outer join sequence sequence10_ on featureloc1_.sequence_id=sequence10_.id left outer join feature_property featurepro11_ on this_.id=featurepro11_.feature_id left outer join feature_grails_user owners12_ on this_.id=owners12_.feature_owners_id left outer join grails_user user13_ on owners12_.user_id=user13_.id left outer join feature_relationship parentfeat14_ on this_.id=parentfeat14_.parent_feature_id left outer join feature feature15_ on parentfeat14_.child_feature_id=feature15_.id left outer join feature_relationship childfeatu16_ on feature15_.id=childfeatu16_.child_feature_id left outer join feature_dbxref featuredbx17_ on feature15_.id=featuredbx17_.feature_featuredbxrefs_id left outer join dbxref dbxref18_ on featuredbx17_.dbxref_id=dbxref18_.id left outer join feature_location featureloc19_ on feature15_.id=featureloc19_.feature_id left outer join sequence sequence20_ on featureloc19_.sequence_id=sequence20_.id left outer join feature_property featurepro21_ on feature15_.id=featurepro21_.feature_id left outer join feature_grails_user owners22_ on feature15_.id=owners22_.feature_owners_id left outer join grails_user user23_ on owners22_.user_id=user23_.id left outer join feature_relationship parentfeat24_ on feature15_.id=parentfeat24_.parent_feature_id left outer join feature feature25_ on parentfeat14_.parent_feature_id=feature25_.id left outer join feature_location featureloc26_ on feature25_.id=featureloc26_.feature_id left outer join sequence sequence27_ on featureloc26_.sequence_id=sequence27_.id left outer join feature_property status28_ on this_.status_id=status28_.id where (featureloc1_.sequence_id=36) and this_.class in ('org.bbop.apollo.Transcript', 'org.bbop.apollo.MRNA', 'org.bbop.apollo.TRNA', 'org.bbop.apollo.SnRNA', 'org.bbop.apollo.SnoRNA', 'org.bbop.apollo.NcRNA', 'org.bbop.apollo.RRNA', 'org.bbop.apollo.MiRNA', 'org.bbop.apollo.RepeatRegion', 'org.bbop.apollo.Terminator', 'org.bbop.apollo.TransposableElement', 'org.bbop.apollo.Deletion', 'org.bbop.apollo.Insertion', 'org.bbop.apollo.Substitution', 'org.bbop.apollo.SNV', 'org.bbop.apollo.SNP', 'org.bbop.apollo.MNV', 'org.bbop.apollo.MNP', 'org.bbop.apollo.Indel');
# Time: 200423 16:19:06
# User@Host: apollo2_user[apollo2_user] @ our_apollo_server.net [XX.XX.XXX.XXX]
# Thread_id: 1890196  Schema: apollo2_prod  QC_hit: No
# Query_time: 5.548233  Lock_time: 0.000579  Rows_sent: 112748  Rows_examined: 1039283
SET timestamp
=1587651546;
select this_.id as id1_27_22_, this_.version as version2_27_22_, this_.date_created as date_cre3_27_22_, this_.dbxref_id as dbxref_i4_27_22_, this_.description as descript5_27_22_, this_.is_analysis as is_analy6_27_22_, this_.is_obsolete as is_obsol7_27_22_, this_.last_updated as last_upd8_27_22_, this_.md5checksum as md9_27_22_, this_.name as name10_27_22_, this_.sequence_length as sequenc11_27_22_, this_.status_id as status_12_27_22_, this_.symbol as symbol13_27_22_, this_.unique_name as unique_14_27_22_, this_.alternate_cv_term as alterna16_27_22_, this_.class_name as class_n17_27_22_, this_.custom_alternate_cv_term as custom_18_27_22_, this_.custom_class_name as custom_19_27_22_, this_.custom_cv_term as custom_20_27_22_, this_.custom_ontology_id as custom_21_27_22_, this_.cv_term as cv_term22_27_22_, this_.meta_data as meta_da23_27_22_, this_.ontology_id as ontolog24_27_22_, this_.alteration_residue as alterat25_27_22_, this_.reference_allele_id as referen26_27_22_, this_.deletion_length as deletio27_27_22_, this_.analysis_feature_id as analysi28_27_22_, this_.class as class15_27_22_, childfeatu3_.child_feature_id as child_fe3_27_24_, childfeatu3_.id as id1_38_24_, childfeatu3_.id as id1_38_0_, childfeatu3_.version as version2_38_0_, childfeatu3_.child_feature_id as child_fe3_38_0_, childfeatu3_.parent_feature_id as parent_f4_38_0_, childfeatu3_.rank as rank5_38_0_, childfeatu3_.value as value6_38_0_, feature4_.id as id1_27_1_, feature4_.version as version2_27_1_, feature4_.date_created as date_cre3_27_1_, feature4_.dbxref_id as dbxref_i4_27_1_, feature4_.description as descript5_27_1_, feature4_.is_analysis as is_analy6_27_1_, feature4_.is_obsolete as is_obsol7_27_1_, feature4_.last_updated as last_upd8_27_1_, feature4_.md5checksum as md9_27_1_, feature4_.name as name10_27_1_, feature4_.sequence_length as sequenc11_27_1_, feature4_.status_id as status_12_27_1_, feature4_.symbol as symbol13_27_1_, feature4_.unique_name as unique_14_27_1_, feature4_.alternate_cv_term as alterna16_27_1_, feature4_.class_name as class_n17_27_1_, feature4_.custom_alternate_cv_term as custom_18_27_1_, feature4_.custom_class_name as custom_19_27_1_, feature4_.custom_cv_term as custom_20_27_1_, feature4_.custom_ontology_id as custom_21_27_1_, feature4_.cv_term as cv_term22_27_1_, feature4_.meta_data as meta_da23_27_1_, feature4_.ontology_id as ontolog24_27_1_, feature4_.alteration_residue as alterat25_27_1_, feature4_.reference_allele_id as referen26_27_1_, feature4_.deletion_length as deletio27_27_1_, feature4_.analysis_feature_id as analysi28_27_1_, feature4_.class as class15_27_1_, featureloc5_.feature_id as feature_3_27_25_, featureloc5_.id as id1_33_25_, featureloc5_.id as id1_33_2_, featureloc5_.version as version2_33_2_, featureloc5_.feature_id as feature_3_33_2_, featureloc5_.fmax as fmax4_33_2_, featureloc5_.fmin as fmin5_33_2_, featureloc5_.is_fmax_partial as is_fmax_6_33_2_, featureloc5_.is_fmin_partial as is_fmin_7_33_2_, featureloc5_.locgroup as locgroup8_33_2_, featureloc5_.phase as phase9_33_2_, featureloc5_.rank as rank10_33_2_, featureloc5_.residue_info as residue11_33_2_, featureloc5_.sequence_id as sequenc12_33_2_, featureloc5_.strand as strand13_33_2_, featureloc5_.FMAX-featureloc5_.FMIN as formula0_2_, sequence6_.id as id1_76_3_, sequence6_.version as version2_76_3_, sequence6_.sequence_end as sequence3_76_3_, sequence6_.length as length4_76_3_, sequence6_.name as name5_76_3_, sequence6_.organism_id as organism6_76_3_, sequence6_.seq_chunk_size as seq_chun7_76_3_, sequence6_.sequence_start as sequence8_76_3_, featuredbx7_.feature_featuredbxrefs_id as feature_1_27_26_, dbxref8_.id as dbxref_i2_28_26_, dbxref8_.id as id1_23_4_, dbxref8_.accession as accessio3_23_4_, dbxref8_.db_id as db_id4_23_4_, dbxref8_.description as descript5_23_4_, featureloc1_.id as id1_33_5_, featureloc1_.version as version2_33_5_, featureloc1_.feature_id as feature_3_33_5_, featureloc1_.fmax as fmax4_33_5_, featureloc1_.fmin as fmin5_33_5_, featureloc1_.is_fmax_partial as is_fmax_6_33_5_, featureloc1_.is_fmin_partial as is_fmin_7_33_5_, featureloc1_.locgroup as locgroup8_33_5_, featureloc1_.phase as phase9_33_5_, featureloc1_.rank as rank10_33_5_, featureloc1_.residue_info as residue11_33_5_, featureloc1_.sequence_id as sequenc12_33_5_, featureloc1_.strand as strand13_33_5_, featureloc1_.FMAX-featureloc1_.FMIN as formula0_5_, sequence10_.id as id1_76_6_, sequence10_.version as version2_76_6_, sequence10_.sequence_end as sequence3_76_6_, sequence10_.length as length4_76_6_, sequence10_.name as name5_76_6_, sequence10_.organism_id as organism6_76_6_, sequence10_.seq_chunk_size as seq_chun7_76_6_, sequence10_.sequence_start as sequence8_76_6_, featurepro11_.feature_id as feature_3_27_27_, featurepro11_.id as id1_35_27_, featurepro11_.id as id1_35_7_, featurepro11_.version as version2_35_7_, featurepro11_.feature_id as feature_3_35_7_, featurepro11_.rank as rank4_35_7_, featurepro11_.tag as tag5_35_7_, featurepro11_.type_id as type_id6_35_7_, featurepro11_.value as value7_35_7_, featurepro11_.class as class8_35_7_, owners12_.feature_owners_id as feature_1_27_28_, user13_.id as user_id2_32_28_, user13_.id as id1_50_8_, user13_.version as version2_50_8_, user13_.first_name as first_na3_50_8_, user13_.inactive as inactive4_50_8_, user13_.last_name as last_nam5_50_8_, user13_.metadata as metadata6_50_8_, user13_.password_hash as password7_50_8_, user13_.username as username8_50_8_, parentfeat14_.parent_feature_id as parent_f4_27_29_, parentfeat14_.id as id1_38_29_, parentfeat14_.id as id1_38_9_, parentfeat14_.version as version2_38_9_, parentfeat14_.child_feature_id as child_fe3_38_9_, parentfeat14_.parent_feature_id as parent_f4_38_9_, parentfeat14_.rank as rank5_38_9_, parentfeat14_.value as value6_38_9_, feature15_.id as id1_27_10_, feature15_.version as version2_27_10_, feature15_.date_created as date_cre3_27_10_, feature15_.dbxref_id as dbxref_i4_27_10_, feature15_.description as descript5_27_10_, feature15_.is_analysis as is_analy6_27_10_, feature15_.is_obsolete as is_obsol7_27_10_, feature15_.last_updated as last_upd8_27_10_, feature15_.md5checksum as md9_27_10_, feature15_.name as name10_27_10_, feature15_.sequence_length as sequenc11_27_10_, feature15_.status_id as status_12_27_10_, feature15_.symbol as symbol13_27_10_, feature15_.unique_name as unique_14_27_10_, feature15_.alternate_cv_term as alterna16_27_10_, feature15_.class_name as class_n17_27_10_, feature15_.custom_alternate_cv_term as custom_18_27_10_, feature15_.custom_class_name as custom_19_27_10_, feature15_.custom_cv_term as custom_20_27_10_, feature15_.custom_ontology_id as custom_21_27_10_, feature15_.cv_term as cv_term22_27_10_, feature15_.meta_data as meta_da23_27_10_, feature15_.ontology_id as ontolog24_27_10_, feature15_.alteration_residue as alterat25_27_10_, feature15_.reference_allele_id as referen26_27_10_, feature15_.deletion_length as deletio27_27_10_, feature15_.analysis_feature_id as analysi28_27_10_, feature15_.class as class15_27_10_, childfeatu16_.child_feature_id as child_fe3_27_30_, childfeatu16_.id as id1_38_30_, childfeatu16_.id as id1_38_11_, childfeatu16_.version as version2_38_11_, childfeatu16_.child_feature_id as child_fe3_38_11_, childfeatu16_.parent_feature_id as parent_f4_38_11_, childfeatu16_.rank as rank5_38_11_, childfeatu16_.value as value6_38_11_, featuredbx17_.feature_featuredbxrefs_id as feature_1_27_31_, dbxref18_.id as dbxref_i2_28_31_, dbxref18_.id as id1_23_12_, dbxref18_.accession as accessio3_23_12_, dbxref18_.db_id as db_id4_23_12_, dbxref18_.description as descript5_23_12_, featureloc19_.feature_id as feature_3_27_32_, featureloc19_.id as id1_33_32_, featureloc19_.id as id1_33_13_, featureloc19_.version as version2_33_13_, featureloc19_.feature_id as feature_3_33_13_, featureloc19_.fmax as fmax4_33_13_, featureloc19_.fmin as fmin5_33_13_, featureloc19_.is_fmax_partial as is_fmax_6_33_13_, featureloc19_.is_fmin_partial as is_fmin_7_33_13_, featureloc19_.locgroup as locgroup8_33_13_, featureloc19_.phase as phase9_33_13_, featureloc19_.rank as rank10_33_13_, featureloc19_.residue_info as residue11_33_13_, featureloc19_.sequence_id as sequenc12_33_13_, featureloc19_.strand as strand13_33_13_, featureloc19_.FMAX-featureloc19_.FMIN as formula0_13_, sequence20_.id as id1_76_14_, sequence20_.version as version2_76_14_, sequence20_.sequence_end as sequence3_76_14_, sequence20_.length as length4_76_14_, sequence20_.name as name5_76_14_, sequence20_.organism_id as organism6_76_14_, sequence20_.seq_chunk_size as seq_chun7_76_14_, sequence20_.sequence_start as sequence8_76_14_, featurepro21_.feature_id as feature_3_27_33_, featurepro21_.id as id1_35_33_, featurepro21_.id as id1_35_15_, featurepro21_.version as version2_35_15_, featurepro21_.feature_id as feature_3_35_15_, featurepro21_.rank as rank4_35_15_, featurepro21_.tag as tag5_35_15_, featurepro21_.type_id as type_id6_35_15_, featurepro21_.value as value7_35_15_, featurepro21_.class as class8_35_15_, owners22_.feature_owners_id as feature_1_27_34_, user23_.id as user_id2_32_34_, user23_.id as id1_50_16_, user23_.version as version2_50_16_, user23_.first_name as first_na3_50_16_, user23_.inactive as inactive4_50_16_, user23_.last_name as last_nam5_50_16_, user23_.metadata as metadata6_50_16_, user23_.password_hash as password7_50_16_, user23_.username as username8_50_16_, parentfeat24_.parent_feature_id as parent_f4_27_35_, parentfeat24_.id as id1_38_35_, parentfeat24_.id as id1_38_17_, parentfeat24_.version as version2_38_17_, parentfeat24_.child_feature_id as child_fe3_38_17_, parentfeat24_.parent_feature_id as parent_f4_38_17_, parentfeat24_.rank as rank5_38_17_, parentfeat24_.value as value6_38_17_, feature25_.id as id1_27_18_, feature25_.version as version2_27_18_, feature25_.date_created as date_cre3_27_18_, feature25_.dbxref_id as dbxref_i4_27_18_, feature25_.description as descript5_27_18_, feature25_.is_analysis as is_analy6_27_18_, feature25_.is_obsolete as is_obsol7_27_18_, feature25_.last_updated as last_upd8_27_18_, feature25_.md5checksum as md9_27_18_, feature25_.name as name10_27_18_, feature25_.sequence_length as sequenc11_27_18_, feature25_.status_id as status_12_27_18_, feature25_.symbol as symbol13_27_18_, feature25_.unique_name as unique_14_27_18_, feature25_.alternate_cv_term as alterna16_27_18_, feature25_.class_name as class_n17_27_18_, feature25_.custom_alternate_cv_term as custom_18_27_18_, feature25_.custom_class_name as custom_19_27_18_, feature25_.custom_cv_term as custom_20_27_18_, feature25_.custom_ontology_id as custom_21_27_18_, feature25_.cv_term as cv_term22_27_18_, feature25_.meta_data as meta_da23_27_18_, feature25_.ontology_id as ontolog24_27_18_, feature25_.alteration_residue as alterat25_27_18_, feature25_.reference_allele_id as referen26_27_18_, feature25_.deletion_length as deletio27_27_18_, feature25_.analysis_feature_id as analysi28_27_18_, feature25_.class as class15_27_18_, featureloc26_.feature_id as feature_3_27_36_, featureloc26_.id as id1_33_36_, featureloc26_.id as id1_33_19_, featureloc26_.version as version2_33_19_, featureloc26_.feature_id as feature_3_33_19_, featureloc26_.fmax as fmax4_33_19_, featureloc26_.fmin as fmin5_33_19_, featureloc26_.is_fmax_partial as is_fmax_6_33_19_, featureloc26_.is_fmin_partial as is_fmin_7_33_19_, featureloc26_.locgroup as locgroup8_33_19_, featureloc26_.phase as phase9_33_19_, featureloc26_.rank as rank10_33_19_, featureloc26_.residue_info as residue11_33_19_, featureloc26_.sequence_id as sequenc12_33_19_, featureloc26_.strand as strand13_33_19_, featureloc26_.FMAX-featureloc26_.FMIN as formula0_19_, sequence27_.id as id1_76_20_, sequence27_.version as version2_76_20_, sequence27_.sequence_end as sequence3_76_20_, sequence27_.length as length4_76_20_, sequence27_.name as name5_76_20_, sequence27_.organism_id as organism6_76_20_, sequence27_.seq_chunk_size as seq_chun7_76_20_, sequence27_.sequence_start as sequence8_76_20_, status28_.id as id1_35_21_, status28_.version as version2_35_21_, status28_.feature_id as feature_3_35_21_, status28_.rank as rank4_35_21_, status28_.tag as tag5_35_21_, status28_.type_id as type_id6_35_21_, status28_.value as value7_35_21_ from feature this_ left outer join feature_relationship childfeatu3_ on this_.id=childfeatu3_.child_feature_id left outer join feature feature4_ on childfeatu3_.parent_feature_id=feature4_.id left outer join feature_location featureloc5_ on feature4_.id=featureloc5_.feature_id left outer join sequence sequence6_ on featureloc5_.sequence_id=sequence6_.id left outer join feature_dbxref featuredbx7_ on this_.id=featuredbx7_.feature_featuredbxrefs_id left outer join dbxref dbxref8_ on featuredbx7_.dbxref_id=dbxref8_.id inner join feature_location featureloc1_ on this_.id=featureloc1_.feature_id left outer join sequence sequence10_ on featureloc1_.sequence_id=sequence10_.id left outer join feature_property featurepro11_ on this_.id=featurepro11_.feature_id left outer join feature_grails_user owners12_ on this_.id=owners12_.feature_owners_id left outer join grails_user user13_ on owners12_.user_id=user13_.id left outer join feature_relationship parentfeat14_ on this_.id=parentfeat14_.parent_feature_id left outer join feature feature15_ on parentfeat14_.child_feature_id=feature15_.id left outer join feature_relationship childfeatu16_ on feature15_.id=childfeatu16_.child_feature_id left outer join feature_dbxref featuredbx17_ on feature15_.id=featuredbx17_.feature_featuredbxrefs_id left outer join dbxref dbxref18_ on featuredbx17_.dbxref_id=dbxref18_.id left outer join feature_location featureloc19_ on feature15_.id=featureloc19_.feature_id left outer join sequence sequence20_ on featureloc19_.sequence_id=sequence20_.id left outer join feature_property featurepro21_ on feature15_.id=featurepro21_.feature_id left outer join feature_grails_user owners22_ on feature15_.id=owners22_.feature_owners_id left outer join grails_user user23_ on owners22_.user_id=user23_.id left outer join feature_relationship parentfeat24_ on feature15_.id=parentfeat24_.parent_feature_id left outer join feature feature25_ on parentfeat14_.parent_feature_id=feature25_.id left outer join feature_location featureloc26_ on feature25_.id=featureloc26_.feature_id left outer join sequence sequence27_ on featureloc26_.sequence_id=sequence27_.id left outer join feature_property status28_ on this_.status_id=status28_.id where (featureloc1_.sequence_id=43) and this_.class in ('org.bbop.apollo.Transcript', 'org.bbop.apollo.MRNA', 'org.bbop.apollo.TRNA', 'org.bbop.apollo.SnRNA', 'org.bbop.apollo.SnoRNA', 'org.bbop.apollo.NcRNA', 'org.bbop.apollo.RRNA', 'org.bbop.apollo.MiRNA', 'org.bbop.apollo.RepeatRegion', 'org.bbop.apollo.Terminator', 'org.bbop.apollo.TransposableElement', 'org.bbop.apollo.Deletion', 'org.bbop.apollo.Insertion', 'org.bbop.apollo.Substitution', 'org.bbop.apollo.SNV', 'org.bbop.apollo.SNP', 'org.bbop.apollo.MNV', 'org.bbop.apollo.MNP', 'org.bbop.apollo.Indel');
# Time: 200423 16:24:09
# User@Host: apollo2_user[apollo2_user] @ our_apollo_server.net [XX.XX.XXX.XXX]
# Thread_id: 1890214  Schema: apollo2_prod  QC_hit: No
# Query_time: 5.494590  Lock_time: 0.000611  Rows_sent: 108431  Rows_examined: 1009693
SET timestamp
=1587651849;
select this_.id as id1_27_22_, this_.version as version2_27_22_, this_.date_created as date_cre3_27_22_, this_.dbxref_id as dbxref_i4_27_22_, this_.description as descript5_27_22_, this_.is_analysis as is_analy6_27_22_, this_.is_obsolete as is_obsol7_27_22_, this_.last_updated as last_upd8_27_22_, this_.md5checksum as md9_27_22_, this_.name as name10_27_22_, this_.sequence_length as sequenc11_27_22_, this_.status_id as status_12_27_22_, this_.symbol as symbol13_27_22_, this_.unique_name as unique_14_27_22_, this_.alternate_cv_term as alterna16_27_22_, this_.class_name as class_n17_27_22_, this_.custom_alternate_cv_term as custom_18_27_22_, this_.custom_class_name as custom_19_27_22_, this_.custom_cv_term as custom_20_27_22_, this_.custom_ontology_id as custom_21_27_22_, this_.cv_term as cv_term22_27_22_, this_.meta_data as meta_da23_27_22_, this_.ontology_id as ontolog24_27_22_, this_.alteration_residue as alterat25_27_22_, this_.reference_allele_id as referen26_27_22_, this_.deletion_length as deletio27_27_22_, this_.analysis_feature_id as analysi28_27_22_, this_.class as class15_27_22_, childfeatu3_.child_feature_id as child_fe3_27_24_, childfeatu3_.id as id1_38_24_, childfeatu3_.id as id1_38_0_, childfeatu3_.version as version2_38_0_, childfeatu3_.child_feature_id as child_fe3_38_0_, childfeatu3_.parent_feature_id as parent_f4_38_0_, childfeatu3_.rank as rank5_38_0_, childfeatu3_.value as value6_38_0_, feature4_.id as id1_27_1_, feature4_.version as version2_27_1_, feature4_.date_created as date_cre3_27_1_, feature4_.dbxref_id as dbxref_i4_27_1_, feature4_.description as descript5_27_1_, feature4_.is_analysis as is_analy6_27_1_, feature4_.is_obsolete as is_obsol7_27_1_, feature4_.last_updated as last_upd8_27_1_, feature4_.md5checksum as md9_27_1_, feature4_.name as name10_27_1_, feature4_.sequence_length as sequenc11_27_1_, feature4_.status_id as status_12_27_1_, feature4_.symbol as symbol13_27_1_, feature4_.unique_name as unique_14_27_1_, feature4_.alternate_cv_term as alterna16_27_1_, feature4_.class_name as class_n17_27_1_, feature4_.custom_alternate_cv_term as custom_18_27_1_, feature4_.custom_class_name as custom_19_27_1_, feature4_.custom_cv_term as custom_20_27_1_, feature4_.custom_ontology_id as custom_21_27_1_, feature4_.cv_term as cv_term22_27_1_, feature4_.meta_data as meta_da23_27_1_, feature4_.ontology_id as ontolog24_27_1_, feature4_.alteration_residue as alterat25_27_1_, feature4_.reference_allele_id as referen26_27_1_, feature4_.deletion_length as deletio27_27_1_, feature4_.analysis_feature_id as analysi28_27_1_, feature4_.class as class15_27_1_, featureloc5_.feature_id as feature_3_27_25_, featureloc5_.id as id1_33_25_, featureloc5_.id as id1_33_2_, featureloc5_.version as version2_33_2_, featureloc5_.feature_id as feature_3_33_2_, featureloc5_.fmax as fmax4_33_2_, featureloc5_.fmin as fmin5_33_2_, featureloc5_.is_fmax_partial as is_fmax_6_33_2_, featureloc5_.is_fmin_partial as is_fmin_7_33_2_, featureloc5_.locgroup as locgroup8_33_2_, featureloc5_.phase as phase9_33_2_, featureloc5_.rank as rank10_33_2_, featureloc5_.residue_info as residue11_33_2_, featureloc5_.sequence_id as sequenc12_33_2_, featureloc5_.strand as strand13_33_2_, featureloc5_.FMAX-featureloc5_.FMIN as formula0_2_, sequence6_.id as id1_76_3_, sequence6_.version as version2_76_3_, sequence6_.sequence_end as sequence3_76_3_, sequence6_.length as length4_76_3_, sequence6_.name as name5_76_3_, sequence6_.organism_id as organism6_76_3_, sequence6_.seq_chunk_size as seq_chun7_76_3_, sequence6_.sequence_start as sequence8_76_3_, featuredbx7_.feature_featuredbxrefs_id as feature_1_27_26_, dbxref8_.id as dbxref_i2_28_26_, dbxref8_.id as id1_23_4_, dbxref8_.accession as accessio3_23_4_, dbxref8_.db_id as db_id4_23_4_, dbxref8_.description as descript5_23_4_, featureloc1_.id as id1_33_5_, featureloc1_.version as version2_33_5_, featureloc1_.feature_id as feature_3_33_5_, featureloc1_.fmax as fmax4_33_5_, featureloc1_.fmin as fmin5_33_5_, featureloc1_.is_fmax_partial as is_fmax_6_33_5_, featureloc1_.is_fmin_partial as is_fmin_7_33_5_, featureloc1_.locgroup as locgroup8_33_5_, featureloc1_.phase as phase9_33_5_, featureloc1_.rank as rank10_33_5_, featureloc1_.residue_info as residue11_33_5_, featureloc1_.sequence_id as sequenc12_33_5_, featureloc1_.strand as strand13_33_5_, featureloc1_.FMAX-featureloc1_.FMIN as formula0_5_, sequence10_.id as id1_76_6_, sequence10_.version as version2_76_6_, sequence10_.sequence_end as sequence3_76_6_, sequence10_.length as length4_76_6_, sequence10_.name as name5_76_6_, sequence10_.organism_id as organism6_76_6_, sequence10_.seq_chunk_size as seq_chun7_76_6_, sequence10_.sequence_start as sequence8_76_6_, featurepro11_.feature_id as feature_3_27_27_, featurepro11_.id as id1_35_27_, featurepro11_.id as id1_35_7_, featurepro11_.version as version2_35_7_, featurepro11_.feature_id as feature_3_35_7_, featurepro11_.rank as rank4_35_7_, featurepro11_.tag as tag5_35_7_, featurepro11_.type_id as type_id6_35_7_, featurepro11_.value as value7_35_7_, featurepro11_.class as class8_35_7_, owners12_.feature_owners_id as feature_1_27_28_, user13_.id as user_id2_32_28_, user13_.id as id1_50_8_, user13_.version as version2_50_8_, user13_.first_name as first_na3_50_8_, user13_.inactive as inactive4_50_8_, user13_.last_name as last_nam5_50_8_, user13_.metadata as metadata6_50_8_, user13_.password_hash as password7_50_8_, user13_.username as username8_50_8_, parentfeat14_.parent_feature_id as parent_f4_27_29_, parentfeat14_.id as id1_38_29_, parentfeat14_.id as id1_38_9_, parentfeat14_.version as version2_38_9_, parentfeat14_.child_feature_id as child_fe3_38_9_, parentfeat14_.parent_feature_id as parent_f4_38_9_, parentfeat14_.rank as rank5_38_9_, parentfeat14_.value as value6_38_9_, feature15_.id as id1_27_10_, feature15_.version as version2_27_10_, feature15_.date_created as date_cre3_27_10_, feature15_.dbxref_id as dbxref_i4_27_10_, feature15_.description as descript5_27_10_, feature15_.is_analysis as is_analy6_27_10_, feature15_.is_obsolete as is_obsol7_27_10_, feature15_.last_updated as last_upd8_27_10_, feature15_.md5checksum as md9_27_10_, feature15_.name as name10_27_10_, feature15_.sequence_length as sequenc11_27_10_, feature15_.status_id as status_12_27_10_, feature15_.symbol as symbol13_27_10_, feature15_.unique_name as unique_14_27_10_, feature15_.alternate_cv_term as alterna16_27_10_, feature15_.class_name as class_n17_27_10_, feature15_.custom_alternate_cv_term as custom_18_27_10_, feature15_.custom_class_name as custom_19_27_10_, feature15_.custom_cv_term as custom_20_27_10_, feature15_.custom_ontology_id as custom_21_27_10_, feature15_.cv_term as cv_term22_27_10_, feature15_.meta_data as meta_da23_27_10_, feature15_.ontology_id as ontolog24_27_10_, feature15_.alteration_residue as alterat25_27_10_, feature15_.reference_allele_id as referen26_27_10_, feature15_.deletion_length as deletio27_27_10_, feature15_.analysis_feature_id as analysi28_27_10_, feature15_.class as class15_27_10_, childfeatu16_.child_feature_id as child_fe3_27_30_, childfeatu16_.id as id1_38_30_, childfeatu16_.id as id1_38_11_, childfeatu16_.version as version2_38_11_, childfeatu16_.child_feature_id as child_fe3_38_11_, childfeatu16_.parent_feature_id as parent_f4_38_11_, childfeatu16_.rank as rank5_38_11_, childfeatu16_.value as value6_38_11_, featuredbx17_.feature_featuredbxrefs_id as feature_1_27_31_, dbxref18_.id as dbxref_i2_28_31_, dbxref18_.id as id1_23_12_, dbxref18_.accession as accessio3_23_12_, dbxref18_.db_id as db_id4_23_12_, dbxref18_.description as descript5_23_12_, featureloc19_.feature_id as feature_3_27_32_, featureloc19_.id as id1_33_32_, featureloc19_.id as id1_33_13_, featureloc19_.version as version2_33_13_, featureloc19_.feature_id as feature_3_33_13_, featureloc19_.fmax as fmax4_33_13_, featureloc19_.fmin as fmin5_33_13_, featureloc19_.is_fmax_partial as is_fmax_6_33_13_, featureloc19_.is_fmin_partial as is_fmin_7_33_13_, featureloc19_.locgroup as locgroup8_33_13_, featureloc19_.phase as phase9_33_13_, featureloc19_.rank as rank10_33_13_, featureloc19_.residue_info as residue11_33_13_, featureloc19_.sequence_id as sequenc12_33_13_, featureloc19_.strand as strand13_33_13_, featureloc19_.FMAX-featureloc19_.FMIN as formula0_13_, sequence20_.id as id1_76_14_, sequence20_.version as version2_76_14_, sequence20_.sequence_end as sequence3_76_14_, sequence20_.length as length4_76_14_, sequence20_.name as name5_76_14_, sequence20_.organism_id as organism6_76_14_, sequence20_.seq_chunk_size as seq_chun7_76_14_, sequence20_.sequence_start as sequence8_76_14_, featurepro21_.feature_id as feature_3_27_33_, featurepro21_.id as id1_35_33_, featurepro21_.id as id1_35_15_, featurepro21_.version as version2_35_15_, featurepro21_.feature_id as feature_3_35_15_, featurepro21_.rank as rank4_35_15_, featurepro21_.tag as tag5_35_15_, featurepro21_.type_id as type_id6_35_15_, featurepro21_.value as value7_35_15_, featurepro21_.class as class8_35_15_, owners22_.feature_owners_id as feature_1_27_34_, user23_.id as user_id2_32_34_, user23_.id as id1_50_16_, user23_.version as version2_50_16_, user23_.first_name as first_na3_50_16_, user23_.inactive as inactive4_50_16_, user23_.last_name as last_nam5_50_16_, user23_.metadata as metadata6_50_16_, user23_.password_hash as password7_50_16_, user23_.username as username8_50_16_, parentfeat24_.parent_feature_id as parent_f4_27_35_, parentfeat24_.id as id1_38_35_, parentfeat24_.id as id1_38_17_, parentfeat24_.version as version2_38_17_, parentfeat24_.child_feature_id as child_fe3_38_17_, parentfeat24_.parent_feature_id as parent_f4_38_17_, parentfeat24_.rank as rank5_38_17_, parentfeat24_.value as value6_38_17_, feature25_.id as id1_27_18_, feature25_.version as version2_27_18_, feature25_.date_created as date_cre3_27_18_, feature25_.dbxref_id as dbxref_i4_27_18_, feature25_.description as descript5_27_18_, feature25_.is_analysis as is_analy6_27_18_, feature25_.is_obsolete as is_obsol7_27_18_, feature25_.last_updated as last_upd8_27_18_, feature25_.md5checksum as md9_27_18_, feature25_.name as name10_27_18_, feature25_.sequence_length as sequenc11_27_18_, feature25_.status_id as status_12_27_18_, feature25_.symbol as symbol13_27_18_, feature25_.unique_name as unique_14_27_18_, feature25_.alternate_cv_term as alterna16_27_18_, feature25_.class_name as class_n17_27_18_, feature25_.custom_alternate_cv_term as custom_18_27_18_, feature25_.custom_class_name as custom_19_27_18_, feature25_.custom_cv_term as custom_20_27_18_, feature25_.custom_ontology_id as custom_21_27_18_, feature25_.cv_term as cv_term22_27_18_, feature25_.meta_data as meta_da23_27_18_, feature25_.ontology_id as ontolog24_27_18_, feature25_.alteration_residue as alterat25_27_18_, feature25_.reference_allele_id as referen26_27_18_, feature25_.deletion_length as deletio27_27_18_, feature25_.analysis_feature_id as analysi28_27_18_, feature25_.class as class15_27_18_, featureloc26_.feature_id as feature_3_27_36_, featureloc26_.id as id1_33_36_, featureloc26_.id as id1_33_19_, featureloc26_.version as version2_33_19_, featureloc26_.feature_id as feature_3_33_19_, featureloc26_.fmax as fmax4_33_19_, featureloc26_.fmin as fmin5_33_19_, featureloc26_.is_fmax_partial as is_fmax_6_33_19_, featureloc26_.is_fmin_partial as is_fmin_7_33_19_, featureloc26_.locgroup as locgroup8_33_19_, featureloc26_.phase as phase9_33_19_, featureloc26_.rank as rank10_33_19_, featureloc26_.residue_info as residue11_33_19_, featureloc26_.sequence_id as sequenc12_33_19_, featureloc26_.strand as strand13_33_19_, featureloc26_.FMAX-featureloc26_.FMIN as formula0_19_, sequence27_.id as id1_76_20_, sequence27_.version as version2_76_20_, sequence27_.sequence_end as sequence3_76_20_, sequence27_.length as length4_76_20_, sequence27_.name as name5_76_20_, sequence27_.organism_id as organism6_76_20_, sequence27_.seq_chunk_size as seq_chun7_76_20_, sequence27_.sequence_start as sequence8_76_20_, status28_.id as id1_35_21_, status28_.version as version2_35_21_, status28_.feature_id as feature_3_35_21_, status28_.rank as rank4_35_21_, status28_.tag as tag5_35_21_, status28_.type_id as type_id6_35_21_, status28_.value as value7_35_21_ from feature this_ left outer join feature_relationship childfeatu3_ on this_.id=childfeatu3_.child_feature_id left outer join feature feature4_ on childfeatu3_.parent_feature_id=feature4_.id left outer join feature_location featureloc5_ on feature4_.id=featureloc5_.feature_id left outer join sequence sequence6_ on featureloc5_.sequence_id=sequence6_.id left outer join feature_dbxref featuredbx7_ on this_.id=featuredbx7_.feature_featuredbxrefs_id left outer join dbxref dbxref8_ on featuredbx7_.dbxref_id=dbxref8_.id inner join feature_location featureloc1_ on this_.id=featureloc1_.feature_id left outer join sequence sequence10_ on featureloc1_.sequence_id=sequence10_.id left outer join feature_property featurepro11_ on this_.id=featurepro11_.feature_id left outer join feature_grails_user owners12_ on this_.id=owners12_.feature_owners_id left outer join grails_user user13_ on owners12_.user_id=user13_.id left outer join feature_relationship parentfeat14_ on this_.id=parentfeat14_.parent_feature_id left outer join feature feature15_ on parentfeat14_.child_feature_id=feature15_.id left outer join feature_relationship childfeatu16_ on feature15_.id=childfeatu16_.child_feature_id left outer join feature_dbxref featuredbx17_ on feature15_.id=featuredbx17_.feature_featuredbxrefs_id left outer join dbxref dbxref18_ on featuredbx17_.dbxref_id=dbxref18_.id left outer join feature_location featureloc19_ on feature15_.id=featureloc19_.feature_id left outer join sequence sequence20_ on featureloc19_.sequence_id=sequence20_.id left outer join feature_property featurepro21_ on feature15_.id=featurepro21_.feature_id left outer join feature_grails_user owners22_ on feature15_.id=owners22_.feature_owners_id left outer join grails_user user23_ on owners22_.user_id=user23_.id left outer join feature_relationship parentfeat24_ on feature15_.id=parentfeat24_.parent_feature_id left outer join feature feature25_ on parentfeat14_.parent_feature_id=feature25_.id left outer join feature_location featureloc26_ on feature25_.id=featureloc26_.feature_id left outer join sequence sequence27_ on featureloc26_.sequence_id=sequence27_.id left outer join feature_property status28_ on this_.status_id=status28_.id where (featureloc1_.sequence_id=45) and this_.class in ('org.bbop.apollo.Transcript', 'org.bbop.apollo.MRNA', 'org.bbop.apollo.TRNA', 'org.bbop.apollo.SnRNA', 'org.bbop.apollo.SnoRNA', 'org.bbop.apollo.NcRNA', 'org.bbop.apollo.RRNA', 'org.bbop.apollo.MiRNA', 'org.bbop.apollo.RepeatRegion', 'org.bbop.apollo.Terminator', 'org.bbop.apollo.TransposableElement', 'org.bbop.apollo.Deletion', 'org.bbop.apollo.Insertion', 'org.bbop.apollo.Substitution', 'org.bbop.apollo.SNV', 'org.bbop.apollo.SNP', 'org.bbop.apollo.MNV', 'org.bbop.apollo.MNP', 'org.bbop.apollo.Indel');
# Time: 200423 16:30:03
# User@Host: apollo2_user[apollo2_user] @ our_apollo_server.net [XX.XX.XXX.XXX]
# Thread_id: 1890270  Schema: apollo2_prod  QC_hit: No
# Query_time: 5.789369  Lock_time: 0.000647  Rows_sent: 114021  Rows_examined: 1052266
SET timestamp
=1587652203;
select this_.id as id1_27_22_, this_.version as version2_27_22_, this_.date_created as date_cre3_27_22_, this_.dbxref_id as dbxref_i4_27_22_, this_.description as descript5_27_22_, this_.is_analysis as is_analy6_27_22_, this_.is_obsolete as is_obsol7_27_22_, this_.last_updated as last_upd8_27_22_, this_.md5checksum as md9_27_22_, this_.name as name10_27_22_, this_.sequence_length as sequenc11_27_22_, this_.status_id as status_12_27_22_, this_.symbol as symbol13_27_22_, this_.unique_name as unique_14_27_22_, this_.alternate_cv_term as alterna16_27_22_, this_.class_name as class_n17_27_22_, this_.custom_alternate_cv_term as custom_18_27_22_, this_.custom_class_name as custom_19_27_22_, this_.custom_cv_term as custom_20_27_22_, this_.custom_ontology_id as custom_21_27_22_, this_.cv_term as cv_term22_27_22_, this_.meta_data as meta_da23_27_22_, this_.ontology_id as ontolog24_27_22_, this_.alteration_residue as alterat25_27_22_, this_.reference_allele_id as referen26_27_22_, this_.deletion_length as deletio27_27_22_, this_.analysis_feature_id as analysi28_27_22_, this_.class as class15_27_22_, childfeatu3_.child_feature_id as child_fe3_27_24_, childfeatu3_.id as id1_38_24_, childfeatu3_.id as id1_38_0_, childfeatu3_.version as version2_38_0_, childfeatu3_.child_feature_id as child_fe3_38_0_, childfeatu3_.parent_feature_id as parent_f4_38_0_, childfeatu3_.rank as rank5_38_0_, childfeatu3_.value as value6_38_0_, feature4_.id as id1_27_1_, feature4_.version as version2_27_1_, feature4_.date_created as date_cre3_27_1_, feature4_.dbxref_id as dbxref_i4_27_1_, feature4_.description as descript5_27_1_, feature4_.is_analysis as is_analy6_27_1_, feature4_.is_obsolete as is_obsol7_27_1_, feature4_.last_updated as last_upd8_27_1_, feature4_.md5checksum as md9_27_1_, feature4_.name as name10_27_1_, feature4_.sequence_length as sequenc11_27_1_, feature4_.status_id as status_12_27_1_, feature4_.symbol as symbol13_27_1_, feature4_.unique_name as unique_14_27_1_, feature4_.alternate_cv_term as alterna16_27_1_, feature4_.class_name as class_n17_27_1_, feature4_.custom_alternate_cv_term as custom_18_27_1_, feature4_.custom_class_name as custom_19_27_1_, feature4_.custom_cv_term as custom_20_27_1_, feature4_.custom_ontology_id as custom_21_27_1_, feature4_.cv_term as cv_term22_27_1_, feature4_.meta_data as meta_da23_27_1_, feature4_.ontology_id as ontolog24_27_1_, feature4_.alteration_residue as alterat25_27_1_, feature4_.reference_allele_id as referen26_27_1_, feature4_.deletion_length as deletio27_27_1_, feature4_.analysis_feature_id as analysi28_27_1_, feature4_.class as class15_27_1_, featureloc5_.feature_id as feature_3_27_25_, featureloc5_.id as id1_33_25_, featureloc5_.id as id1_33_2_, featureloc5_.version as version2_33_2_, featureloc5_.feature_id as feature_3_33_2_, featureloc5_.fmax as fmax4_33_2_, featureloc5_.fmin as fmin5_33_2_, featureloc5_.is_fmax_partial as is_fmax_6_33_2_, featureloc5_.is_fmin_partial as is_fmin_7_33_2_, featureloc5_.locgroup as locgroup8_33_2_, featureloc5_.phase as phase9_33_2_, featureloc5_.rank as rank10_33_2_, featureloc5_.residue_info as residue11_33_2_, featureloc5_.sequence_id as sequenc12_33_2_, featureloc5_.strand as strand13_33_2_, featureloc5_.FMAX-featureloc5_.FMIN as formula0_2_, sequence6_.id as id1_76_3_, sequence6_.version as version2_76_3_, sequence6_.sequence_end as sequence3_76_3_, sequence6_.length as length4_76_3_, sequence6_.name as name5_76_3_, sequence6_.organism_id as organism6_76_3_, sequence6_.seq_chunk_size as seq_chun7_76_3_, sequence6_.sequence_start as sequence8_76_3_, featuredbx7_.feature_featuredbxrefs_id as feature_1_27_26_, dbxref8_.id as dbxref_i2_28_26_, dbxref8_.id as id1_23_4_, dbxref8_.accession as accessio3_23_4_, dbxref8_.db_id as db_id4_23_4_, dbxref8_.description as descript5_23_4_, featureloc1_.id as id1_33_5_, featureloc1_.version as version2_33_5_, featureloc1_.feature_id as feature_3_33_5_, featureloc1_.fmax as fmax4_33_5_, featureloc1_.fmin as fmin5_33_5_, featureloc1_.is_fmax_partial as is_fmax_6_33_5_, featureloc1_.is_fmin_partial as is_fmin_7_33_5_, featureloc1_.locgroup as locgroup8_33_5_, featureloc1_.phase as phase9_33_5_, featureloc1_.rank as rank10_33_5_, featureloc1_.residue_info as residue11_33_5_, featureloc1_.sequence_id as sequenc12_33_5_, featureloc1_.strand as strand13_33_5_, featureloc1_.FMAX-featureloc1_.FMIN as formula0_5_, sequence10_.id as id1_76_6_, sequence10_.version as version2_76_6_, sequence10_.sequence_end as sequence3_76_6_, sequence10_.length as length4_76_6_, sequence10_.name as name5_76_6_, sequence10_.organism_id as organism6_76_6_, sequence10_.seq_chunk_size as seq_chun7_76_6_, sequence10_.sequence_start as sequence8_76_6_, featurepro11_.feature_id as feature_3_27_27_, featurepro11_.id as id1_35_27_, featurepro11_.id as id1_35_7_, featurepro11_.version as version2_35_7_, featurepro11_.feature_id as feature_3_35_7_, featurepro11_.rank as rank4_35_7_, featurepro11_.tag as tag5_35_7_, featurepro11_.type_id as type_id6_35_7_, featurepro11_.value as value7_35_7_, featurepro11_.class as class8_35_7_, owners12_.feature_owners_id as feature_1_27_28_, user13_.id as user_id2_32_28_, user13_.id as id1_50_8_, user13_.version as version2_50_8_, user13_.first_name as first_na3_50_8_, user13_.inactive as inactive4_50_8_, user13_.last_name as last_nam5_50_8_, user13_.metadata as metadata6_50_8_, user13_.password_hash as password7_50_8_, user13_.username as username8_50_8_, parentfeat14_.parent_feature_id as parent_f4_27_29_, parentfeat14_.id as id1_38_29_, parentfeat14_.id as id1_38_9_, parentfeat14_.version as version2_38_9_, parentfeat14_.child_feature_id as child_fe3_38_9_, parentfeat14_.parent_feature_id as parent_f4_38_9_, parentfeat14_.rank as rank5_38_9_, parentfeat14_.value as value6_38_9_, feature15_.id as id1_27_10_, feature15_.version as version2_27_10_, feature15_.date_created as date_cre3_27_10_, feature15_.dbxref_id as dbxref_i4_27_10_, feature15_.description as descript5_27_10_, feature15_.is_analysis as is_analy6_27_10_, feature15_.is_obsolete as is_obsol7_27_10_, feature15_.last_updated as last_upd8_27_10_, feature15_.md5checksum as md9_27_10_, feature15_.name as name10_27_10_, feature15_.sequence_length as sequenc11_27_10_, feature15_.status_id as status_12_27_10_, feature15_.symbol as symbol13_27_10_, feature15_.unique_name as unique_14_27_10_, feature15_.alternate_cv_term as alterna16_27_10_, feature15_.class_name as class_n17_27_10_, feature15_.custom_alternate_cv_term as custom_18_27_10_, feature15_.custom_class_name as custom_19_27_10_, feature15_.custom_cv_term as custom_20_27_10_, feature15_.custom_ontology_id as custom_21_27_10_, feature15_.cv_term as cv_term22_27_10_, feature15_.meta_data as meta_da23_27_10_, feature15_.ontology_id as ontolog24_27_10_, feature15_.alteration_residue as alterat25_27_10_, feature15_.reference_allele_id as referen26_27_10_, feature15_.deletion_length as deletio27_27_10_, feature15_.analysis_feature_id as analysi28_27_10_, feature15_.class as class15_27_10_, childfeatu16_.child_feature_id as child_fe3_27_30_, childfeatu16_.id as id1_38_30_, childfeatu16_.id as id1_38_11_, childfeatu16_.version as version2_38_11_, childfeatu16_.child_feature_id as child_fe3_38_11_, childfeatu16_.parent_feature_id as parent_f4_38_11_, childfeatu16_.rank as rank5_38_11_, childfeatu16_.value as value6_38_11_, featuredbx17_.feature_featuredbxrefs_id as feature_1_27_31_, dbxref18_.id as dbxref_i2_28_31_, dbxref18_.id as id1_23_12_, dbxref18_.accession as accessio3_23_12_, dbxref18_.db_id as db_id4_23_12_, dbxref18_.description as descript5_23_12_, featureloc19_.feature_id as feature_3_27_32_, featureloc19_.id as id1_33_32_, featureloc19_.id as id1_33_13_, featureloc19_.version as version2_33_13_, featureloc19_.feature_id as feature_3_33_13_, featureloc19_.fmax as fmax4_33_13_, featureloc19_.fmin as fmin5_33_13_, featureloc19_.is_fmax_partial as is_fmax_6_33_13_, featureloc19_.is_fmin_partial as is_fmin_7_33_13_, featureloc19_.locgroup as locgroup8_33_13_, featureloc19_.phase as phase9_33_13_, featureloc19_.rank as rank10_33_13_, featureloc19_.residue_info as residue11_33_13_,