Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-35685

Performance issue on a SQL request (related to capability/permission/roleid=6)

    XMLWordPrintable

    Details

      Description

      Reporting issue from comments of the closed issue: MDL-25288
      Feel free to edit the issue title.

      The comments started there: http://tracker.moodle.org/browse/MDL-25288?focusedCommentId=177841&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-177841

      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;
      -----------

      count(id)

      -----------

      110166

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

      ----------

      count

      ----------

      3054

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

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

      Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

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

      mdl_context 0 PRIMARY 1 id A 113747 NULL NULL   BTREE  
      mdl_context 0 mdl_cont_conins_uix 1 contextlevel A 6 NULL NULL   BTREE  
      mdl_context 0 mdl_cont_conins_uix 2 instanceid A 113747 NULL NULL   BTREE  
      mdl_context 1 mdl_cont_ins_ix 1 instanceid A 113747 NULL NULL   BTREE  
      mdl_context 1 mdl_cont_pat_ix 1 path A 113747 NULL NULL YES BTREE  

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

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

      Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

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

      mdl_role_capabilities 0 PRIMARY 1 id A 3107 NULL NULL   BTREE  
      mdl_role_capabilities 0 mdl_rolecapa_rolconcap_uix 1 roleid A 54 NULL NULL   BTREE  
      mdl_role_capabilities 0 mdl_rolecapa_rolconcap_uix 2 contextid A 155 NULL NULL   BTREE  
      mdl_role_capabilities 0 mdl_rolecapa_rolconcap_uix 3 capability A 3107 NULL NULL   BTREE  
      mdl_role_capabilities 1 mdl_rolecapa_rol_ix 1 roleid A 31 NULL NULL   BTREE  
      mdl_role_capabilities 1 mdl_rolecapa_con_ix 1 contextid A 103 NULL NULL   BTREE  
      mdl_role_capabilities 1 mdl_rolecapa_mod_ix 1 modifierid A 57 NULL NULL   BTREE  
      mdl_role_capabilities 1 mdl_rolecapa_cap_ix 1 capability A 1035 NULL NULL   BTREE  

      -----------------------------------------------------------------------------------------------------------------------------------------+
      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");

        Attachments

          Activity

            People

            • Votes:
              5 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                14/May/13