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

message: message_provider_uninstall slow

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 4.4.5
    • Messages
    • MOODLE_404_STABLE
    • MDL-83628-m405
    • MDL-83628-m500
    • MDL-83628-main
    • Hide

      Prerequisites:

      • To carry out this test you need to be using a Postgres database.
      • You need access to run SQL updates on that database manually.

      Setup

      Run the following SQL commands (which take several minutes, ~7 mins on my laptop) to insert 7 million rows into your test user_preferences table. This assumes your database prefix is 'mdl_'. If it's something else, change the command:

      INSERT INTO mdl_user_preferences(userid, name, value)
      		 
      SELECT
      		 
        2000000000+num AS userid,
      		 
        'block_hidden_' || md5(random()::text) AS name,
      		 
        md5(random()::text) AS value
      		 
      FROM generate_series(1,7000000) AS g(num);
      		 
       
      		 
      VACUUM FULL mdl_user_preferences;
      		 
      REINDEX TABLE mdl_user_preferences;
      		 
      ANALYZE mdl_user_preferences;

      Test

      1. Go to System administration / Plugins / Plugins overview
      2. Select a plugin with no database tables and no other important data on your test system. I chose the question format plugins, for example qformat_aiken.
      3. Click the Uninstall link next to this plugin. You should get to a confirm screen.
      4. Prepare to time the next request by enabling browser developer tools and opening the Network tab.
      5. Click the Continue button and confirm.
      6. When the page loads, read the time duration for plugins.php in the developer tools.
        • EXPECTED: As this plugin has no database tables and not much other data, the request should complete reasonably quickly (see below).
      7. Reinstall the uninstalled plugin either by visiting the admin screen and continuing through all prompts, or by running the CLI upgrade (php admin/cli/upgrade.php --non-interactive).

      It is difficult to say what the actual expected performance should be, so I suggest it should just be roughly in line with other substantial Moodle actions on the system being used to test.

      I repeated the test 3 times using the new and old code, making sure to wait before clicking continue at step 5 until the browser had completely finished loading the page and system CPU had dropped below 30% (which usually took ages because my Windows laptop sucks). These are the durations reported in my browser for plugins.php when uninstalling the plugin:

      New 3.9s 6.0s 5.7s
      Old 7.3s 9.5s 7.0s

      Taking 'best of 3' we have 3.9s for the 'new' and 7.0s for the old, or about 3 seconds faster. This corresponds to the basic performance test results (see comments) so I think it's working.

      If you want to repeat the test for timing purposes it is necessary to complete step 7 (reinstall the plugin) and start again from the beginning by returning to the plugins page, rather than just immediately deleting some other plugin, because the system gets in a bit of a state if you leave a plugin half-deleted like this and it can take much longer. Presumably this is because caches still need updating or something (since all caches are purged when you uninstall a plugin).

      Tidying up

      To tidy up you can delete the junk from your database:

      DELETE FROM mdl_user_preferences 
      WHERE name LIKE 'message_provider_other_plugin_%';
      		 
      VACUUM FULL mdl_user_preferences;

      Show
      Prerequisites: To carry out this test you need to be using a Postgres database. You need access to run SQL updates on that database manually. Setup Run the following SQL commands (which take several minutes, ~7 mins on my laptop) to insert 7 million rows into your test user_preferences table. This assumes your database prefix is 'mdl_'. If it's something else, change the command: INSERT INTO mdl_user_preferences(userid, name, value) SELECT 2000000000+num AS userid, 'block_hidden_' || md5(random()::text) AS name, md5(random()::text) AS value FROM generate_series(1,7000000) AS g(num);   VACUUM FULL mdl_user_preferences; REINDEX TABLE mdl_user_preferences; ANALYZE mdl_user_preferences; Test Go to System administration / Plugins / Plugins overview Select a plugin with no database tables and no other important data on your test system. I chose the question format plugins, for example qformat_aiken . Click the Uninstall link next to this plugin. You should get to a confirm screen. Prepare to time the next request by enabling browser developer tools and opening the Network tab. Click the Continue button and confirm. When the page loads, read the time duration for plugins.php in the developer tools. EXPECTED: As this plugin has no database tables and not much other data, the request should complete reasonably quickly (see below). Reinstall the uninstalled plugin either by visiting the admin screen and continuing through all prompts, or by running the CLI upgrade ( php admin/cli/upgrade.php --non-interactive ). It is difficult to say what the actual expected performance should be, so I suggest it should just be roughly in line with other substantial Moodle actions on the system being used to test. I repeated the test 3 times using the new and old code, making sure to wait before clicking continue at step 5 until the browser had completely finished loading the page and system CPU had dropped below 30% (which usually took ages because my Windows laptop sucks). These are the durations reported in my browser for plugins.php when uninstalling the plugin: New 3.9s 6.0s 5.7s Old 7.3s 9.5s 7.0s Taking 'best of 3' we have 3.9s for the 'new' and 7.0s for the old, or about 3 seconds faster. This corresponds to the basic performance test results (see comments) so I think it's working. If you want to repeat the test for timing purposes it is necessary to complete step 7 (reinstall the plugin) and start again from the beginning by returning to the plugins page, rather than just immediately deleting some other plugin, because the system gets in a bit of a state if you leave a plugin half-deleted like this and it can take much longer. Presumably this is because caches still need updating or something (since all caches are purged when you uninstall a plugin). Tidying up To tidy up you can delete the junk from your database: DELETE FROM mdl_user_preferences WHERE name LIKE 'message_provider_other_plugin_%'; VACUUM FULL mdl_user_preferences;
    • Hide

      Code verified against automated checks.

      Checked MDL-83628 using repository: https://github.com/sammarshallou/moodle.git

      More information about this report

      Built on: Tue 05 Nov 2024 09:58:21 AM UTC

      Show
      Code verified against automated checks. Checked MDL-83628 using repository: https://github.com/sammarshallou/moodle.git MOODLE_404_STABLE (0 errors / 0 warnings) [branch: MDL-83628-m404 | CI Job ] MOODLE_405_STABLE (0 errors / 0 warnings) [branch: MDL-83628-m405 | CI Job ] main (0 errors / 0 warnings) [branch: MDL-83628-main | CI Job ] More information about this report Built on: Tue 05 Nov 2024 09:58:21 AM UTC
    • Hide

      Launching automatic jobs for branch MDL-83628-m404

      Launching automatic jobs for branch MDL-83628-m405

      Launching automatic jobs for branch MDL-83628-main

      Built on: Tue Nov 5 12:30:23 PM UTC 2024

      Show
      Launching automatic jobs for branch MDL-83628 -m404 https://ci.moodle.org/view/Testing/job/DEV.02%20-%20Developer-requested%20PHPUnit/17245/ PHPUnit (sqlsrv) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58769/ Behat (NonJS - boost and classic) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58770/ Behat (Firefox - boost) Launching automatic jobs for branch MDL-83628 -m405 https://ci.moodle.org/view/Testing/job/DEV.02%20-%20Developer-requested%20PHPUnit/17246/ PHPUnit (sqlsrv) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58771/ Behat (NonJS - boost and classic) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58772/ Behat (Firefox - boost) Launching automatic jobs for branch MDL-83628 -main https://ci.moodle.org/view/Testing/job/DEV.02%20-%20Developer-requested%20PHPUnit/17247/ PHPUnit (sqlsrv) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58773/ Behat (NonJS - boost and classic) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58774/ Behat (Firefox - boost) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58775/ Behat (Firefox - classic) https://ci.moodle.org/view/Testing/job/DEV.01%20-%20Developer-requested%20Behat/58776/ App tests (stable app version) --> unrelated Built on: Tue Nov 5 12:30:23 PM UTC 2024

      The message_provider_uninstall function is slow on large systems when using Postgres because of this line:

      $DB->delete_records_select('user_preferences', $DB->sql_like('name', '?', false), array("message_provider_{$component}_%"));

      The user_preferences table is a huge one on a system with lots of users, and although the 'name' field is indexed, this index doesn't appear to work for this call (at least in Postgres). Testing the database query involved, supposing you are deleting a plugin called local_frog, this would be roughly:

      DELETE FROM mdl_user_preferences WHERE name ILIKE 'message_provider_local_frog_%'

      This does a sequential scan and takes 12 seconds on my test system. Compare with:

      DELETE FROM mdl_user_preferences WHERE name LIKE 'message_provider_local_frog_%'

      This uses an index and completes instantly.

      I can see no reason for the case-insensitivity (that's the 'false' parameter in the function) because these preferences are always added with lower case; nobody is going to add one called Message_Provider_local_frog_anything let alone message_provider_Local_Frog_anything.

      So I propose removing the unnecessary case-insensitivity.

      While 12 seconds may not be a lot, if you are uninstalling a number of plugins as part of a system upgrade (that's how we do it), it adds some rather pointless wasted time to the upgrade during which the system may be offline. Also:

      • Even for a manual uninstall, the user experience is not great if it takes longer than needed.
      • There are systems with an order of magnitude more users than ours so it could be a larger problem than 12 seconds.

        1. deleteperformance.php
          2 kB

            quen Sam Marshall
            quen Sam Marshall
            Katie Ransom Katie Ransom
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 minutes
                2m

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