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

mdl_event query takes down Moodle

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.9.3
    • Fix Version/s: None
    • Component/s: Events API
    • Affected Branches:
      MOODLE_39_STABLE

      Description

      Hello I have recently noticed that my Moodle is going offline and during that period the following query is being ran on the DB

      SELECT e.*
            FROM mdl_event e
      INNER JOIN (SELECT ev.modulename,
                      ev.instance,
                      ev.eventtype,
                      MIN(ev.priority) as priority
                 FROM (SELECT modulename, instance, eventtype, priority
                            FROM mdl_event ev
                           WHERE (ev.userid = '226' AND ev.courseid = 0 AND ev.groupid = 0 AND ev.categoryid = 0) UNION SELECT modulename, instance, eventtype, priority
                            FROM mdl_event ev
                           WHERE  (ev.groupid != 0 AND ev.eventtype = 'group') UNION SELECT modulename, instance, eventtype, priority
                            FROM mdl_event ev
                           WHERE  (ev.groupid = 0 AND ev.courseid IN ('12345','11453','7141','4232','9910','13636','13635','13624','15849','8290','22','13075','10088','8284','6058','4431','256','15660','7216','10090','10093','8847','8848','6929','7256','6930','13473','13476','12622','12572','15869','15876','1') AND ev.categoryid = 0) UNION SELECT modulename, instance, eventtype, priority
                            FROM mdl_event ev
                           WHERE  (ev.categoryid != 0 AND ev.eventtype = 'category')) ev
             GROUP BY ev.modulename, ev.instance, ev.eventtype) fe
              ON e.modulename = fe.modulename
                 AND e.instance = fe.instance
                 AND e.eventtype = fe.eventtype
                 AND (e.priority = fe.priority OR (e.priority IS NULL AND fe.priority IS NULL))
       LEFT JOIN mdl_modules m
              ON e.modulename = m.name
           WHERE (m.visible = 1 OR m.visible IS NULL) AND type = '1' AND timesort >= '1610316000' AND timesort <= '1610920800' AND ((e.userid = '226' AND e.courseid = 0 AND e.groupid = 0 AND e.categoryid = 0) OR e.groupid != 0 OR (e.groupid = 0 AND e.courseid IN ('12345','11453','7141','4232','9910','13636','13635','13624','15849','8290','22','13075','10088','8284','6058','4431','256','15660','7216','10090','10093','8847','8848','6929','7256','6930','13473','13476','12622','12572','15869','15876','1')) OR (e.groupid = 0 AND e.courseid = 0 AND e.categoryid != 0)) AND (e.visible = 1)
        ORDER BY COALESCE(e.timesort, e.timestart) ASC, e.id ASC LIMIT 0, 6;
      
      

      Here is my info

      Moodle Version: 020061501.06;   

      Moodle Release: 3.9.1+ (Build: 20200807)

      Running on MariaDB (AWS)

      OS: Amazon Linux AMI

       

      Since I have enabled the performance insights in Amazon, I got this report

       

       

      I know that there is a related issue MDL-66253 Reduce the number of full table scans on the event table - Moodle Tracker here and it says its fixed but I am still facing a similar issue.

       

      This is really serious since it takes Moodle down. The DB CPU Utilizations jumps to 100%.

       

      Does anyone else having this issue and can give me some pointes on how to fix it?

       

      Kind regards

      Christos

       

       

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            christos312 christos
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
            Votes:
            5 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated: