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

Reduce the amout of db queries during execution of community of inquiry indicators

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.4
    • Fix Version/s: 3.4
    • Component/s: Analytics
    • Labels:
    • Testing Instructions:
      Hide
      1. Unit test pass.

      For extra points, go through MDL-59067 testing instructions

      1. In Execute php admin/tool/task/cli/schedule_task.php --execute=\\tool_analytics\\task
        train_models step note the number of db queries
      2. Execute truncate table mdl_analytics_predict_samples ; truncate table mdl_analytics_indicator_calc ;truncate table mdl_analytics_predictions ;truncate table mdl_analytics_train_samples ;truncate table mdl_analytics_used_files; delete from mdl_files where component = 'analytics'
      3. Revert the merge commit of this issue. Something like git revert I-AM-THE-GIT-MERGE-HASH -m 1
      4. Execute php admin/tool/task/cli/schedule_task.php --execute=\\tool_analytics\\task
        train_models again
      5. The number of db queries SHOULD be higher
      Show
      Unit test pass. For extra points, go through MDL-59067 testing instructions In Execute php admin/tool/task/cli/schedule_task.php --execute=\\tool_analytics\\task train_models step note the number of db queries Execute truncate table mdl_analytics_predict_samples ; truncate table mdl_analytics_indicator_calc ;truncate table mdl_analytics_predictions ;truncate table mdl_analytics_train_samples ;truncate table mdl_analytics_used_files; delete from mdl_files where component = 'analytics' Revert the merge commit of this issue. Something like git revert I-AM-THE-GIT-MERGE-HASH -m 1 Execute php admin/tool/task/cli/schedule_task.php --execute=\\tool_analytics\\task train_models again The number of db queries SHOULD be higher
    • Affected Branches:
      MOODLE_34_STABLE
    • Fixed Branches:
      MOODLE_34_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-59779_master

      Description

      While trying to improve analytics engine performance I've noticed that we could improve some stuff.

      Using the most simple training scheme (just 1 course with 1 student and 1 activity) I've seen that there are around 100 db queries:

      • Around the 30% of them are queries to mdl_grade_items that come from grade_get_grades call, this seems a lot to me and we need to inspect what this function is doing to see if it makes sense that 1 course with 1 activity generates 30 calls to the same table (which if I am not missing something should contain 2 items in that course)

        * line 220 of /lib/grade/grade_object.php: call to moodle_database->get_recordset_select()
        * line 160 of /lib/grade/grade_object.php: call to grade_object::fetch_all_helper()
        * line 354 of /lib/grade/grade_item.php: call to grade_object::fetch_helper()
        * line 1125 of /lib/grade/grade_item.php: call to grade_item::fetch()
        * line 437 of /lib/gradelib.php: call to grade_item::fetch_course_item()
        * line 496 of /analytics/classes/course.php: call to grade_get_grades()
        * line 400 of /analytics/classes/local/indicator/community_of_inquiry_activity.php: call to core_analytics\course->get_student_grades()
        * line 496 of /analytics/classes/local/indicator/community_of_inquiry_activity.php: call to core_analytics\local\indicator\community_of_inquiry_activity->get_student_activities()
        * line 641 of /analytics/classes/local/indicator/community_of_inquiry_activity.php: call to core_analytics\local\indicator\community_of_inquiry_activity->cognitive_calculate_sample()
        * line 149 of /analytics/classes/local/indicator/base.php: call to core_analytics\local\indicator\community_of_inquiry_activity->calculate_sample()
        * line 249 of /analytics/classes/local/time_splitting/base.php: call to core_analytics\local\indicator\base->calculate()
        

      • Around a 15% from core_analytics\course instantiations (they fetch role archetypes and role ids) This should be easily cached.

        ++ SELECT * FROM m_role WHERE archetype = $1  ORDER BY sortorder ASC ++
        * line 432 of /lib/dml/moodle_database.php: call to debugging()
        * line 228 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_start()
        * line 774 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_start()
        * line 1400 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->get_records_sql()
        * line 1351 of /lib/dml/moodle_database.php: call to moodle_database->get_records_select()
        * line 2664 of /lib/accesslib.php: call to moodle_database->get_records()
        * line 146 of /analytics/classes/course.php: call to get_archetype_roles()
        * line 167 of /analytics/classes/course.php: call to core_analytics\course->__construct()
        * line 57 of /analytics/classes/local/analyser/by_course.php: call to core_analytics\course::instance()
        * line 79 of /analytics/classes/local/analyser/by_course.php: call to core_analytics\local\analyser\by_course->get_courses()
        * line 187 of /analytics/classes/local/analyser/base.php: call to core_analytics\local\analyser\by_course->get_analysable_data()
        * line 589 of /analytics/classes/model.php: call to core_analytics\local\analyser\base->get_labelled_data()
        * line 73 of /admin/tool/analytics/classes/task/train_models.php: call to core_analytics\model->train()
        * line 146 of /admin/tool/task/cli/schedule_task.php: call to tool_analytics\task\train_models->execute()
        

      • Around the 22% of the calls deal with mdl_files, they creates directories, files... This should be ok and the percent shouldn't be that high when under normal circumstances because there is 1 set of db operations (maybe 2 or 3 db interactions) per analysable so it is acceptable IMO and I'm not sure if we could do much anyway
      • Around a 15% of the calls read from the standard logstore, which is also ok

      We should be able to reduce a lot the amount of db reads to grade_items and roles stuff (also important that MDL-59694 will reduce the amount of courses we keep in memory and the amount of roles tables db reads at the same time)

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/Nov/17