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

Slow search in 'Search people and messages'

    XMLWordPrintable

Details

    • MOODLE_311_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_400_STABLE
    • MOODLE_311_STABLE, MOODLE_400_STABLE
    • Hide

      Note: Test on different databases. At least PostgreSQL and MySQL

      Before the patch
      1. Check out a version of your site before the patch was applied.
      2. Install the site
      3. Create a few users (e.g. using MDK, "mdk run users")
      4. Download and run the script to generate messages on your site: testmessage_gen.php.
      5. Log in as an admin (the script would create a lot of messages for the admin user)
      6. Open the messaging panel
      7. Enter "e" on the search text field but do not execute the search yet.
      8. Add the following entry to the "$CFG->dboptions" array in config.php:

        'logslow'  => 0.5,
        

      9. Open your favourite database management software (e.g. pgAdmin, MySQL workbench, etc.)
      10. Open the database and query the mdl_log_queries table. e.g.

        SELECT id, sqltext, exectime, sqlparams FROM mdl_log_queries ORDER BY id DESC;
        

      11. Back on the browser window with the messaging pane open, run the search.
      12. Rerun the query for the mdl_log_queries table. Check the query run when searching messages.
        • On Postgres, it would look like

          SELECT  '' || m.id || '_' || m.useridfrom || '_' || mcm.userid  AS uniqueid, m.id, ...
          

        • On MySQL, it would look like

          SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, ...
          

          If there are no such records, try lowering the value of the 'logslow' option.

      13. Repeat the search until the exectime values have stabilised. Take note of this stabilised exectime value.
      14. Comment out the logslow DB option.
      15. Log in as another user that did not get as many messages (e.g. s10 or m3, if you ran the MDK users script).
      16. Open the messaging panel.
      17. Uncomment the logslow DB option.
      18. Repeat the same procedure. Taking note of the stabilised exectime before the patch.
      After the patch
      1. Check out the moodle version with the patch applied. Upgrade the site if necessary.
      2. Log in as admin.
      3. Repeat the message search and take note of the exectime values with the patch applied. (You may need to run the search several times until the exectime values stabilise).
      4. Check the mdl_log_queries table.
      5. Confirm that either one of the following happen:
        • The message search query is no longer being recorded because it's not considered a slow query.
        • Or the message search query is logged in the mdl_log_queries table, but the exectime is now better than before the patch.
      6. Repeat the procedure above for the second user
      7. Confirm that either one of the following happen:
        • The message search query is no longer being recorded because it's not considered a slow query.
        • Or the message search query is logged in the mdl_log_queries table, but the exectime is now better than before the patch.
      Show
      Note: Test on different databases. At least PostgreSQL and MySQL Before the patch Check out a version of your site before the patch was applied. Install the site Create a few users (e.g. using MDK, " mdk run users ") Download and run the script to generate messages on your site: testmessage_gen.php . Log in as an admin (the script would create a lot of messages for the admin user) Open the messaging panel Enter " e " on the search text field but do not execute the search yet. Add the following entry to the " $CFG->dboptions " array in config.php: 'logslow' => 0.5, Open your favourite database management software (e.g. pgAdmin, MySQL workbench, etc.) Open the database and query the mdl_log_queries table. e.g. SELECT id, sqltext, exectime, sqlparams FROM mdl_log_queries ORDER BY id DESC ; Back on the browser window with the messaging pane open, run the search. Rerun the query for the mdl_log_queries table. Check the query run when searching messages. On Postgres, it would look like SELECT '' || m.id || '_' || m.useridfrom || '_' || mcm.userid AS uniqueid, m.id, ... On MySQL, it would look like SELECT CONCAT(m.id, '_' , m.useridfrom, '_' , mcm.userid) AS uniqueid, m.id, ... If there are no such records, try lowering the value of the 'logslow' option. Repeat the search until the exectime values have stabilised. Take note of this stabilised exectime value. Comment out the logslow DB option. Log in as another user that did not get as many messages (e.g. s10 or m3, if you ran the MDK users script). Open the messaging panel. Uncomment the logslow DB option. Repeat the same procedure. Taking note of the stabilised exectime before the patch. After the patch Check out the moodle version with the patch applied. Upgrade the site if necessary. Log in as admin. Repeat the message search and take note of the exectime values with the patch applied. (You may need to run the search several times until the exectime values stabilise). Check the mdl_log_queries table. Confirm that either one of the following happen: The message search query is no longer being recorded because it's not considered a slow query. Or the message search query is logged in the mdl_log_queries table, but the exectime is now better than before the patch. Repeat the procedure above for the second user Confirm that either one of the following happen: The message search query is no longer being recorded because it's not considered a slow query. Or the message search query is logged in the mdl_log_queries table, but the exectime is now better than before the patch.

    Description

      In the text box for 'Search for people and messages' there is a lot of slowness in any type of search, lasting 5 to 10 minutes depending on the search performed, on platforms with a large number of messages and users, such as one or two million messages and thousands of users.

       

       

       

      Attachments

        1. core_message_data_for_messagearea_search_messages-trace.png
          core_message_data_for_messagearea_search_messages-trace.png
          63 kB
        2. MDL-66955_master_mysql.png
          MDL-66955_master_mysql.png
          30 kB
        3. MDL-66955_master_postgre.png
          MDL-66955_master_postgre.png
          23 kB
        4. MDL-66955_v311_mysql.png
          MDL-66955_v311_mysql.png
          29 kB
        5. MDL-66955_v311_postgre.png
          MDL-66955_v311_postgre.png
          19 kB
        6. MDL-66955_v400_mysql.png
          MDL-66955_v400_mysql.png
          30 kB
        7. MDL-66955_v400_postgre.png
          MDL-66955_v400_postgre.png
          22 kB
        8. mensajeria-1.PNG
          mensajeria-1.PNG
          15 kB
        9. mensajeria-2.PNG
          mensajeria-2.PNG
          12 kB
        10. testmessage_gen_nm.php
          7 kB
        11. testmessage_gen.php
          3 kB
        12. testuser_gen_nm.php
          0.3 kB
        13. user_message_search.txt
          41 kB

        Issue Links

          Activity

            People

              nmagill Neill Magill
              generazion Generazion Consulting S.L.
              Mark Johnson Mark Johnson
              Jun Pataleta Jun Pataleta
              John Edward Pedregosa John Edward Pedregosa
              Votes:
              11 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

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

                  Clockify

                    Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.