Bug #2805


Metacat Performance: updates from Morpho of data packages are taking longer than five minutes

Added by Callie Bowdish over 16 years ago. Updated over 15 years ago.

Target version:
Start date:
Due date:
% Done:


Estimated time:


When a data set is saved online it is a quick process. However, when it is saved or updated using Morpho it can take longer than five minutes. Recent documents have all been created using the online NCEAS Data Repository form. When they need to be updated using Morpho the save to the network process takes longer than five minutes. I've recored some saves (updates) of up to ten minutes.


Actions #1

Updated by Jing Tao about 16 years ago

I timed the update command and found out deleting xml_index table is the bottleneck. I updated a very simple document and it took about 108 seconds. However the deleting xml_index table take 97 seconds. Here is the sql command it executed:
DELETE FROM xml_index WHERE docid='mydocid';

Actions #2

Updated by Jing Tao about 16 years ago

After creating a index in docid in xml_index table, the time for deleting decreased from 97 seconds less than 1 second.

Actions #3

Updated by Jing Tao almost 16 years ago

I searched the internet and found somebody said:

Almost always, if delete is slow when selecting the same rows is fast,
it's because you've got a trigger performance problem --- most commonly,there are foreign keys referencing this table from other tables and
don't have the referencing columns indexed.

I checked our table structures and found xml_documents (rootnodeid) and xml_index (nodeid) tables have foreign keys referenecing xml_nodes
table. And columns rootnodeid and nodeid were not indexed. After I indexed them in my local test machine, delete and update only take couple
seconds (before they should take more than 56,000 seconds - 15 hours!). Oh, index rocks!

Actions #4

Updated by Jing Tao almost 16 years ago

Moved to 1.7.1 release

Actions #5

Updated by Callie Bowdish over 15 years ago

saving or updating data packages takes around 3 minutes.

Actions #6

Updated by Matt Jones over 15 years ago

I'm closing this bug, as it was used to document changes that were released with the 1.7.1 release and so should not have been reopened because we can't retarget it and have it point at both 1.7.1 and a later release. The best way to file a bug for an issue that was closed and was part of a release is to open a new bug report and put a pointer in that bug to the old bug for a history. I will open a new bug for this issue.

Actions #7

Updated by Redmine Admin over 10 years ago

Original Bugzilla ID was 2805


Also available in: Atom PDF