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

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.)