Bug #2805
closed
Metacat Performance: updates from Morpho of data packages are taking longer than five minutes
Added by Callie Bowdish over 17 years ago.
Updated almost 17 years ago.
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
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';
After creating a index in docid in xml_index table, the time for deleting decreased from 97 seconds less than 1 second.
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!
saving or updating data packages takes around 3 minutes.
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.
Original Bugzilla ID was 2805
Also available in: Atom
PDF