Very slow course participation reports

Very slow course participation reports

by Josef Fortier -
Number of replies: 3

SHORT SUMMARY -- Moodle performs well in general, but running participation reports with intervals of a few weeks creates extremely high I/O load and very long running queries (5+ minutes)


DETAILS

I've recently taken a more direct sysadmin role for moodle (my general role is not moodle specific).

At the semester start we made significant changes from our old moodle setup, most significantly moving from a physical server to a VMWare, and updating to Moodle 2.7. The VM:

  1. Single processor, 2.4Gh, EqualLogic backend.
  2. 8G ram (3G innodb_buffer_pool_size cache)
  3. MySQL 5.6, apache2, php 5.4, ACP all running on OpenSuSE 13.1
  4. varnish webcache (although this should not be relevant)

We imported the Db from the prior instance with a dump and restor. It's InnoDB tables, with on disk size of around 6G

Performance has been fine, with no complaints (aside from course participation reports)

Participation reports cause a very large spike in I/O. Course Participation report with intervals of any size (say a few weeks) will peg I/O for 5 minutes or more (and timeout at the browser).

My next step is to enable slow_log and look for query without indexes. I've googled and looked through the forums here, and do not see much useful.

I'm hoping someone here might provide a some insight. I'll post more as I learn more. But if anyone here has thoughts I'd love to hear them!

Average of ratings: -
In reply to Josef Fortier

Re: Very slow course participation reports

by Josef Fortier -
I've just used "show full processlist" to look at the SQL executing in from a participation report (attached)

Running explain against the query shows a lot of temp file use. That's seems like a potential issue.

It also is looking through 0.5M log lines, although the log lines appear to have an index.

Average of ratings: Useful (1)
In reply to Josef Fortier

Re: Very slow course participation reports

by James McLean -

Half a million log lines? That's cute smile Come talk to us when you get over 100 million smile

Participation reports are an issue and have been for a LONG time... We disabled them and built our own ETL processes which transform the data and store it in an enterprise data warehouse which allows much more efficient and powerful analytics.

Average of ratings: Useful (1)
In reply to Josef Fortier

Re: Very slow course participation reports

by Jay Liotta -

Any luck resolving your issue? We're experiencing similar issues with the course participation report with the browser frequently timing out before the report finishes. Thanks!

Average of ratings: -