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

Performance with large courses is very poor eg get_fast_modinfo isn't

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.3
    • Fix Version/s: 1.9.4
    • Component/s: Libraries
    • Labels:
      None
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      Performance of get_fast_modinfo is poor when courses have many activities, especially on Postgres.

      This is because the main query get_fast_modinfo does is to retrieve the list of contexts and it does this with an 'instanceid IN (...)' syntax. This syntax is slow when retrieving large number of results. We observed this on a genuine course with 900 activities. (Yes this is special purpose but still.)

      Performance can be improved by using a standard join rather than trying to 'optimise' the database query by passing in the already-known instance IDs. I have done a patch to make this happen.

      Here are some tests on dev system (note, Postgres/MySQL times are different servers so not comparable, but tests on the same db are on the same machine; all queries were run repeatedly 5 times in a row after at least one/two warmups):

      Postgres (before, after)

      2000 activities: ~1500ms, ~140ms
      ~30 activities: ~30ms, ~30ms

      MySQL (before, after)

      2000 activities: ~500ms, ~170ms
      3 activities: ~20ms, ~20ms

      So in other words there is no difference in performance for small courses and for very large courses this is an improvement of roughly 10x on the performance of get_fast_modinfo in Postgres, or roughly 3x in MySQL.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  28/Jan/09