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

Performance Issue with mod/data/view.php

    Details

    • Database:
      MySQL
    • Testing Instructions:
      Hide

      (Regression testing)

      Warning! This needs to be tested on all databases.

      1. Create a database activity.
      2. Add fields and save the template, or alternatively use the preset and import some entries.
        • (Please note with the csv file that it contains 500 entries. This may take some time to import. Feel free to remove records to make this process faster)
      3. Do a few simple searches and a few advanced searches on the database.
        • Check out the paging of the results.
        • Use different sorting criteria.
        • Check to make sure that there are no errors displayed.
          Ideally you would check to make sure that the right number of entries are returned.
      Show
      (Regression testing) Warning! This needs to be tested on all databases. Create a database activity. Add fields and save the template, or alternatively use the preset and import some entries. (Please note with the csv file that it contains 500 entries. This may take some time to import. Feel free to remove records to make this process faster) Do a few simple searches and a few advanced searches on the database. Check out the paging of the results. Use different sorting criteria. Check to make sure that there are no errors displayed. Ideally you would check to make sure that the right number of entries are returned.
    • Affected Branches:
      MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE, MOODLE_23_STABLE
    • Pull from Repository:

      Description

      We applied a patch of MDL-35558 to our production servers, and are noticing an extremely high amount of DB activity as a result. The offending query is:

      SELECT DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname
      FROM mdl_data_content c, mdl_data_records r, mdl_data_content cs, mdl_user u
      WHERE c.recordid = r.id
      AND r.dataid = '52'
      AND r.userid = u.id
      ORDER BY r.timecreated ASC , r.id ASC

      Output of Explain:

      ------------------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ------------------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE r ref PRIMARY,mdl_datareco_dat_ix mdl_datareco_dat_ix 8 const 1031 Using temporary; Using filesort
      1 SIMPLE u eq_ref PRIMARY PRIMARY 8 credability.r.userid 1  
      1 SIMPLE c ref mdl_datacont_rec_ix mdl_datacont_rec_ix 8 credability.r.id 15 Using index; Distinct
      1 SIMPLE cs index NULL mdl_datacont_rec_ix 8 NULL 35526 Using index; Distinct; Using join buffer

      ------------------------------------------------------------------------------------------------------------------------------------------------+

      The biggest difference is that "AND cs.recordid = r.id" clause was moved out of the main query into the basic search query in 2 places:

      The root cause appears to stem from a partially backported fix from MDL-35961. In that fix, the second reference to "

      {data_content} cs" and corresponding filter on "AND cs.recordid = r.id" is removed. In MDL-35558, only the WHERE clause was modified, which means that the result set is multiplied by the total records in {data_content}

      . Because the query is only selecting DISTINCT values, this bug will appear undetected to functional tests although it is a major drain on database performance.

      The suggested fix is to either remove the join on

      {data_content}

      , or to add the filter back to the where clause.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

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

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    14/Jan/13