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

Missing logstore_standard index significantly slows backups with logs

    XMLWordPrintable

Details

    • MOODLE_30_STABLE, MOODLE_31_STABLE
    • MOODLE_30_STABLE
    • MDL-53795-master
    • Hide

      The performance implication for this is hard to test, since it requires very large logstore_standard_log tables (for reference, ours is ~145M records), and courses with logs of activities with logs.

      Because of this, we basically want to make sure we didn't break anything:

      Start before applying the patch.

      1. Create a course with a number of resources (like Page). You can just use a Small test course.
      2. As both a teacher and a student, navigate around the course and into pages. The goal here is just to make log records.
      3. Backup the course, making sure to include course logs.
      4. Apply this patch
      5. Backup the course again, again making sure the course logs are enabled.
      6. Download and extract both back files.
      7. Recursively diff the two resulting folders
      8. Compare them, there should only be trivial differences between them.

      Some additional testing steps:

      • Do a clean install with this patch, examine the DB and ensure the index on the contextid field in the logstore_standard_log table was created.
      • Do an upgrade from a previous version, examine the DB and ensure the index on the contextid field in the logstore_standard_log table was created.
      • On a pre-upgrade site, manually add the index on contextid, then upgrade the site and make sure there are no errors.
      Show
      The performance implication for this is hard to test, since it requires very large logstore_standard_log tables (for reference, ours is ~145M records), and courses with logs of activities with logs. Because of this, we basically want to make sure we didn't break anything: Start before applying the patch. Create a course with a number of resources (like Page). You can just use a Small test course. As both a teacher and a student, navigate around the course and into pages. The goal here is just to make log records. Backup the course, making sure to include course logs. Apply this patch Backup the course again, again making sure the course logs are enabled. Download and extract both back files. Recursively diff the two resulting folders Compare them, there should only be trivial differences between them. Some additional testing steps: Do a clean install with this patch, examine the DB and ensure the index on the contextid field in the logstore_standard_log table was created. Do an upgrade from a previous version, examine the DB and ensure the index on the contextid field in the logstore_standard_log table was created. On a pre-upgrade site, manually add the index on contextid, then upgrade the site and make sure there are no errors.

    Description

      While performing course backups, we noticed it was taking much longer than previously.

      After some hunting, we found this to largely be caused by the addition of logstore_standard to the backups (which is a must!), but more specifically, this query:

      SELECT * FROM mdl_logstore_standard_log WHERE contextid = $1

      We quickly discovered there is no index covering contextid on logstore_standard_log.

      We manually created such an index on our dataset, and the time to backup 13 example courses went from 160 minutes to 5 minutes (no, that is not a typo).

      Attachments

        Issue Links

          Activity

            People

              emerrill Eric Merrill
              emerrill Eric Merrill
              Mark Nelson Mark Nelson
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Ryan Wyllie Ryan Wyllie
              Votes:
              5 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                9/May/16