Hi everybody,
we are running Moodle 2.1.x on Apache with a PostgreSQL 9.1 database server on a separate machine. Compared to a database server running locally, there is some lag in response time.
Therefore, I just had a look at the database queries which moodle does on every page call. For example, moodle does (in our setup) perform approx. 100 database calls when requesting /index.php - without being logged in!
I was just surprised that the number of database calls is that high because the majority of the database calls could be - theoretically - cached in memory because the affected rows don't change very often.
Now I read about memcached and that it could be used to solve this performance killer. Unfortunately, according to some threads in this forum, support for memcached seems to have been dropped in Moodle 2.x. Is there any possibility to use moodle with memcached? Or am I on the wrong track and memcached doesn't solve my problem at all?
Any help or hint for decreasing our response time is welcome!
Thanks in advance
Alex
100 on the front page is definitely wrong. I get 61 when I try - which is still more than it should be, but quite a lot of those are just getting metadata, and we really ought to deal with those in a different way.
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.
Ah! That is a well known thing.
Go to Site administration ► Plugins ► Filters ► Common filter settings and Text cache lifetime to No.
With the DB on a separate server, it is really not clear that text Caching helps.
Tim,
I understand that text caching to the DB doesn't ma
I'm sorry, but even if I set $CFG->cachetext to "no" as you described, the number of DB queries doesn't change and the
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
queries like described in my previous post are still there.
Sorry, I was not paying attention. I just saw the word filter and made assumptions.
Yes, I would expect several instances of that query. Presumably the index page is displaying a long list of courses. What that query is doing is loading the filter settings for each couse, presumably so that the course summary can be filtered property. That is probably excessive. It might be better to filter all the descriptions using the system-context sttings.
Except ... what if the TeX filter is only turned on for maths courses, and the course discriptions contain equations?
How slow is it? I checked and our test system, on index.php which we do not actually use here, takes about 1.3 seconds to do 527 queries (wtf) according to moodle perf info in page footer. This is with Postgres 9 on a different server.
Well, for the database's sake, I'm really glad we don't use index.php...
That said I am logged in as admin - try logging in as student, you might see different numbers. Also this is 2.1, maybe it's been improved.
Cacheing is always hard, it would be better for developers to look at underlying performance problems - for example why is index.php making 527 queries in the first place.
--sam
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
You are right that it is possible to cache query results, but I'm not clear whether, when using memcached, it is possible to do this intelligently. I.e. how easy is it to automatically clear the cache for all queries referring to a table when there is an insert/update on that table?
Most likely this can be made to work well on a single-server system but if you have more than one front-end web server you might have a problem (if you were going to run memcached on the same server as web server) as inserts on one system wouldn't invalidate cache on the other.
(Note: A common pattern is 'insert data, then redirect to page that depends on data' - so time-based caching approaches definitely don't work. It needs to be manually invalidated.)
I think it might work okay if you used sticky sessions to ensure that a user stays on the same load-balanced server, then invalidate cache on the same server and use a reasonably short (say <1 minute) timeout on other servers... Not certain though.
BTW, I wonder if there is a Postgres cacheing proxy that would do this for you instead of needing to change Moodle code...? Haven't looked.
--sam
Sam,
I just search for some kind of Postgres cacheing proxy and stumbled upon a discussion on the pgsql-hackers mailinglist (http://archives.postgresql.org/pgsql-hackers/2006-10/msg00810.php). Reading through this discussion, it became clear to me that cacheing database results isn't easy at all and that the cache should be controlled by the application, not the database system, to ensure consistency.
Assumed that moodle knows which table contents don't change at all or don't change very often (for example mdl_config, mdl_context or mdl_course_categories), cacheing these results should be accomplishable.
But as moodle doesn't support memcached anymore (or never did support it at all - don't know because documentation is scarce) and I don't have the time to dig into details to code the memcached support myself, memcached is no option for our moodle now - unfortunately...
Problem is, even with ones that don't change often, when they do change people tend to complain if there is a 10 minute delay between you changing it and something happening.
If cache times are very short, say 15 seconds, it might be possible to do it transparently without anything cleverer than the time delay; also force a 15 second delay after people edit the affected tables (before you get to the 'changes saved' after mdl_config update, for instance), and then nobody will ever notice.
--sam
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.
David,
thanks for your thoughts on this topic.
I will have a look at your patch within the next days to see if I can reproduce what you have found and to check if your idea will fit for our requirements.
I haven't thought of using PHP as a database query cache, but that seems to be more obvious than setting up memcached
Unfortunately, even with your patch, the improvement is limited only to a fraction of all queries and you have to dig into moodle core to acchieve a performance gain...
Great! Looking forward to finding out about your results. It is unfortunate that the biggest impact it would have would be on the admin account (if indeed my findings are repeatable) and that I had to dig into moodle core to do it. As well, eventually you get to the age old question of dev efficiency vs. code efficiency, and use of a cache definitely adds complexity to the dev side. Not sure where this would fall on the spectrum as I'm fairly new to the moodle world... but I can definitely understand the arguments on both sides.
I'm attaching an update to the solution I originally posted, without the first step (trying to get everything in one query and then hash it) which really didn't make an impact in the end, and a more general "cached_value" function that takes a cache key and a function closure. The syntax is a little weird, but it would mean that only a couple of lines would need to be added to make something use the cache, and if apc isn't installed, it would just default to the usual. Not sure if that would make it easier to integrate or not.
I think memcache (centralized) would still likely be a better choice to cache anything that's not configuration data, but then you're definitely in the game of having to manage cache invalidation, etc. carefully. That said, it's a nice thing to have easy local caching in the PHP toolset... it was dumb luck that I stumbled on an article talking about using APC's cache api. I'd always just thought of it as an accelerator that you set and forget.