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

Bloated notifications table full with no way to reduce it down via the UI

XMLWordPrintable

    • MOODLE_35_STABLE, MOODLE_36_STABLE
    • MOODLE_35_STABLE, MOODLE_36_STABLE
    • MDL-64400-master
    • Hide

      Pre-requisites

      1. Either an SMTP server configured, or mailcatcher/mailhog configured

      Test

      1. As admin create a new course with at least a forum.
      2. Enrol at least 2 students in that course.
      3. As user1, subscribe to the forum.
      4. As user1 go to your Preferences -> Notification Preferences and switch on all options for Subscribed forum posts
      5. As user2, subscribe to the forum.
      6. As user2 go to your Preferences -> Notification Preferences, switch on options for Web Subscribed forum posts and switch off options for Email Subscribed forum posts.
      7. As admin open a new discussion in the forum and mark as 'Send forum post notifications with no editing-time delay' before posting it.
      8. As admin run these tasks: 

        php admin/tool/task/cli/schedule_task.php --execute="\mod_forum\task\cron_task"
        		 
        php admin/tool/task/cli/schedule_task.php --execute="\core\task\messaging_cleanup_task"

      9. As user1 see that you don't have any new notification to read. But click on notifications bubble and go to See all and see that notification related to the forum exists and is marked as read.
      10. As user2 see that you have a new notification. Don't read it.
      11. As admin go to Site administration -> Advanced features. Choose 1 day for 'Delete read notifications' and 1 month 'Delete all notifications' and Save Changes.
      12. As admin wait for 1 day (or change server time if possible) and run the cron task: 

        php admin/tool/task/cli/schedule_task.php --execute="\core\task\messaging_cleanup_task"

      13. As user1 go to Notifications -> See all and check that the forum notification is not there anymore.
      14. As user2 go to Notifications -> See all and check that the forum notification is still there.
      15. As admin go to Site administration -> Advanced features. Choose 1 day for 'Delete all notifications' and Save Changes.
      16. As admin run the cron task: 

        php admin/tool/task/cli/schedule_task.php --execute="\core\task\messaging_cleanup_task"

      17. As user2 go to Notifications -> See all and check that the forum notification is not there anymore.
      Show
      Pre-requisites Either an SMTP server configured, or mailcatcher/mailhog configured Test As admin create a new course with at least a forum. Enrol at least 2 students in that course. As user1, subscribe to the forum. As user1 go to your Preferences -> Notification Preferences and switch on all options for Subscribed forum posts As user2, subscribe to the forum. As user2 go to your Preferences -> Notification Preferences, switch on options for Web Subscribed forum posts and switch off options for Email Subscribed forum posts. As admin open a new discussion in the forum and mark as 'Send forum post notifications with no editing-time delay' before posting it. As admin run these tasks: php admin/tool/task/cli/schedule_task.php --execute="\mod_forum\task\cron_task" php admin/tool/task/cli/schedule_task.php --execute="\core\task\messaging_cleanup_task" As user1 see that you don't have any new notification to read. But click on notifications bubble and go to See all and see that notification related to the forum exists and is marked as read. As user2 see that you have a new notification. Don't read it. As admin go to Site administration -> Advanced features. Choose 1 day for 'Delete read notifications' and 1 month 'Delete all notifications' and Save Changes. As admin wait for 1 day (or change server time if possible) and run the cron task: php admin/tool/task/cli/schedule_task.php --execute="\core\task\messaging_cleanup_task" As user1 go to Notifications -> See all and check that the forum notification is not there anymore. As user2 go to Notifications -> See all and check that the forum notification is still there. As admin go to Site administration -> Advanced features. Choose 1 day for 'Delete all notifications' and Save Changes. As admin run the cron task: php admin/tool/task/cli/schedule_task.php --execute="\core\task\messaging_cleanup_task" As user2 go to Notifications -> See all and check that the forum notification is not there anymore.

      This is what we discovered on moodle.org site. The "notifications" table, introduced in May 2018 as a part of MDL-36941, now contains around 5.3 millions records. The whole table has around 18 GB of data as reported by MySQL:

      mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "moodleorg" AND table_name = "notifications";
      		 
      +---------------+------------+
      		 
      | Table         | Size in MB |
      		 
      +---------------+------------+
      		 
      | notifications |   17828.38 |
      		 
      +---------------+------------+

      The table takes significant part of the database dump size. All operations in it (such as personal data access requests etc) take long time and has caused us various troubles recently.

      We have had the $CFG->messagingdeletereadnotificationsdelay set for a short period (one day) to avoid this kind of bloating before. But this setting did not seem to help us any more to keep things under control.

      I was looking at it quickly and I have a suspicion now that forum post notifications that are being emailed via the email output processor, are not set as read in the notifications table. So if there is a discussion with 100 subscribed users (via email notifications) and someone posts into that discussion, we create 100 notification records in this table (each holding the copy of the whole forum post) and send them via email. But we never set the "timeread" value so it remains NULL and the task that deletes read notifications, never removes them.

      As a result, the table bloats quickly.

      This is seen as a serious issue for us with significant performance impacts. Let me suggest to:

      1. Change the behaviour so that once the notification is sent via email output processor, it is marked as read in the notifications table. If someone has both email and popup / mobile notifications enabled, I don't think it is expected to have the notification still marked as unread if it is already in the INBOX.
      2. Introduce a new setting next to the existing "messagingdeletereadnotificationsdelay" that would allow to define a period after which we delete all notifications, both read and unread. The default value can be quite high here, such as 3 months. I believe that after 3 months, most notifications are outdated anyway.

        1. 1.PNG
          1.PNG
          140 kB
        2. 2.PNG
          2.PNG
          64 kB
        3. sample.patch
          2 kB

            amaia Amaia Anabitarte
            mudrd8mz David Mudrák (@mudrd8mz)
            Peter Dias Peter Dias
            Andrew Lyons Andrew Lyons
            Janelle Barcega Janelle Barcega
            Votes:
            2 Vote for this issue
            Watchers:
            14 Start watching this issue

              Created:
              Updated:
              Resolved:

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

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