Moodle
  1. Moodle
  2. MDL-33018

Add context index to substantially improve system performance on large PostgreSQL installations

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2, 2.3
    • Fix Version/s: 2.4
    • Component/s: Performance
    • Labels:
    • Testing Instructions:
      Hide
      1. install moodle on pg before this patch
      2. manually create the index in question:
        CREATE INDEX mdl-context-prepatch ON mdl_context USING btree (path varchar_pattern_ops);
        
      3. upgrade to post patch
      4. verify there are three indexes on mdl_context.path, two with varchar_pattern_ops (one ours and one autogenerated)
      5. Drop your installation and do a new install
      6. verify there are two indexes on mdl_context.path, the second with varchar_pattern_ops
      7. Use the xmldb editor - to add, edit and delete hints on indexes
      8. run phpunit tests
      Show
      install moodle on pg before this patch manually create the index in question: CREATE INDEX mdl-context-prepatch ON mdl_context USING btree (path varchar_pattern_ops); upgrade to post patch verify there are three indexes on mdl_context.path, two with varchar_pattern_ops (one ours and one autogenerated) Drop your installation and do a new install verify there are two indexes on mdl_context.path, the second with varchar_pattern_ops Use the xmldb editor - to add, edit and delete hints on indexes run phpunit tests
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_24_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w28_MDL-33018_m24_pgindex2
    • Rank:
      40194

      Description

      At the OU we upgraded to Moodle 2.2.x (from 2.1.x) in April and saw a substantial reduction in performance. Consequently, since then, our systems team have been examining ways to improve performance to regain some of what we lost.

      So far the most successful of these (which appears to have recovered most of the performance decline) is very simple: an additional index on the context table.

      We thought it would be helpful to report this, in case it can be included in standard Moodle (even though this issue is Postgres-specific). It should be noted that although the OU now runs an almost-standard Moodle 2.2.x core, we do use many custom plugins, so these results might not necessarily be applicable to other locations. However in this case it seems likely they probably will be.

      CREATE INDEX mdl_cont_pat2_ix
         ON mdl_context
         USING btree
         (path varchar_pattern_ops);
      

      varchar_pattern_ops is a Postgres-specific keyword that means the index can be used for certain types of pattern-matching search.

      Apparently this is only necessary if your database is set to a non-C locale, such as the UTF-8 locale (in a C locale, the 'normal' index would work). However, UTF-8 locale is probably a standard setting for Moodle users.

      I believe this index is used in addition to the standard index, rather than as a replacement for it.

      After creating this index using a local plugin, we found the following results:

      • On our database server, CPU load (measured over about a few hours before the change and half an hour afterward) dropped sharply from about 4.75 to about 3.0.
      • On our front-end servers, the time taken to serve a course view page dropped sharply from about 0.45 to about 0.3 seconds.

      By the way, neither database server nor the front-end servers are under heavy load at present. (That is to say, there are currently about 10 mdl_log entries per second, so the system isn't idle, but we currently have much more hardware than actually necessary to handle this load.)

      1. .jpg
        61 kB
      2. load-graph.png
        11 kB

        Issue Links

          Activity

            People

            • Votes:
              10 Vote for this issue
              Watchers:
              15 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: