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

external_tokens table needs index on sid

    XMLWordPrintable

Details

    • Any
    • MOODLE_310_STABLE, MOODLE_39_STABLE, MOODLE_401_STABLE
    • MOODLE_401_STABLE
    • MDL-71202-master
    • Hide

      Upgrading

      1. Install a version of Moodle without this patch (i.e. the most recent weekly development build)
      2. Update the code to include this version
      3. Run the Moodle upgrade
      4. Verify there are no PHP errors at any stage
      5. Access the Moodle database directly
      6. Verify that there is an index for the sid field in the external_tokens table

      Installing

      1. Install Moodle using a version that includes this patch
      2. Access the Moodle database directly
      3. Verify that there is an index for the sid field in the external_tokens table
      Show
      Upgrading Install a version of Moodle without this patch (i.e. the most recent weekly development build) Update the code to include this version Run the Moodle upgrade Verify  there are no PHP errors at any stage Access the Moodle database directly Verify  that there is an index for the  sid  field in the  external_tokens  table Installing Install Moodle using a version that includes this patch Access the Moodle database directly Verify  that there is an index for the  sid  field in the  external_tokens  table

    Description

      Whilst stress/performance testing Moodle with k6 and AWS Performance Insights / MySQL Performance Schema, we noticed that the log out process was very expensive.

      This was due to a full table scan / table locking occurring on the external_tokens table, as during the logout process the relevant token is looked up by sid, and external_tokens.sid is not currently indexed, resulting in a full table scan and table locks (rather than row level locks) on what can be a large table in a busy Moodle site.

      Adding an index to external_tokens.sid resolved this issue, and shouldn't add too much additional size to the DB files.  

      Will create a patch for review.

      Attachments

        Activity

          People

            peedeeboy Pete Whelpton
            peedeeboy Pete Whelpton
            Ilya Tregubov Ilya Tregubov
            Jun Pataleta Jun Pataleta
            Angelia Dela Cruz Angelia Dela Cruz
            David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, Juan Leyva, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              28/Nov/22

              Time Tracking

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