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

Poor performance resulting to page timeout on Moodle User Log report using Standard log

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.7.7, 3.8.4
    • Fix Version/s: None
    • Component/s: Reports
    • Labels:
      None
    • Affected Branches:
      MOODLE_37_STABLE, MOODLE_38_STABLE

      Description

      A timeout and poor performance is still encountered on Moodle User Report Logs. This happens on Site-wide log report and course with large number of enrolled users/lots of activities.

      Example for site-wide report log: total mdl_logstore_standard_log records = 1,863,847,613 (Postgresql 11.7 version) another site with total of 196,823,363 (Postgresql 9.6.12) gives timeout error when viewing the site logs.

      Going to Site administration / Reports / Logs and displaying all the dates report result into time out.

      Example for course report log where timeout is also experienced.

      EXPLAIN ANALYSE SELECT * FROM mdl_logstore_standard_log WHERE courseid = 12345 AND anonymous = 0 ORDER BY timecreated DESC, id DESC;
      QUERY PLAN
      --------------------------------------------------------------------------------------------------------------------------------------------------------------
       Sort (cost=785132.38..785681.12 rows=219496 width=213) (actual time=215704.564..215798.965 rows=249815 loops=1)
       Sort Key: timecreated DESC, id DESC
       Sort Method: external merge Disk: 54616kB
       -> Bitmap Heap Scan on mdl_logstore_standard_log (cost=8090.40..743150.37 rows=219496 width=213) (actual time=2830.373..215110.653 rows=249815 loops=1)
       Recheck Cond: ((courseid = 12345) AND (anonymous = 0))
       Rows Removed by Index Recheck: 3451937
       Heap Blocks: exact=25900 lossy=108313
       -> Bitmap Index Scan on mdl_logsstanlog_couanotim_ix (cost=0.00..8035.53 rows=219496 width=0) (actual time=2823.003..2823.003 rows=249815 loops=1)
       Index Cond: ((courseid = 12345) AND (anonymous = 0))
       Planning time: 0.085 ms
      ---> Execution time: 215818.557 ms (3.59 in Minutes) 
      

       

      Additional note:

      • Timeout was increased on load balancer but did not fixed the issue.
      • Code fix from MDL-52482 and/or MDL-44998 did not fixed the issue.

      Thank  you.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              jebarvia Joshua Ebarvia
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: