Bug #2805
closedMetacat Performance: updates from Morpho of data packages are taking longer than five minutes
0%
Description
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.
Callie
Updated by Jing Tao over 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';
Updated by Jing Tao over 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.
Updated by Jing Tao over 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
you
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!
Updated by Callie Bowdish over 16 years ago
saving or updating data packages takes around 3 minutes.
Updated by Matt Jones about 16 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.