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)
I've recently taken a more direct sysadmin role for moodle (my general role is not moodle specific).
- Single processor, 2.4Gh, EqualLogic backend.
- 8G ram (3G innodb_buffer_pool_size cache)
- MySQL 5.6, apache2, php 5.4, ACP all running on OpenSuSE 13.1
- 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!