Affects Version/s: 2.2.2, 2.3
Fix Version/s: 2.4
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
- install moodle on pg before this patch
- manually create the index in question:
- 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
Pull from Repository:
Pull Master Branch:w28_
Pull Master Diff URL:
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.
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.)