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

Improve mdl_notifications 3.7->3.9 upgrade script performance

    XMLWordPrintable

Details

    • Any
    • MOODLE_310_STABLE, MOODLE_39_STABLE
    • MOODLE_39_STABLE
    • MOODLE_39_MDL-69848
    • master_MDL-69848
    • Hide

      1. Create a fresh Moodle 3.7 instance.

      2. Add records:

      psql> insert into mdl_notifications (useridfrom, useridto, subject, fullmessageformat, timecreated) select i, i, 'This is a subject', 0, 1601623098 from generate_series(1,10000000) i;
      psql> insert into mdl_message_popup_notifications (notificationid) select 66000000 from generate_series(1,1000000) i;
      

      3. Checkout to the target Moodle version (3.9, 3.10, master).

      4. Run upgrade from the GUI.

      5. Confirm, that the `message_popup` progress bar displays the progress.

      6. Confirm, that the number of records is going down:

      psql> select count(1) from mdl_message_popup_notifications;
      

      7. Confirm, that upgrade finished successfully.

      8. Repeat 1-3 and run upgrade from the CLI:

      php admin/cli/upgrade.php
      

      9. Confirm, that upgrade finished successfully.

      Show
      1. Create a fresh Moodle 3.7 instance. 2. Add records: psql> insert into mdl_notifications (useridfrom, useridto, subject, fullmessageformat, timecreated) select i, i, 'This is a subject' , 0 , 1601623098 from generate_series( 1 , 10000000 ) i; psql> insert into mdl_message_popup_notifications (notificationid) select 66000000 from generate_series( 1 , 1000000 ) i; 3. Checkout to the target Moodle version (3.9, 3.10, master). 4. Run upgrade from the GUI. 5.  Confirm , that the `message_popup` progress bar displays the progress. 6. Confirm , that the number of records is going down: psql> select count( 1 ) from mdl_message_popup_notifications; 7. Confirm , that upgrade finished successfully. 8. Repeat 1-3 and run upgrade from the CLI: php admin/cli/upgrade.php 9. Confirm , that upgrade finished successfully.

    Description

      With 10 million records in `mdl_notifications` table and a million in `mdl_message_popup_notifications` upgrade cli script hangs due to non optimized delete:

      https://github.com/moodle/moodle/blob/MOODLE_39_STABLE/message/output/popup/db/upgrade.php#L49

       With numbers mentioned it had been working for 8 hours before I terminated the script.

       

      Attachments

        Issue Links

          Activity

            People

              mikhailgolenkov Misha Golenkov
              mikhailgolenkov Misha Golenkov
              Dmitrii Metelkin Dmitrii Metelkin
              Jake Dallimore Jake Dallimore
              Amaia Anabitarte Amaia Anabitarte
              Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                9/Nov/20

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 5 hours, 1 minute
                  5h 1m