Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.3, 1.9.4, 1.9.5
    • Fix Version/s: None
    • Component/s: Administration
    • Labels:
    • Environment:
      LAMP
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Rank:
      2335

      Description

      On 1.9.3+ (Build: 20090109) stats are completely unusable for us.

      My server tried to runs stats for ONE SINGLE DAY of logs on our site (a pilot of only 30-odd courses but with 30000 mostly-unused rows in the user table). After 8 HOURS, I had to restart MySQL in the morning as it was still locked up at 100% and moodle could not connect to the DB.

      MySQL struggled for 6 HOURS on a single query!!

      Here is the monstrous query. We showed it to our Computer Science SQL lecturer for a laugh who said 'it is definitely a very bad query. Using cross join on large tables is a definite NO'. Creating 1.2 billion rows from our little DB is insane.

      1. Time: 090306 6:30:42
      2. User@Host: moodle[moodle] @ localhost []
      3. Query_time: 21631 Lock_time: 0 Rows_sent: 0 Rows_examined: 1199857693
        UPDATE mdl_stats_daily
        SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
        FROM mdl_role_assignments ra JOIN mdl_context ctx
        ON ctx.id = ra.contextid
        CROSS JOIN mdl_course c
        JOIN mdl_role_capabilities rc
        ON rc.roleid = ra.roleid LEFT OUTER JOIN mdl_course_categories cc1
        ON cc1.id = c.category
        WHERE ra.roleid = mdl_stats_daily.roleid AND
        c.id = mdl_stats_daily.courseid AND
        ((rc.capability = 'moodle/course:view' )
        AND rc.permission = 1 AND rc.contextid = 1
        AND (ctx.contextlevel = 10
        OR (c.id = ctx.instanceid AND ctx.contextlevel = 50) OR (cc1.id = ctx.instanceid AND ctx.contextlevel = 40))) AND
        EXISTS (SELECT 'x'
        FROM mdl_log l
        WHERE l.course = mdl_stats_daily.courseid AND
        l.userid = ra.userid AND l.time >= 1236164400 AND l.time < 1236250800))
        WHERE mdl_stats_daily.stattype = 'enrolments' AND
        mdl_stats_daily.timeend = 1236250800 AND
        mdl_stats_daily.courseid IN
        (SELECT DISTINCT l.course
        FROM mdl_log l
        WHERE l.time >= 1236164400 AND l.time < 1236250800);
      1. commit-5b5a23f
        14 kB
        Matt Clarkson
      2. statslib.php-rev01.diff
        17 kB
        Matteo Scaramuccia
      3. statslib.php-rev02.diff
        18 kB
        Matteo Scaramuccia
      4. statslib.php-rev03.diff
        28 kB
        Juan Segarra Montesinos
      5. statslib.php-rev04.diff
        31 kB
        Juan Segarra Montesinos
      6. statslib.php-rev05.diff
        34 kB
        Juan Segarra Montesinos
      7. statslib.php-rev06.diff
        36 kB
        Juan Segarra Montesinos
      8. statslib.php-rev07.diff
        36 kB
        Juan Segarra Montesinos
      9. statslib.php-rev08.diff
        38 kB
        Juan Segarra Montesinos
      10. statslib.php-rev09.diff
        38 kB
        Tyler Bannister

        Issue Links

          Activity

          Bruce Webster created issue -
          Wen Hao Chuang made changes -
          Field Original Value New Value
          Affects Version/s 1.9.5 [ 10320 ]
          Affects Version/s 1.9.4 [ 10300 ]
          Matt Clarkson made changes -
          Attachment commit-5b5a23f [ 18644 ]
          Elan Hasson made changes -
          Link This issue has a clone MDL-18359 [ MDL-18359 ]
          Jordan Tomkinson made changes -
          Comment [ just a quick note to say im looking into this and testing some patches, no promises though =) ]
          Matteo Scaramuccia made changes -
          Attachment statslib.php-rev01.diff [ 22164 ]
          Matteo Scaramuccia made changes -
          Attachment statslib.php-rev02.diff [ 22170 ]
          Martin Dougiamas made changes -
          Workflow jira [ 30995 ] MDL Workflow [ 44603 ]
          Juan Segarra Montesinos made changes -
          Attachment statslib.php-rev03.diff [ 23468 ]
          Juan Segarra Montesinos made changes -
          Attachment statslib.php-rev04.diff [ 23485 ]
          Juan Segarra Montesinos made changes -
          Attachment statslib.php-rev05.diff [ 23507 ]
          Juan Segarra Montesinos made changes -
          Attachment statslib.php-rev06.diff [ 23556 ]
          Juan Segarra Montesinos made changes -
          Attachment statslib.php-rev07.diff [ 23557 ]
          Juan Segarra Montesinos made changes -
          Attachment statslib.php-rev08.diff [ 23559 ]
          Martin Dougiamas made changes -
          Workflow MDL Workflow [ 44603 ] MDL Full Workflow [ 72969 ]
          Jean-Philippe Gaudreau made changes -
          Link This issue has a non-specific relationship to MDL-30643 [ MDL-30643 ]
          Michael Blake made changes -
          Labels partner
          Tyler Bannister made changes -
          Attachment statslib.php-rev09.diff [ 26711 ]
          Eloy Lafuente (stronk7) made changes -
          Status Open [ 1 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Eloy Lafuente (stronk7) made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          Michael de Raadt made changes -
          Status Reopened [ 4 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]

            Dates

            • Created:
              Updated:
              Resolved: