Mart Mangus added a comment - 10/Sep/12 11:53 PM
Hello!

We have real performance problems with our Moodle (2.2.4) site that are connected to this issue.

The main cause of the problem is (according to MySQL slow queries report) this query:

SELECT ctx.path,

                   rc.capability, rc.permission

              FROM context ctx

              JOIN role_capabilities rc ON rc.contextid = ctx.id

         LEFT JOIN context cctx

                   ON (cctx.contextlevel = 50 AND ctx.path LIKE CONCAT(cctx.path, '/%'))

             WHERE rc.roleid = '6' AND cctx.id IS NULL;

(called from lib/accesslib.php from function get_role_access every time user enters the site and quest role access data needs to be loaded)

When the result of this query comes from MySQL cache, then it takes less then 0.01 seconds.
But without MySQL cache or when the query is not in cache, it takes about 4 seconds to run (with no database load).

The problem occurs, when this query falls out of MySQL cache and at the same time a lot of users come to the site - they all start to make that query that takes about 4 seconds. This causes big database load and this 4 seconds query becomes 2 minutes query and the whole site is unavailable (very slow).

In slow query report there are thousands of this query and all in same time-frame (not a single one alone).

I see caching quest role access information in Moodle as the logical solution to this problem.
Is it possible to reopen this issue?

Matthew Slowe added a comment - 27/Sep/12 11:02 PM
We too are seeing massive load on this query. The query was typically taking 10-20 seconds when under moderate load but when MySQL hit the thread_concurrancy limit (we have ours set to 32 and have been hitting it during "peak" load) the whole thing grinds to a halt as queries come in faster than MySQL can service them.

See the image below for an idea of the immediacy of this problem. The part labelled "bodged" was where we abstracted that dataset into memcached for an hour to make the problem "go away".

Please could this be looked at ASAP.

mysql> select count(id) from mdl_context; select count from mdl_role_capabilities;
-----------

-----------

-----------
1 row in set (0.83 sec)

----------

count

----------

----------
1 row in set (0.01 sec)

mysql> show index from mdl_context; show index from mdl_role_capabilities;
-------------------------------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.19 sec)

-----------------------------------------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.05 sec)

Mart Mangus added a comment - 28/Sep/12 2:46 AM
We have made nasty temporary solution for keeping our Moodle running by caching this information in simple serialized file:

function get_role_access($roleid) {

-    global $DB, $ACCESSLIB_PRIVATE;

+    global $DB, $ACCESSLIB_PRIVATE, $CFG;

     /* Get it in 1 DB query...

      * - relevant role caps at the root and down

@@ -260,6 +260,13 @@ function get_role_access($roleid) {

     //TODO: MUC - this could be cached in shared memory to speed up first page loading, web crawlers, etc.

+    // Temporary hack for better performance http://tracker.moodle.org/browse/MDL-25288

+    if (6 == $roleid) { // Guest role

+        return unserialize(file_get_contents($CFG->dataroot . '/cache/guest_role_access_cache.serialized'));

+    } else if ('update_guest_role_access' == $roleid) { // For update script

+        $roleid = 6;

+    }

+

     $accessdata = get_empty_accessdata();

Plus update file, that we run from command line with cron and also manually after plugin installs:

guest_role_access_cache_update.php

<?php

define('CLI_SCRIPT', true);

require_once('public_html/config.php');

require_once('public_html/lib/accesslib.php');

$accessInfo = serialize(get_role_access('update_guest_role_access'));

$success = file_put_contents($CFG->dataroot . '/cache/guest_role_access_cache.serialized', $accessInfo);

echo ($success ? "Guest role access file updated! $success bites written.\n" : "ERROR during guest access file update!\n");