General developer forum

I have just pushed to git.catalyst.net.nz/git/moodle-r2.git a new branch called mdl19-perf . It has a large patchseries (some 130 commits) that I have been working on over the last month to address the performance problems in 1.8/1.9. The work is based on different things we have been trying over the last 4 months. It hasn't been fun or easy, but here it is at last.

You can get it with a recent git (1.5.2 or 1.5.3) doing

git clone http://git.catalyst.net.nz/git/moodle-r2.git
cd moodle-r2.git
git branch --track mdl19-perf origin/mdl19-perf
git checkout mdl19-perf

To review the code easily you can do

gitk b6c2b618ebb76717b43b94e9f27d94812deeb322..

Status

The code as it stands does not install or upgrade cleanly. To try it, you have to

• Run the Moodle "install" process from CVSHEAD (hint: do git checkout origin/cvshead)
• Switch to the mdl19-perf code (hint: do git checkout mdl19-perf)
• build the context paths with php -r 'include("./config.php");build_context_path();'

Outside of the install/upgrade process, the 1.8 version of the code has been tested quite a bit @ Catalyst, but this is a largely untested 1.9 port of it -- I have had to change a few things. So it's probably rough here and there.

MySQL support hasn't been tested yet either - but I have removed all the Postgresisms from the code. So it should work

Testing

You will want to have a moodle install with a few thousand user accts, and few thousand courses with content and lots of enrolments. Tons of them

To evaluate the changes, testers should at least

• Enable perf logging, make sure you get DB queries entries and that you read it in apache's errorlog (not on the page footer). The page footer does not appear in the heaviest page of moodle today - login/index.php .
• Disable rcache

For developers it is very interesting to additionally

• Enable full query logging at the DB server (and tail those logs)
• If possible have the DB server on a separate machine

(It is really important to disable rcache for developers -- we have been relying too much on it, and that has actually made things worse.)

Interesting things to test:

• Login as
• Role switching
• Participants list
• My courses blocks
• Overrides
• Privilege removals while you are logged in (This actually is quite nice - login with 2 webbrowsers, one as admin, the otherone as a lowly user, and add/remove privileges from the lowly user, who should see them come into effect immediately. New enrolments don't always appear immediately, but enrolment removal, and capabilities changes are always instant.)

And of course you'll want to test performance of key operations An easy way to do it if you have the git checkout is to switch branches.

• git checkout origin/cvshead (to get CVS HEAD)
• git checkout mdl19-perf (to go back to the perf branch)

Note that on every "switch" you have to delete your cookie and re-login everytime (if you don't delete your cookie it will complain quite a bit.)

Before it gets into HEAD

There are a couple of things I need to sort out before this gets into HEAD proper.

• The most important one is that it breaks installation and upgrades. The easy fix is to throw whatever's needed into has_capability() but that will probably have a negative impact on runtime -- and we cannot afford that. So I will be working on a fix that doesn't trigger additional DB queries or anything expensive in runtime.
• I hope works on MySQL If anyone can confirm -- great. Otherwise I'll test it when I get there.

Once those key things are done, it'll probably be all clear and I'll land it into HEAD. There are also a couple of things that appeared in the porting from 18 to 19/head need to be sorted -- from my notes:

• update_course_icon() we need to walk the callers to ensure the check for capabilities properly
• Also - audit $USER->editcourseallowed -- unsure of which approach is the correct one
• kill sql_intarray_to_in() -- implode() will do
• get_role_users() needs significant rework -- my merge drops some functionality
• course/lib: review capchecks for viewfullnames
• get_my_courses() and callers need a review around capchecks

Hacking on this code

If you want to work on the code before it lands in HEAD (which will take a couple of days), just work in git and then use git-am to export the patches and post them here in GDF for discussion. Or push them to repo.or.cz if that's easier.

The new code is a bit of a balancing act, so we need to be careful around it to make sure changes improve it rather than regress it. I have made a practice of measuring the DB queries, overall time and memory (if applicable) of the pages that each code change affects. This made me realise that some changes that I thought "of course" would make things better... didn't. Or made things 10% better in one place, and 300% worse in other places.

Measuring is the only way we can evolve core Moodle code to be scalable. So if you are proposing a patch, it'll be great to see it come with some numbers. A bit for boasting if you want, but mainly to show what cases you have tried and how it fares.

Once the code is in CVS I'd like to keep hacking on it (mainly accesslib and other performance-critical parts of core) with a "propose patch, get peer review in GDF, commit to CVS" approach. At least for a while.

Constant number of DB queries

For me, the most important thing to learn from this patchseries is that the core of Moodle must get its job done in a constant number of database queries.

What does this mean? The most expensive operation we have is a database query. Every time PHP "talks" with the DB, it could do thousands of function calls and internal operations. Thousands. So every time we talk with the DB, it has to be meaningful... which may mean the PHP side does a bit more preparation before the query, and post-processing of the data returned.

All the DBs supported now by Moodle can do complex JOINs and subselects -- so we can make those opportunities meaningful. The main issue we have today in moodle is that we often code like

 $course = get_records('courses');
 foreach ($courses as $course) {
      $forums = get_records('forum', 'courseid', $course->id);
      foreach ($forums as $forum) {
          $discussions = get_records('discussions', 'forum', $forum->id);
          foreach ($discussions as $discussion) {
              // here we call a function
              // that does some DB queries for _each_
              // discussion
              forum_do_somthing($discussion);
          }
      }
}

The code above will cause a huge storm of DB queries that grows exponentialy with the number of courses/forums/discussions/posts. Most of the time we don't even realise as we are coding it!

(If you have only one test course, with one forum with 3 dscussions on it, and the DB is on the same machine as apache, you don't see a thing. That is why it is important to have those query logs enabled on full, and some real data in your DB.)

And in 99% of the cases (at least for moodle) we can do it in a fixed number of DB queries. Yes, the query will be a bit slower than the little individual queries. But it will be one instead of thousands -- which scales a lot better.

It takes a bit more thinking and work, but code that looks like

 $sql = "SELECT c.coursefields,...
                f.forumfields...
                fd. forum discussion fields
                fdp. forum post fields
         FROM ... 
         JOIN ... ON ()
         ...
         WHERE ..."
$rs = get_recordset_sql($sql);
if ($rs->RecordCount()) {
     while ($rec = fetch_next_record($rs)) {
          // forum_do_something() will never do any DB work
          // because the controlling look as grabbed all the 
          // required data into a single record
          forum_do_something($rec);
     }
}

means we can scale pretty much linearly -- rather than the log(log(log(O^n))) in the previous example. The key strategy above is to move the SQL work out of the inner functions and to the controlling loop. It does mean rewriting some of the inner functions, and in some cases they'll have to be specialised to the specific work required in that loop.

Of course, there are always exceptions where you will have to do some DB work deep inside the loop. But hey, they will be just that: exceptions

There are some very core areas of moodle that I haven't gotten to yet and show the problem. With a bit of effort and testing, we can optimise them to work in a constant number of DB queries...

• The course "main body" display loop in course/view.php and the course formats. It's doing a ton of unneeded DB work that could be done in 1 DB query. Just restore the "test" course and look at the DB traffic for each coursepage view.
• mod/forum mailout code
• My Moodle

I'll be very happy if anyone wants to tackle any of those 3 above. (I'll probably have a go at My Moodle at some point).

If we start hacking with this concept in mind -- get the job done with a constant number of queries regardless of the number of courses/users/enrolment/stars-in-the-sky -- we can get moodle to deliver all the important pages with perhaps < 30 DB queries.

And if we can get there -- or close to that -- Moodle will be the fastest most scalable LMS around. The core API -- specially modules API -- is very well suited for this coding approach. With a few tweaks here and there...

Finding your way around the new code

Make sure you read the commit messages, and the top of accesslib. Should answer 99% of your questions if it doesn't or is unclear, ask away.

While I work on finishing this for the merge, I'll monitor GDF to answer questions and coordinate.