Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-25669

fix_course_sortorder() does way too many UPDATE queries

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0.2
    • Component/s: Performance
    • Labels:
      None
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      The fix_course_sortorder function is called every time create_course() runs + some other cases. It ends up doing an UPDATE query for every course in the mdl_course table. In our case we have 10,000 courses and so it is doing 10,000 updates. Not only does this result in a huge amount of data needing to be written to the DB disk, it also locks the entire table if it is done within a transaction.

      In 2.0, Web Services enable people to do a call to create as many courses as they want, lets say 500. This results in 500 courses * 10,000 update queries, all within a single transaction. This completely locks up Moodle, eg. trying to load course/view.php can block because it wants to write to mdl_course.modinfo. Or trying to update a course settings page will hang there until the lock is released. This results in many requests pooling up and killing the web server.

      I have attached a test PHP script + the list of DB queries performed when I run this script. Something really needs to be done about this menacing function!

      Thanks

        Attachments

        1. course.log
          18 kB
        2. createcourse.out.txt
          1.67 MB
        3. createcourse.php
          0.3 kB
        4. sortorders.txt
          331 kB

          Activity

            People

            Assignee:
            dongsheng Dongsheng Cai
            Reporter:
            ashleyholman Ashley Holman
            Tester:
            David Mudrák (@mudrd8mz)
            Participants:
            Component watchers:
            Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            6 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              21/Feb/11