Details

    • Type: Task Task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.5.3, 1.8.3, 1.9, 2.5
    • Fix Version/s: BACKEND
    • Component/s: Logging, Performance
    • Labels:
    • Database:
      Any, MySQL
    • Affected Branches:
      MOODLE_15_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_25_STABLE
    • Epic Link:
    • Rank:
      51957

      Description

      There is somewhat of a performance bug in the way logs/activity reports are handled.

      Activity reports are of course generally slow, and tend to run some very SQL selects. While these are selects are happening, of course no inserts can occur. This blocks essentially all traffic on the site (since almost any page view/action includes a log write).

      This directly relates to the count of log rows you have. In our case we pruned it as much as we could, and were left with 10M+ entries (we have to keep at least 2 semesters, worth of logs), so each select may take 5-15s, which really cripples the site.

      The fix that we implemented is to have 2 tables. One,'log', and one 'log_insert'. We modified the add_to_log in datalib.php to write to 'log_insert' instead of 'log'. Every cron cycle a piece of code run that moves log entries from 'log_insert' to 'log'.

      Martin D. mentioned that logs were one of the things he isn't very happy with the general implementation of, so maybe there needs to be a bigger discussion of it.

      Ill post our patch code when I get back to the states, but it's pretty basic.

        Issue Links

          Activity

          Hide
          Eric Merrill added a comment -
          Show
          Eric Merrill added a comment - here is a forum for this: http://moodle.org/mod/forum/discuss.php?d=83081
          Hide
          Samuli Karevaara added a comment -

          The "Recent Activity" block has been reported to slow things down also. Maybe the intermediate table (log_insert) could be log_cache or something similar? It would hold the logs for the number of days to enable the Recent Activity block. The cron job would copy this then to log table. This way the inserts would be faster + lots of the frequent log table queries would be faster too.

          Show
          Samuli Karevaara added a comment - The "Recent Activity" block has been reported to slow things down also. Maybe the intermediate table (log_insert) could be log_cache or something similar? It would hold the logs for the number of days to enable the Recent Activity block. The cron job would copy this then to log table. This way the inserts would be faster + lots of the frequent log table queries would be faster too.
          Hide
          Martin Dougiamas added a comment -

          Yes one big log file is a terrible mess and leads to inefficient queries in many places.

          I'd like to propose we have 3 tables, even:

          log_new - for short term use like recent activity (past two days)
          log_recent - for the past week, say, this would be the one most of the other current joins would use for things like activity reports.
          log_archive - for X years - rarely gets used but it's there if we need it.

          I was thinking we'd write to the first two all the time, and occasionally copy older ones from log_recent to log_archive.

          There might be much better strategies - let's hear em!

          Show
          Martin Dougiamas added a comment - Yes one big log file is a terrible mess and leads to inefficient queries in many places. I'd like to propose we have 3 tables, even: log_new - for short term use like recent activity (past two days) log_recent - for the past week, say, this would be the one most of the other current joins would use for things like activity reports. log_archive - for X years - rarely gets used but it's there if we need it. I was thinking we'd write to the first two all the time, and occasionally copy older ones from log_recent to log_archive. There might be much better strategies - let's hear em!
          Hide
          Yolanda Ordoñez Rufat added a comment -

          Hi,
          There's a post that present a solution to the growth of the log table by partitioning the table, so the work is done by the system database:
          http://moodle.org/mod/forum/discuss.php?d=162310

          Show
          Yolanda Ordoñez Rufat added a comment - Hi, There's a post that present a solution to the growth of the log table by partitioning the table, so the work is done by the system database: http://moodle.org/mod/forum/discuss.php?d=162310
          Hide
          Jonathan Champ added a comment -

          One of the changes we made is to use INSERT DELAYED so that the INSERTs return success immediately so that a locked log table is not blocking. This helps a lot with MyISAM or ARCHIVE tables, but won't work on InnoDB and other table types.

          One change that may be useful / required for partitioning the table is to drop the id column's primary key (or the id column entirely) so that the table can be partitioned using other indexes. There were only two places in the code that appear to modify the log table using the id column, but those are probably misuses anyway.

          Show
          Jonathan Champ added a comment - One of the changes we made is to use INSERT DELAYED so that the INSERTs return success immediately so that a locked log table is not blocking. This helps a lot with MyISAM or ARCHIVE tables, but won't work on InnoDB and other table types. One change that may be useful / required for partitioning the table is to drop the id column's primary key (or the id column entirely) so that the table can be partitioned using other indexes. There were only two places in the code that appear to modify the log table using the id column, but those are probably misuses anyway.
          Hide
          Martin Dougiamas added a comment -

          I'm moving this to be part of the logging project for 2.6. Even though that is focussed on external logging databases, we can still improve performance of the internal logging when it is used.

          Show
          Martin Dougiamas added a comment - I'm moving this to be part of the logging project for 2.6. Even though that is focussed on external logging databases, we can still improve performance of the internal logging when it is used.
          Hide
          moodle.com added a comment -

          This is being resolved via the new logging improvements.

          Show
          moodle.com added a comment - This is being resolved via the new logging improvements.

            People

            • Votes:
              25 Vote for this issue
              Watchers:
              18 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: