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.