Moodle
  1. Moodle
  2. MDL-20018

CLONE - Scheduled course backup runs full table scan on mdl_backup_log - possible regression.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 1.8.4, 1.9
    • Fix Version/s: None
    • Component/s: Backup, Performance
    • Labels:
      None
    • Environment:
      MySQL 5.0.22
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE
    • Rank:
      5478

      Description

      If scheduled course backups are enabled, cron.php calls schedule_backup_cron() whenever it runs. Line 36 of backup/backup_scheduled.php is

      $numofrec = count_records_select ("backup_log","time > $timeafter");

      This runs a full scan on mdl_backup_log, which can be a very large table depending on your site's log-retention policy. For example, we have cron.php scheduled to run every 5 minutes, and ~65,000 rows are read, simply to find out whether a backup task is currently running. The query takes around 4 seconds to complete. A workaround is to add an index to the table.

      ALTER TABLE mdl_backup_log ADD INDEX (time);

      This avoids the full table scan, and the query now completes in less than one hundredth of a second at our site. The drawback is that mdl_backup_log.MYI is now almost as big as mdl_backup_log.MYD I can't see any way of working around this issue in the PHP code.

        Issue Links

          Activity

          Hide
          Lee Bowyer added a comment -

          Eloy,
          Having just upgraded a moodle instance to 1.9.5+ from 1.8.4 (Upgrading Moodle database from version 1.8.4 (2007021540) to 1.9.5+ (Build: 20090805) (2007101550)...) - I notice that no index was created for mdl_backup_log.time.

          Also running check indexes produces the following for mdl_backup_log:
          backup_log
          Key: primary (id) OK
          Key: foreign (courseid) references backup_courses (courseid) OK

          no reference to the time column is made.

          I can also confirm that this produces a considerable performance boost once the index is created manually.

          ~L

          Show
          Lee Bowyer added a comment - Eloy, Having just upgraded a moodle instance to 1.9.5+ from 1.8.4 (Upgrading Moodle database from version 1.8.4 (2007021540) to 1.9.5+ (Build: 20090805) (2007101550)...) - I notice that no index was created for mdl_backup_log.time. Also running check indexes produces the following for mdl_backup_log: backup_log Key: primary (id) OK Key: foreign (courseid) references backup_courses (courseid) OK no reference to the time column is made. I can also confirm that this produces a considerable performance boost once the index is created manually. ~L
          Hide
          Eloy Lafuente (stronk7) added a comment -

          This issue was assigned to me automatically, however I will not be able to work on this issue in the immediate future. In order to create a truer sense of the state of this issue and to allow other developers to have chance to become involved, I am removing myself as the assignee of this issue.

          For more information, see http://docs.moodle.org/dev/Changes_to_issue_assignment

          Show
          Eloy Lafuente (stronk7) added a comment - This issue was assigned to me automatically, however I will not be able to work on this issue in the immediate future. In order to create a truer sense of the state of this issue and to allow other developers to have chance to become involved, I am removing myself as the assignee of this issue. For more information, see http://docs.moodle.org/dev/Changes_to_issue_assignment
          Hide
          Marina Glancy added a comment -

          Thanks for reporting this.

          I'm closing this issue because I believe it affects only unsupported versions of Moodle. This issue will remain here in case other users have the same problem.

          If you haven't already done so, I encourage you to upgrade to a supported version.

          Show
          Marina Glancy added a comment - Thanks for reporting this. I'm closing this issue because I believe it affects only unsupported versions of Moodle. This issue will remain here in case other users have the same problem. If you haven't already done so, I encourage you to upgrade to a supported version.

            People

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

              Dates

              • Created:
                Updated:
                Resolved: