Hello,
I am a MySQL performance engineer and I am new to moodle.
I have recently reviewed a large moodle installation with X thousand users and I wanted to show what I discovered as well as ask more seasoned moodle engineers if this is a good idea.
Firstly, moodle is largely read based. The writes in MySQL need to be optimised by in the context of not locking the reads. Reads to Writes are about 50 to 1.
Query cache appears to be a good idea for moodle.
The table mdl_files table seems to be a very big bottleneck when you log all queries hitting the database.
I recommend adding a partition to reduce contention
alter table mdl_files drop primary key, drop key mdl_file_pat_uix, add primary key (id,contextid), add unique key mdl_file_pat_uix(pathnamehash, contextid) partition by hash(contextid) partitions 10;
I also found some indexes that may speed up the login pages:
alter table moodle_live.mdl_scorm_scoes_track drop index mdl_scorscoetrac_use_ix, add index mdl_scorscoetrac_use_ix(userid,scoid,attempt,element), add index idx_userid2(userid,scormid,attempt);
alter table moodle_live.mdl_cache_flags drop index mdl_cachflag_fla_ix, add index mdl_cachflag_fla_ix(flagtype,expiry);
Also, logstore table seems to be quite big, but not read very often.
However, courseid seems to be a good key to partition it on if you would like to.
Please review and let me know if you think that these are helpful.
