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

Performance issues with forum_print_overview()

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.9.5, 2.0
    • Fix Version/s: 1.9.6
    • Component/s: Dashboard (My home), Forum
    • Labels:
      None
    • Database:
      Any, PostgreSQL
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      The forum_print_overview() function, used by My Moodle, can be extremely slow for high usage sites. It queries the mdl_log table which can have millions of entries per day. We were finding on some systems that the function was actually taking more than 30 seconds and sometimes minutes. See attached text file for a postgres explain analyze output. The performance can be improved by changing the ILIKE 'add post%' condition to be = 'add post', so that the query can use the log_coumodact_ix index to filter the log.action value.

      My attached patch changes the "ILIKE" to an "=" which is fine because there are never any cases where there is text following the 'add post' (confirmed by code audit + log audit).

      Although this change speeds up the query and reduces DB load, it still is a very expensive query. I think there needs to be a way to determine number of new forum posts without having to scan the log table. Note that the query can contain conditions like "WHERE lastaccess > 0" which causes a requires a lengthy scan of the log table.

        Attachments

        1. forumoverview.txt
          15 kB
        2. forumoverview19.patch
          0.7 kB
        3. forumoverview20.patch
          0.5 kB

          Activity

            People

            • Assignee:
              ashleyholman Ashley Holman
              Reporter:
              ashleyholman Ashley Holman
              Tester:
              Andrew Davis
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón, Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                21/Oct/09