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
    • 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
              Votes:
              0 Vote for this issue
              Watchers:
              11 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 - 5 hours, 1 minute
                  5h 1m

                  Clockify

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