Tripal: Long load times for gff3 loads

[mpoelchau]

BUG/ERROR report

System information

• Tripal Version: 2
• Drupal Version: 7.69
• PostgreSQL Version: 9.6.17
• PHP Version: 7.3.16

Issue description

Loading gff3 files into the feature table via the GFF3 loader has been taking a really long time. The last GFF3 file that we loaded had 16,017 genes and 24,093 mRNAs (we load subfeatures such as exons as well). It took about 4 days to load on our production site. We’re wondering if the problem is the feature table size - it currently has 3,303,837 entries. The same file took 14 hours to load on our dev site, where the feature table has 2,091,629 entries (although there are some other differences between the sites, as well - but the php, postgres, and drupal versions are the same). Other larger files similarly take much longer to load on our production site than our dev site.

@srobb1 suggested to dump/backup of the db, then in the webform in the advanced options deselect the 'Use a transaction'. Also vacuuming the database before loading can sometimes help as well.

@spficklin wonders if the GFF3 loader needs to be looked at for performance issues: "For every single feature property, dbxref, etc that gets added it must check for integrity constraints and that adds a lot of time. The suggestions @Sofia Robb gives can help, but I suspect the underlying problem is a very large feature table and the time it takes to check integrity before inserts."

If there are any suggestions others might have about how to reduce the load time - in particular with larger feature tables - I would love to hear them!

Steps to reproduce

• Loaded genome fasta file with Tripal fasta loader
• Loaded gff3 file with Tripal fasta loader
• I can provide the files that I loaded if it's helpful.

[par12005]

@spficklin, I have submitted PR #1057 which tries to resolve some performance issues and implements some unit tests. Please take a look when you get the chance!

[spficklin]

Hi all, Just an update on this issue.

I worked on it a bit this weekend and I realized to make the GFF3 loader run faster we need to completely rework it to support multiple inserts at once as described in issue #1068. So, I spent time doing that. I'm not finished but here's where I'm at. I can load all feature, featureloc, featureprop, and feature_synonym records from the rice IGRP v1.0 GFF3 file (with 584,334 features) in 2 minutes on an empty Tripal site! It takes a lot more memory (~4GB) though as it takes advantage of a lot of in-memory caching.

It still needs quite a few other items (i.e. dbxrefs, relationships, etc.). But I'll work on it as I can fit in time. But these initial results are looking great.

[spficklin]

Oh, anyone who is interested in jumping in, the code is on this branch: 1040-tv3-gff3_performance. It contains earlier work by @par12005 with the PR he submitted (#1057). I build from his code but this update is really a full rewrite...

[spficklin]

Hi folks as an update... We now have the Tripal v3 GFF3 uploader almost completely rebuilt. It is missing support for the Target attribute and adding protein sequences if they are not present.

As far as performance goes, I can fully import the entire IGRSP _Oryza sativa_ v1.0 GFF3 file with > 584,481 lines (44,754 transcripts) in 3 minutes and it consumes ~4GB of RAM. Syncing genes and then mRNA takes another 3 minutes each. About 10 minutes total to get everything into the site and published online. This is on a completely empty Tripal setup so it may take longer on a site with data.

If anyone is interested in testing (please do so only on a development machine), you can pull the version of Tripal in the branch 1040-tv3-gff3_performance and see how it goes. I think it's ready for testing as long as you don't need the items that are missing. If anyone would like to test it will help us find potential problems now rather than later!

[spficklin]

As another update. We now have a completely rewritten GFF3 loader that supports all functionality of the previous uploader but loads extremely fast (compared to the previous). For example, I can load a GFF3 (the one shared by @Ferrisx4) about 250MB in size, with 2,241,372 features in 9 minutes and maxing at 2.3GB of RAM. After that was loaded, I next loaded a 144GB Araport 11 GFF3 file for Arabidopsis with 789,890 features in 5 minutes (that with the 2M records already in the feature table). That was followed by 545,371 features from the IRGSP v1.0 Oryza sativa genome with 545,371 features in 3 minutes. In all, I was able to load 3 full genomes in less than 20 minutes.

The loader still needs some thorough debugging and some functional tests, but is ready for another round of tests if others would like to try it on a development Tripal site.

[risharde]

Resolved a small but major glitch with feature indexing which caused data integrity issues.
Pull created: https://github.com/tripal/tripal/pull/1101

[spficklin]

The updated GFF3 loader is now merged into the development branch and ready for the next release! So, I'm closing this out.