Tim,

we have the multilang filter enabled for content and titles because we have HTML Blocks with multilang information. 

Switching off the multilang filter completely, we have 63 queries on /index.php being not logged in.
Switching on the multilang filter for content only, we still have 63 queries.
Switching on the multilang filter for content and titles, we get 114 queries on the same page! The additional queries are like  

SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,16,21) GROUP BY filter HAVING MAX(f.active * ctx.depth ) > -MIN(f.active * ctx.depth ) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 21 ORDER BY active.sortorder

and are fired by filter_get_active_in_context() in /lib/filterlib.php. I read through http://docs.moodle.org/dev/Filter_enable/disable_by_context and it makes some sense to me, but I still don't understand why this needs to produce so much load on the database.

Sam,

/index.php needs 0.24 secs and 110/0 DB reads/writes when being called not being logged in according to moodle perf info in page footer

/index.php needs 0.56 secs and 293/2 DB reads/writes when being called logged in as admin

/index.php needs 0.16 secs and 38/1 DB reads/writes when being called logged in as student user.

Browsing moodle and courses as student user, pages get build in 0.5 secs or less according to moodle perf info in page footer. Well, looking at all the things moodle does to build a page, that's not bad.

Nevertheless, when building a page moodle does several queries which are always the same or at least the result doesn't change very often. As far as I have understood memcached you could cache the results for such database queries in memory and only talk to the database when there is a Update/Insert query or if the query result isn't in cache. Is that right or am I on the wrong track?

Thanks in advance
Alex 

I thought I'd add my 3 cents into this. Taking the index.php page, logged in as admin, I was getting around 260 queries on each load. Around a couple hundred of those were single select queries that were being generated by get_config in moodlelib.php to get configuration data for various plugins. I knew this kind of thing would get into the database cache pretty quickly so would likely be pretty light in terms of real i/o, but I still wanted to see how removing that round-trip might improve performance.

I decided to do two things to make grabbing configuration data more efficient. The first was to do it in one big query instead of a bunch of small ones and store that result to be accessed in subsequent calls during the request. The second was to use the 'apc' extension, if it is installed, to store the results of that configuration locally on the app server for a small amount of time between requests. Because it's still at a very experimental stage, I simply let the local cache expire after a couple of minutes instead of checking that it was always in sync, but the latter could be done as well with fairly minimal effort and a negligible impact to performance.

Then I did some very simple apache bench runs on my fairly low powered test instances (in this case, I just used one app server and one db server) on the logged in version of index.php. Drastic performance improvements? Unfortunately not. I was getting a maximum 10% boost in the req/second I could get out of the system. Considering index.php is a bit of an outlier in its number of queries, that's probably an even more minimal gain in general.

However, another thing I looked at was CPU usage on the database server with and without the patch. Applying the patch caused the CPU load to drop to (on average) 1/3 of what it was without the patch (running apache bench). So those extra queries, cached or not by the DB server, are not without cost. And while it may not make a dent in req/s by reducing the number of queries, it can give your database server a lot more CPU headroom, which should mean you'd be able to throw more app servers at it before it would fall over (provided you don't run into i/o limitations).

Admins are unlikely to represent the major source of load on a moodle site as someone already pointed out in this thread. Looking at most pages under a user account, it still looked like you could cache about 1/3 of the queries in this way (if you included things like metadata querying, capabilities, etc.). I'd have to do more investigation on that, however. I'm also unsure as to whether or not there might be a PostgreSQL (database I was using) configuration setting that would reduce the CPU load without having to make code changes.

I'd be interested to hear if anyone else out is able to reproduce what I found (almost negligible req/s gain but a significant drop in CPU usage) and/or any counter-arguments to whether this sort of thing is a worthwhile endeavour in the grand scheme of things. I'm attaching my modified version of the latest moodlelib.php (scratch that... the entire file puts me over the 300kb upload limit, so this attachment just has the modified get_config function plus my get_config_from_db wrapper function). To make it easy to switch back and forth, I'm turning the patch on and off based on a CFG variable I added. If you set:

$CFG->performance_patch = 1;

in config.php, it'll turn the patch on. It's important to note that this doesn't cover all of the ways in which you can access configuration data and likely has me blundering my way through several moodle coding conventions I'm unaware of. I apologize in advance and will do my best to read up on those if any of this seems worth submitting to the main moodle repo. The main purpose at this point was just to get a proof of concept that could be measured.

moodlelib-mod.php