Slow upgrades for profile icons & group quotas

Bug #817796 reported by Richard Mansfield on 2011-07-28
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
High
Hugh Davenport

Bug Description

Three recent upgrades are very slow on large databases and are in need of a bit of a rewrite:

- The upgrade to remove bad profileicon references from the user table
- The upgrade to initialise group file quotas
- The upgrade which moves existing profileicons into an 'images' folder.

https://reviews.mahara.org/#change,528

Changed in mahara:
status: New → Confirmed
status: Confirmed → In Progress
assignee: nobody → Richard Mansfield (richard-mansfield)
milestone: none → 1.5.0
importance: Undecided → Low

Reviewed: https://reviews.mahara.org/528
Committed: http://gitorious.org/mahara/mahara/commit/1beac3ad5123cf1e552dba740496244f1ed83b56
Submitter: Hugh Davenport (<email address hidden>)
Branch: master

commit 1beac3ad5123cf1e552dba740496244f1ed83b56
Author: Richard Mansfield <email address hidden>
Date: Fri Jul 29 11:46:29 2011 +1200

    Rewrite some slow upgrades (bug #817796)

    This is a rewrite of three recent upgrades that are very slow on
    large databases.

    Change-Id: Ic22b344ff499d317b19d6512004286321e492e6e
    Signed-off-by: Richard Mansfield <email address hidden>

Changed in mahara:
status: In Progress → Fix Committed
Changed in mahara:
status: Fix Committed → Fix Released
milestone: 1.5.0 → none
François Marier (fmarier) wrote :

Reopening this one because it was only fixed and tested on Postgres.

Someone needs to test this using a large MySQL DB and write the equivalent optimized SQL.

Changed in mahara:
importance: Low → High
milestone: none → 1.5.0
status: Fix Released → In Progress
assignee: Richard Mansfield (richard-mansfield) → nobody
tags: added: sql upgrade
Changed in mahara:
status: In Progress → Confirmed
Hugh Davenport (hugh-davenport) wrote :

I don't think we can get any more optimized for MySQL.

MySQL doesn't have UPDATE FROM sql syntax (like what was done with the postgres optimization).

Another option would be to do an UPDATE and a SELECT query inside the SET clause, but from the page http://dev.mysql.com/doc/refman/5.6/en/update.html down the bottom we have the following:
"Currently, you cannot update a table and select from the same table in a subquery."

This basically means we can't update the group table without SELECTing the needed information before hand. Doing it this way makes a bulk UPDATE statement difficult. Because of this, I think we should just keep as is for mysql upgrades.

Thoughts?

Cheers,

Hugh

Changed in mahara:
status: Confirmed → Incomplete
Hugh Davenport (hugh-davenport) wrote :

Turns out you might be able to do a nested SELECT to get around that second issue, see http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

Hugh Davenport (hugh-davenport) wrote :

https://reviews.mahara.org/908
for mysql optimized one

Changed in mahara:
status: Incomplete → In Progress
assignee: nobody → Hugh Davenport (hugh-catalyst)
Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/908
Committed: http://gitorious.org/mahara/mahara/commit/d41717dd4c40a9de84f19b26c2d2f3dd03cc8a8b
Submitter: Richard Mansfield (<email address hidden>)
Branch: master

commit d41717dd4c40a9de84f19b26c2d2f3dd03cc8a8b
Author: Hugh Davenport <email address hidden>
Date: Fri Dec 2 11:44:23 2011 +1300

    Rewrite slow upgrades (mysql) (bug #817796)

    Change-Id: I0fd7dfdc9540abb1359a78e2c4ac9cc099cc9576
    Signed-off-by: Hugh Davenport <email address hidden>

Changed in mahara:
milestone: 1.5.0 → none
status: In Progress → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers