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

Improve loading performance of filepicker when loading Recent Files

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      The test must be carried out before and after applying the patch.
      It is necessary to have a Moodle site with a large number of records in the files and files_reference tables, using PostgreSQL as database (tested in versions 9.5 and 12.2).
      A script for the generation of these records in the database is attached.

      Setup

      Assuming a clean Moodle instance.

      1. Create 15 users.
        • If you use mdk:

          mdk run users
          

      2. Download the attached file setup_db.php under admin/cli.
        • Run the script. It'll take a while to complete.

          php admin/cli/setup_db.php
          

      Test 1 - Before applying the patch

      1. Log in as admin.
      2. Access any filepicker, for example:
        • Edit the user profile.
        • In the text editor of the Description field, click on the "Manage files" icon.
      3. Click on the "Recent files" repository icon.
      4. Confirm the recent files list never loads/takes a very long time while the loading icon spins continuously.

      Note: I guess different environments might require more/fewer records in the tables. If you can't reproduce the behaviour in step 5, run the create_test_file.php script again (step 2 of the Setup). I was able to reproduce it with 210000 records in the files table and 40000 in the files_reference table.

      Test 2 - After applying the patch

      1. Restart the PostgreSQL database process.
      2. If you are still on the Manage files model, you can click on the "Refresh" icon or the "Recent files" repository icon. Otherwise, you can repeat steps 1-4 of Test 1.
      3. Confirm that the recent files list is displayed almost right away.

       

      The new configuration setting is covered by the unit tests.

      Show
      The test must be carried out before and after applying the patch. It is necessary to have a Moodle site with a large number of records in the files and files_reference tables, using PostgreSQL as database (tested in versions 9.5 and 12.2). A script for the generation of these records in the database is attached. Setup Assuming a clean Moodle instance. Create 15 users. If you use mdk : mdk run users Download the attached file setup_db.php under admin/cli . Run the script. It'll take a while to complete. php admin /cli/setup_db .php Test 1 - Before applying the patch Log in as admin. Access any filepicker, for example: Edit the user profile. In the text editor of the Description field, click on the "Manage files" icon. Click on the "Recent files" repository icon. Confirm the recent files list never loads/takes a very long time while the loading icon spins continuously. Note: I guess different environments might require more/fewer records in the tables. If you can't reproduce the behaviour in step 5, run the create_test_file.php script again (step 2 of the Setup). I was able to reproduce it with 210000 records in the files table and 40000 in the files_reference table. Test 2 - After applying the patch Restart the PostgreSQL database process. If you are still on the Manage files model, you can click on the "Refresh" icon or the "Recent files" repository icon. Otherwise, you can repeat steps 1-4 of Test 1. Confirm that the recent files list is displayed almost right away.   The new configuration setting is covered by the unit tests.
    • Workaround:
      Hide

      Go to /admin/repository.php and set recent files to "enabled but hidden".

      It will disable that feature for all users.

      Show
      Go to /admin/repository.php and set recent files to "enabled but hidden". It will disable that feature for all users.
    • Affected Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_30_STABLE, MOODLE_31_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
    • Fixed Branches:
      MOODLE_39_STABLE
    • Pull Master Branch:
      MDL-39571-Master

      Description

      To Reproduce:

      1) Need a large moodle install on Postgres 9.2

      or use the create_test_file.php to generate  files, place the file under admin/cli and run "php admin/cli/create_test_file.php"
      2) Log in as admin and access any filepicker.
      3) Click on Recent Files
      4) The recent file list never loads/takes a very long time and the progress indicator spins and spins.

      • The setting for max number of recent files in the repository settings is: 25
        -DB running on postgres 9.2

      Cause:

      On the DB server, i can see one query running for a long time:

      SELECT files1.*
      FROM mdl_files files1
      LEFT JOIN mdl_files_reference r
      ON files1.referencefileid = r.id
      JOIN (
      SELECT contenthash, filename, MAX(id) AS id
      FROM mdl_files
      WHERE userid = $1
      AND filename != $2
      AND ((filearea = $3 AND itemid = $4) OR filearea != $5)
      GROUP BY contenthash, filename
      ) files2 ON files1.id = files2.id
      WHERE r.repositoryid is NULL
      ORDER BY files1.timemodified DESC LIMIT 25 OFFSET 0

      I assume that this query is running excessively slow because the mdl_files table is quite large (477161 rows or more).

      Also, this only affects the users with a lot of files uploaded

      When the uses many entries in mdl_files the query is run in a different order – Using the EXPLAIN for the query, the optimiser is change the order of the executing and performing the LEFT JOIN before the INNER JOIN which is very bad in terms of performance.

        Attachments

        1. create_test_file.php
          2 kB
        2. MDL-39571.gif
          MDL-39571.gif
          700 kB
        3. setup_db.php
          3 kB

          Issue Links

            Activity

              People

              Assignee:
              Nguyen Nathan Nguyen
              Reporter:
              gbridge Gordon Bridge
              Peer reviewer:
              Shamim Rezaie
              Integrator:
              Víctor Déniz Falcón
              Tester:
              Ferran Recio
              Participants:
              Component watchers:
              Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
              Votes:
              17 Vote for this issue
              Watchers:
              29 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                15/Jun/20

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 days, 6 hours
                  2d 6h