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

Subscribe/Unsubscribe roles very slow

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.2
    • Fix Version/s: 1.9
    • Labels:
      None
    • Environment:
      Xeon MP 2000 x 4, 8 GB RAM, Linux, MySQL 5.0.44
    • Affected Branches:
      MOODLE_18_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      Perfomance info of subscripe global roles operation :

      494.095403 secs RAM: 23.1????? Included 153 files ticks: 49417 user: 2303 sys: 109 cuser: 0 csys: 2 Load average: 1.52 Record cache hit/miss ratio : 0/0

      Missing Indexes Found: 0

      Mysql "SHOW FULL PROCESSLIST" show such request.
      Status "coping to tmp table" (my.cfg: tmp_table_size =800MB).
      Each request is executed 5-10 seconds (earlier when tmp_table_size was less, they were executed 30-50 seconds).
      --------------
      SELECT rc.capability, c1.id AS id1, c1.id AS id2, (
      c1.contextlevel *100
      ) AS aggrlevel, SUM( rc.permission ) AS sum
      FROM mdl_role_assignments ra, mdl_role_capabilities rc, mdl_context c1
      WHERE ra.contextid = c1.id
      AND ra.roleid = rc.roleid
      AND ra.userid =287
      AND c1.id
      IN ( 197, 10, 5, 29458, 1, 21879 )
      AND rc.contextid =1
      AND (
      rc.capability = 'mod/forum:viewdiscussion'
      OR rc.capability = 'moodle/site:doanything'
      )
      GROUP BY rc.capability, c1.id, c1.contextlevel *100
      HAVING SUM( rc.permission ) !=0
      UNION ALL
      SELECT rc.capability, c1.id AS id1, c2.id AS id2, (
      c1.contextlevel *100 + c2.contextlevel
      ) AS aggrlevel, SUM( rc.permission ) AS sum
      FROM mdl_role_assignments ra
      LEFT JOIN mdl_role_capabilities rc ON ra.roleid = rc.roleid
      LEFT JOIN mdl_context c1 ON ra.contextid = c1.id
      LEFT JOIN mdl_context c2 ON rc.contextid = c2.id
      LEFT JOIN mdl_context_rel cr ON cr.c1 = c2.id
      WHERE ra.userid =287
      AND c1.id
      IN ( 197, 10, 5, 29458, 1, 21879 )
      AND rc.contextid !=1
      AND (
      rc.capability = 'mod/forum:viewdiscussion'
      OR rc.capability = 'moodle/site:doanything'
      )
      AND cr.c2 = c1.id
      GROUP BY rc.capability, c1.id, c2.id, c1.contextlevel *100 + c2.contextlevel
      HAVING SUM( rc.permission ) !=0
      ORDER BY aggrlevel ASC
      --------------

        Attachments

          Activity

            People

            • Assignee:
              lazyfish Yu Zhang
              Reporter:
              mentis Alex Djachenko
              Tester:
              Nobody
              Participants:
              Component watchers:
              Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Sara Arjona (@sarjona), Víctor Déniz Falcón
            • Votes:
              4 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                3/Mar/08