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
