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

Improve mdl_notifications 3.7->3.9 upgrade script performance

XMLWordPrintable

    • 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.

      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.

       

        1. image-2020-10-05-12-24-03-835.png
          19 kB
          Misha Golenkov
        2. image-2020-10-05-13-47-49-895.png
          17 kB
          Misha Golenkov
        3. image-2020-10-05-13-52-38-768.png
          17 kB
          Misha Golenkov

            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

              Created:
              Updated:
              Resolved:

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

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