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

messaging_cleanup_task should use an indexed DB field for improved performance

    XMLWordPrintable

Details

    • MOODLE_35_STABLE, MOODLE_402_STABLE
    • MOODLE_404_STABLE
    • MDL-65403_master
    • Hide

      I have attached 4 SQL scripts for generating notifications records, 1 for each supported database engine. You may wish to modify this depending on your set-up, e.g. change to mdl_ table prefix to something else.

      You can also increase the 4000000 number on the last line to something larger if the  "Background processing for messaging" task runs quickly enough in your environment that you can't confidently state it's faster with the index.

      1. Deploy a site without the patch.
      2. If your site has a system cron job to run scheduled tasks, disable it.
      3. Go to Site Administration -> Server -> Tasks -> Scheduled tasks.
      4. Run in the terminal: php admin/cli/scheduled_task.php --execute='\core\task\messaging_cleanup_task'
      5. Run the attached generate_notifications script that is appropriate for your database.
      6. Run in the terminal: php admin/cli/scheduled_task.php --execute='\core\task\messaging_cleanup_task'
      7. Note the time it takes to execute, "...used XXX seconds"
      8. Truncate the mdl_notifications table
      9. Re-run the generate notifications script
      10. Deploy the patch and run the upgrade to created the new indexes
      11. Run in the terminal: php admin/cli/scheduled_task.php --execute='\core\task\messaging_cleanup_task'
      12. Note the time it takes to execute, "...used YYY seconds"
        1. Confirm YYY is faster than XXX.
      Show
      I have attached 4 SQL scripts for generating notifications records, 1 for each supported database engine. You may wish to modify this depending on your set-up, e.g. change to mdl_ table prefix to something else. You can also increase the 4000000 number on the last line to something larger if the  "Background processing for messaging" task runs quickly enough in your environment that you can't confidently state it's faster with the index. Deploy a site without the patch. If your site has a system cron job to run scheduled tasks, disable it. Go to Site Administration -> Server -> Tasks -> Scheduled tasks. Run in the terminal: php admin/cli/scheduled_task.php --execute='\core\task\messaging_cleanup_task' Run the attached generate_notifications script that is appropriate for your database. Run in the terminal: php admin/cli/scheduled_task.php --execute='\core\task\messaging_cleanup_task' Note the time it takes to execute, "...used XXX seconds" Truncate the mdl_notifications table Re-run the generate notifications script Deploy the patch and run the upgrade to created the new indexes Run in the terminal: php admin/cli/scheduled_task.php --execute='\core\task\messaging_cleanup_task' Note the time it takes to execute, "...used YYY seconds" Confirm YYY is faster than XXX.

    Description

      The messaging_cleanup_task process currently runs a query like this:
      DELETE FROM mdl_notifications WHERE timeread < TIMESTAMP;

      The mdl_notifications table can become quite large and the timeread field is not indexed which can lead to some very inefficient querying (several minutes on large tables instead of seconds). Please add an index to timeread to improve performance.

      Attachments

        Issue Links

          Activity

            People

              marxjohnson Mark Johnson
              dakota.duff Dakota Duff
              Sarah Cotton Sarah Cotton
              Huong Nguyen Huong Nguyen
              Kim Jared Lucas Kim Jared Lucas
              Votes:
              8 Vote for this issue
              Watchers:
              20 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour, 40 minutes
                  1h 40m

                  Clockify

                    Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.