Moodle
  1. Moodle
  2. MDL-33617

New index for role_assignments table to improve query performance

    Details

    • Testing Instructions:
      Hide

      1/ upgrade site
      2/ no other testing can be probably done on test sites - we need a huge install to verify it helps with perf, preferably both on mysql and postgresql

      Show
      1/ upgrade site 2/ no other testing can be probably done on test sites - we need a huge install to verify it helps with perf, preferably both on mysql and postgresql
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w28_MDL-33617_m24_roleenrolindex
    • Rank:
      41561

      Description

      We have found that for enrollment plugins that are also managing role assignments that adding a combined index on the fields component, itemid and userid on the role_assignments table can improve query performance. We saw performance degradation when the role_assignments table had 1 million plus records.

      Sorry, but I'm not sure if I have a core related query as an example for improvement, but we are querying against this table for an enrollment plugin to determine role assignment updates.

      Just to avoid any confusion of the type of index, this is what we are suggesting:

      ALTER TABLE `mdl_role_assignments` ADD KEY `component_itemid_userid` (`component`,`itemid`,`userid`);
      

        Activity

        Hide
        Petr Škoda added a comment -

        to integrators: the change is relatively simple, we are not adding records to role_assignments table often, so the perf should only improve

        Show
        Petr Škoda added a comment - to integrators: the change is relatively simple, we are not adding records to role_assignments table often, so the perf should only improve
        Hide
        Petr Škoda added a comment -

        thanks for the report

        Show
        Petr Škoda added a comment - thanks for the report
        Hide
        Michael Woods added a comment -

        Is this change likely to improve the performance of the ldap course sync (enrol/ldap/cli/sync.php)? We have an install of about 120,000 mdl_role_assignment records, and the time it took to run a full sync increased from 1 minute in 1.9.x to 1 hour in 2.2/2.3.

        Show
        Michael Woods added a comment - Is this change likely to improve the performance of the ldap course sync (enrol/ldap/cli/sync.php)? We have an install of about 120,000 mdl_role_assignment records, and the time it took to run a full sync increased from 1 minute in 1.9.x to 1 hour in 2.2/2.3.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Two things:

        1) LOL, WTF are you adding in the upgrade script?
        2) Shouldn't we consider add this to 2.3.1 too?

        so reopening....

        Michael... I'm not sure if it's related or no, but surely it's a good idea to create a new issue explaining the problem with as many details as possible (and linking to this - "sort of relation").

        That would help developer to have the whole picture in order to reproduce the problem and later, fix it.

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Two things: 1) LOL, WTF are you adding in the upgrade script? 2) Shouldn't we consider add this to 2.3.1 too? so reopening.... Michael... I'm not sure if it's related or no, but surely it's a good idea to create a new issue explaining the problem with as many details as possible (and linking to this - "sort of relation"). That would help developer to have the whole picture in order to reproduce the problem and later, fix it. Ciao
        Hide
        CiBoT added a comment -

        Moving this reopened issue out from current integration. Please, re-submit it for integration once ready.

        Show
        CiBoT added a comment - Moving this reopened issue out from current integration. Please, re-submit it for integration once ready.
        Hide
        Petr Škoda added a comment -

        ooops, fixing the upgrade mess

        Show
        Petr Škoda added a comment - ooops, fixing the upgrade mess
        Hide
        Petr Škoda added a comment -

        done, feel free to cherry pick to 2.3 if you want, sorry!

        Show
        Petr Škoda added a comment - done, feel free to cherry pick to 2.3 if you want, sorry!
        Hide
        Michael Woods added a comment -

        Hi,

        I wanted to let you know that this drastically improved the performance of the moodle/enrol/ldap/cli/sync.php on the latest build of Moodle 2.3.1 (20120706). Our mdl_role_assignments table has about 120,000 records, and the time to run the script dropped from 1 hour to less than 2 minutes (similar to Moodle version 1.9.x).

        I'd recommend back-porting it to earlier 2.x builds if possible.

        Thanks,
        Michael

        Show
        Michael Woods added a comment - Hi, I wanted to let you know that this drastically improved the performance of the moodle/enrol/ldap/cli/sync.php on the latest build of Moodle 2.3.1 (20120706). Our mdl_role_assignments table has about 120,000 records, and the time to run the script dropped from 1 hour to less than 2 minutes (similar to Moodle version 1.9.x). I'd recommend back-porting it to earlier 2.x builds if possible. Thanks, Michael
        Hide
        Michael Woods added a comment -

        Sorry, meant to say that we are a MySQL site. Can't vouch for postgresql.

        Show
        Michael Woods added a comment - Sorry, meant to say that we are a MySQL site. Can't vouch for postgresql.
        Hide
        Sam Hemelryk added a comment -

        Thanks guys, this has been integrated now.
        It appears that backporting to 2.3.x is a popular idea and as such I've backported as well.

        Cheers
        Sam

        Show
        Sam Hemelryk added a comment - Thanks guys, this has been integrated now. It appears that backporting to 2.3.x is a popular idea and as such I've backported as well. Cheers Sam
        Hide
        Michael de Raadt added a comment -

        Test result: OK (I think)

        I was able to upgrade successfully on all DBs.

        Thanks for sharing your performance results, Michael. It would be good to hear if others are able to see improvements also.

        Show
        Michael de Raadt added a comment - Test result: OK (I think) I was able to upgrade successfully on all DBs. Thanks for sharing your performance results, Michael. It would be good to hear if others are able to see improvements also.
        Hide
        Dan Poltawski added a comment -

        Congratulations!

        You've made it into the weekly release!

        Thanks for your contribution - here are some random drummers to keep you inspired for the next week!
        http://www.youtube.com/watch?v=_QhpHUmVCmY

        Show
        Dan Poltawski added a comment - Congratulations! You've made it into the weekly release! Thanks for your contribution - here are some random drummers to keep you inspired for the next week! http://www.youtube.com/watch?v=_QhpHUmVCmY

          People

          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: