[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Status: Fix Committed => Fix Released -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Fix Released Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Status: In Progress => Fix Committed -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Fix Committed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Status: Confirmed => In Progress -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: In Progress Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
Amazing, thank you Robert -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
Hi Issam, I've added a patch with your change https://reviews.mahara.org/#/c/8492/ It has some adjustments to deal with groups/institutions copying things as well -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
Hello Issam, Thank you for the suggestion. That's an incredible improvement! We'll add your suggestion to a patch in our review system to facilitate code review. Cheers Kristina -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
Hi Robert, I would like to share a solution that I applied in our institution. We have a collection (66 pages) that takes 130 second to be copied because of the method "update_hierarchy_path" doing update query on the table "artefact" (in our case 500K records) , with no index in the query it does not help. We came up with the idea of adding the column owner in update query, because we update the hierarchy of the path for the artifacts we just inserted. And since there is an index on the owner column this has lowered the processing from 139 seconds to 19 seconds. Here are the two modified lines of code (line 1429 of artefact/lib.php): $params = array($newparent->path, $length, $this->owner, $this->path, db_like_escape("{$this->path}/") . '%'); $sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE owner = ? AND (path = ? OR path LIKE ? )"; I want to know if there are any side effects for using the owner column in the query. Please advise Thank you. -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Milestone: None => 18.04.0 -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Assignee: Cecilia Vela Gurovic (ceciliavg) => (unassigned) -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Assignee: (unassigned) => Cecilia Vela Gurovic (ceciliavg) -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
** Changed in: mahara Importance: Undecided => High -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1724603] Re: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections
Hi Brian, Good point about the missing index on the path column. The first thing I'd try is adding a unique index to the 'path' column as each path should be unique as they all end with the id of the artefact itself and that is unique. The purpose of the 'path' column (if I remember right) is to handle the hierarchy issue where the child items are older (lower id) than the parent items. Eg if some files are uploaded then some folders are made and the files are moved into the folders we could end up with paths like 2/5/192/16 2/5/192/412 2/5/193/6 2/5/193/77 which were complicated to sort correctly with just the 'id' and 'parent' columns It might make sense to have a 'artefact_path_structure' table to handle things instead of the 'path' column in the future. Cheers Robert ** Changed in: mahara Status: New => Confirmed -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1724603 Title: update_hierarchy_path in artefacts/lib.php hammers sql when copying collections Status in Mahara: Confirmed Bug description: A teacher asked 40 students to copy a 15 page collection with numerous artefacts on each page, which crippled our mysql server. When testing it, even copying the collection once resulted in the web server timing out and raised the sql load incredibly. The code "update_hierarchy_path" in the artefact/lib.php on line 1423 runs an sql query as below: `$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";` The artefact table in Mahara does not index the `path` column, so whilst updating one artefact is not a major issue, but updating the path column for many artefacts hits the database massively. Indexing the path column (which is 1024 bytes) may not be a good solution long term, but either the query needs to be made more efficient or the column indexed. Mahara version 17.04_STABLE (updated about a month ago) Linux RHEL7 MYSQL 5.6 Browser is current chromium Version 61.0.3163.100 (but that is not relevant) To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1724603/+subscriptions ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp