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

SELECT on analytics task hangs when analytics_used_analysables table has enough rows

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      1. Create a new site on mysql
      2. Create a new course in the site and check the id is 2. This is needed for the enrol-users.php script to function properly.
      3. Upload the 70000 users in the attached file
      4. Download enrol-users.php and run it from CLI

        php enrol-users.php

      5. Execute the following command in CLI:

        php admin/tool/task/cli/schedule_task.php --execute="\tool_analytics\task\predict_models"

      6. Execute the following command in CLI and note down the id for the model which name is Upcoming activities due:

        php admin/tool/analytics/cli/evaluate_model.php --list

      7. Go to your mysql console and execute the following query replacing the uppercase placeholders:

        SELECT u.*, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked 
        FROM mdl_user u 
        LEFT JOIN mdl_analytics_used_analysables aua ON u.id = aua.analysableid AND (aua.modelid = 'THE_MODEL_ID_ABOVE'' OR aua.modelid IS NULL) 
        JOIN mdl_context ctx ON (ctx.contextlevel = '30' AND ctx.instanceid = u.id) 
        WHERE 1=1 AND u.deleted = '0' AND u.confirmed = '1' AND u.suspended = '0';
        

      8. The db query SHOULD run blazing fast (less than a second or a few seconds)
      Show
      Create a new site on mysql Create a new course in the site and check the id is 2. This is needed for the enrol-users.php script to function properly. Upload the 70000 users in the attached file Download enrol-users.php and run it from CLI php enrol-users.php Execute the following command in CLI: php admin/tool/task/cli/schedule_task.php --execute="\tool_analytics\task\predict_models" Execute the following command in CLI and note down the id for the model which name is Upcoming activities due : php admin/tool/analytics/cli/evaluate_model.php --list Go to your mysql console and execute the following query replacing the uppercase placeholders: SELECT u.*, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_user u LEFT JOIN mdl_analytics_used_analysables aua ON u.id = aua.analysableid AND (aua.modelid = 'THE_MODEL_ID_ABOVE'' OR aua.modelid IS NULL) JOIN mdl_context ctx ON (ctx.contextlevel = '30' AND ctx.instanceid = u.id) WHERE 1=1 AND u.deleted = '0' AND u.confirmed = '1' AND u.suspended = '0'; The db query SHOULD run blazing fast (less than a second or a few seconds)
    • Affected Branches:
      MOODLE_37_STABLE
    • Fixed Branches:
      MOODLE_37_STABLE
    • Pull from Repository:
    • Pull 3.7 Branch:
    • Pull Master Branch:
      MDL-66599_master

      Description

      When executing analytics models or analytics scheduled tasks, this query hangs because MySQL optimizer don't find a suitable index to use. Last execution was killed after more than 29000 s running.

      The affected query is this:

       

      SELECT u.*, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked 
      FROM user u 
      LEFT JOIN analytics_used_analysables aua ON u.id = aua.analysableid AND (aua.modelid = '3' OR aua.modelid IS NULL) 
      JOIN context ctx ON (ctx.contextlevel = '30' AND ctx.instanceid = u.id) 
      WHERE 1=1 AND u.deleted = '0' AND u.confirmed = '1' AND u.suspended = '0';
      

      And this is the EXPLAIN result in moodle.org DB

       

       
       

      | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
      +----+-------------+-------+------------+--------+--------------------------------------------+-----------------+---------+----------------------+---------+----------+----------------------------------------------------+
      | 1 | SIMPLE | u | NULL | ref | PRIMARY,user_deleted,user_confirmed | user_deleted | 1 | const | 1104191 | 5.00 | Using where |
      | 1 | SIMPLE | ctx | NULL | eq_ref | cont_conins_uix,instanceid | cont_conins_uix | 16 | const,moodleorg.u.id | 1 | 100.00 | Using index condition |
      | 1 | SIMPLE | aua | NULL | ALL | analusedanal_modact_ix,analusedanal_mod_ix | NULL | NULL | NULL | 81664 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

      This query is located on core_analytics\local\analyser\base::get_iterator_sql()

       

        Attachments

        1. 70000Users-Upload.csv
          3.59 MB
        2. enrol-users.php
          0.8 kB
        3. MDL-66599.png
          MDL-66599.png
          256 kB

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  11/Nov/19

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 50 minutes
                  1d 50m