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

Improve mdl_notifications 3.7->3.9 upgrade script performance

    XMLWordPrintable

    Details

    • Database:
      Any
    • Testing Instructions:
      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.
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_39_STABLE
    • Pull from Repository:
    • Pull 3.9 Branch:
      MOODLE_39_MDL-69848
    • Pull 3.10 Branch:
      MOODLE_310_MDL-69848
    • Pull Master Branch:
      master_MDL-69848

      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

              Assignee:
              mikhailgolenkov Mikhail Golenkov
              Reporter:
              mikhailgolenkov Mikhail Golenkov
              Peer reviewer:
              Dmitrii Metelkin
              Integrator:
              Jake Dallimore
              Tester:
              Amaia Anabitarte
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                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