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

scorm report performs badly if many users in report

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.7.9
    • Fix Version/s: None
    • Component/s: SCORM
    • Labels:
    • Affected Branches:
      MOODLE_27_STABLE

      Description

      Here's a blow by blow, from our slack chat:
      a: could you pls take a peek at pithream and see if it is getting hammered?
      ​s said it was showing heavy CPU usage

      10:50
      The site is running like a dog for me ATM
      j
      10:51 AM Will do.

      10:53
      site seems to be doing something on postgres that’s hogging up 2 cpu core

      10:55
      it looks like its scorm reporting

      10:55
      GET /mod/scorm/report.php?id=2262 HTTP/1.0
      ​10:55
      I think there's a problem with that report
      a
      10:56 AM I think there was an issue with that script. Lemme ask the Devs
      j
      10:57 AM @a can you kill the reporting. Otherwise it might bork the apache
      d
      10:57 AM SELECT COUNT(DISTINCT( '' || u.id || '#' || COALESCE(st.attempt, 0) )) AS nbresults, COUNT(DISTINCT( '' || u.id || '#' || st.attempt )) AS nbattempts, COUNT(DISTINCT(u.id)) AS nbusers FROM mdl_user u LEFT JOIN mdl_scorm_scoes_track st ON st.userid = u.id AND st.scormid = 256 WHERE u.id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,$133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150,$151,$152,$153,$154,$155,$156,$157,$158,$159,$160,$161,$162,$163,$164,$165,$166,$167,$168,

      When I killed this query the site performance returned to normal

      Note that the above IN list has > 160 ids in it, and I think it was truncated, so could be many more.

      I think it is pretty well known that large lists inside IN clauses don't perform well.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                danmorph Daniel Morphett
                Participants:
                Component watchers:
                Damyon Wiese, Dan Marsden, Matteo Scaramuccia, Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: