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

Search on the Check permissions page is very slow with a large users table

Details

    • MySQL, PostgreSQL, Microsoft SQL, Oracle
    • MOODLE_311_STABLE, MOODLE_400_STABLE, MOODLE_401_STABLE
    • MDL-77742-402
    • MDL-77742-403
    • MDL-77742-master
    • Hide

      Pre-conditions:

      • Create multiple custom user profile fields of type text input in /user/profile/index.php (make sure it is an identified field ( Character limit < 10)
      • Create a more significant number of users (at least 500K records to test the performance), some of who have the profile field set
      • Go to Admin -> Users -> User policies > Show user identity > Enable custom user profile field in Show user identity¬†

      Scenario:

      Admin page:

      • Go to Users -> Check system permissions
      • Test the search with the text that has a custom user profile field or just a normal text

      Course page:

      • Go to the Check permissions page in a course
      • Test the search with the text that has a custom user profile field or just a normal text

      Course page -> Grades tab

      • Go to Grade History page
      • Test the search with the text that has a custom user profile field or just a normal text

      Expected: the search feature should work as normal

      Now, repeat the tests above, but without selecting the custom profile fileds under "Admin -> Users -> User policies > Show user identity" to check that there are no errors in this case.

      Show
      Pre-conditions: Create multiple custom user profile fields of type text input in /user/profile/index.php (make sure it is an identified field ( Character limit < 10) Create a more significant number of users (at least 500K records to test the performance), some of who have the profile field set Go to Admin -> Users -> User policies > Show user identity > Enable custom user profile field in Show user identity  Scenario: Admin page: Go to Users -> Check system permissions Test the search with the text that has a custom user profile field or just a normal text Course page: Go to the Check permissions page in a course Test the search with the text that has a custom user profile field or just a normal text Course page -> Grades tab Go to Grade History page Test the search with the text that has a custom user profile field or just a normal text Expected : the search feature should work as normal Now, repeat the tests above, but without selecting the custom profile fileds under "Admin -> Users -> User policies > Show user identity" to check that there are no errors in this case.

    Description

      Steps to reproduce, if you have a Moodle site with serveral million users:

      1. As admin (or other role with access) go to the Check permisions page for a course of an activity. (That is, a page like https://qa.moodledemo.net/admin/roles/check.php?contextid=25, but the demo site does not have enough users to trigger the bug.)
      2. Type something in the search box.

      Expected result: search results returned in reasonable time.

      Acutal result: painfully slow, possibly even leading to the Ajax request timing out and locking up your session.

      The problem is with the quries in admin/roles/classes/check_users_selector.php. For example, there is one doing ... FROM {user} u JOIN (SELECT DISTINCT subu.id FROM {user} subu JOIN ...) which looks very dangerous. Another does ... FROM {user} u LEFT JOIN ({user_enrolments} ue ... which also looks dangerous.

      Someone needs to get in there and EXPLAIN those, work out which one(s) are the problem, and optimise them, but they are showing up in our slow query log, taking times like 50+ seconds sometimes.

      Following analysis, I think there are several fixes that will help

      1. Do an intial query to find the required fieldids, and then put those directly in the main query, instead of requiring a separate join.
      2. Change the main query, so that instead of doing SLECT FROM users LEFT JOIN user_info_data it does SELECT FROM users ... UNION SELECT FROM users JOIN user_info_data.
      3. Implement an 'exact matches only' option.

      I will do 1. and 2. here, and I will do 3. in a separate issue: MDL-78312.

      Attachments

        1. image-2023-10-19-20-03-18-813.png
          image-2023-10-19-20-03-18-813.png
          116 kB
        2. record.webm
          8.41 MB
        3. Result01_After.png
          Result01_After.png
          116 kB
        4. Result01_Before.png
          Result01_Before.png
          112 kB
        5. smartdata.php
          32 kB
        6. TestResult_AdminSearchPermissioninCourse.mp4
          9.09 MB

        Issue Links

          Activity

            People

              vuvanhieu143 Hieu Vu
              timhunt Tim Hunt
              Tim Hunt Tim Hunt
              Ilya Tregubov Ilya Tregubov
              Votes:
              5 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

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

                  Clockify

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